Share via

Word: Product Formula in Table includes random cell

Malte Göller 21 Reputation points
2022-01-25T16:05:46.757+00:00

I have an invoice template that lists all positions.

The line "Zwischentotal" should add all the values above. That works as intended.
The line "MwSt. 7.7%" should calculate and show 7.7% of the field "Zwischentotal". That works as intended.

Below is another sum, called "Total", that should add the two fields (Zwischentotal and MwSt) above. I solved that by bookmarking the fields above, and writing a formula for Total as follows: =SUM(Zwischentotal,MwSt).

This sum however always adds another 0.08 (rounded 0.077) coming from the text-filled cell "MwSt. 7.7%" that is not referenced at all. I tried to rename the bookmark MWST to something else, and I tried to work without merged cells. Both does not work. The only solution is to remove the text "7.7%" in the cell next to it.

Any idea?168336-screenshot-2022-01-25-at-170454.png

Here is the original word template file: https://easyupload.io/36z6vj

Word Management
Word Management
Word: A family of Microsoft word processing software products for creating web, email, and print documents.Management: The act or process of organizing, handling, directing or controlling something.
908 questions
0 comments
Accepted answer
  1. John Korchok 5,161 Reputation points
    2022-01-26T18:38:58.393+00:00

    Here is your document with an accurate formula total: Formula Fixed.docx

    I split the table and bookmarked the data part. Then all formulae refer back to the datatable instead of cross-referencing other formulae. I leave it to you to apply rounding as you see fit.

    0 comments

4 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. John Korchok 5,161 Reputation points
    2022-01-25T18:48:26.133+00:00

    This is a long-standing known problem with Word formulae. Basing a formula on a field containing another formula leads to incorrect results. Instead, create a Total formula that refers to the original data.

    0 comments
  2. Malte Göller 21 Reputation points
    2022-01-26T14:47:45.123+00:00

    Thank you for the answer, but since I need to sum the column above the "Zwischentotal", that won't work either. The number of lines is variable for each invoice. Or how would I get the sum of the last column, ignoring the two fields (which contain other formulas, so I cannot just subtract them) above?

    0 comments
  3. Malte Göller 21 Reputation points
    2022-01-26T21:42:53.353+00:00

    Thank you, that's awesome! Three more questions:

    1. If I try to add a new table below an already existing one, it merges both. How can I add two separate tables so close as you did it?
    2. I tried to add ROUND(previous formula,1) and get a syntax error. It worked on my previous word file, and I have the same delimiters set as you. So commons and not semicolons.
    3. As far as I read up, MROUND(1.76,0.05) is not existent in Word. To get a return value of 1.75? I guess I have to live with that.

    See here: Formula Fixed v2.docx

  4. Malte Göller 21 Reputation points
    2022-01-27T13:46:17.663+00:00

    Awesome, I learnt a lot. Thank you very much!

    0 comments