Calculations in tables

[oe]

I have followed Dave Rado's idea of separating tables which I find very
useful. However, I am having a problem with the syntax on my final
calculation and would appreciate any help.

I am designing an 'invoice' type of table with two columns:

Table1 has a list of all the items (Column A) and their prices (Column B).
Table2 has the sum of the items ({ SUM(Table1 B:B) \# "#,##0.00;-
#,##0.00;''" })
Table3 has a VAT calculation - i.e. Table2 x 17.5%

What I need now is the final table to have the sum of Table2 and Table3. I
have tried quite a few options but each get the message "Syntax error".

TIA

 

[Stephen Glynn]

I have followed Dave Rado's idea of separating tables which I find very
useful. However, I am having a problem with the syntax on my final
calculation and would appreciate any help.

I am designing an 'invoice' type of table with two columns:

Table1 has a list of all the items (Column A) and their prices (Column B).
Table2 has the sum of the items ({ SUM(Table1 B:B) \# "#,##0.00;-
#,##0.00;''" })
Table3 has a VAT calculation - i.e. Table2 x 17.5%

What I need now is the final table to have the sum of Table2 and Table3. I
have tried quite a few options but each get the message "Syntax error".

TIA

If you're sure that all your items are VATable, why not calculate Table2
x 117.5%, using the same syntax you've successfully used in Table3?

Steve

 

[oe]

Sorry, I obviously didn't explain very well. I have no problem calculating
the VAT - that's in Table3. What I now need is a table showing the total of
Table 2 and Table 3 to give me an overall total. This would be in "Table 4".

 

[Stephen Glynn]

Sorry, I obviously didn't explain very well. I have no problem calculating
the VAT - that's in Table3. What I now need is a table showing the total of
Table 2 and Table 3 to give me an overall total. This would be in "Table 4".

I know. My point was that if your items attract VAT at 17.5%, the
overall total plus VAT is going to be Table2 (sum of the items) x 117.5%
(i.e. 100% of Table2 plus 17.5% VAT).

If you really want to do it the long way round, I'd bookmark the
individual cells that contain the totals and refer to them. This way you
have {= Sum(Above)} as Table1Total and {= Table1Total * 17.5%} as VAT
and then you calculate your total including VAT with the formula
{=Table1Total + VAT}

Steve

 

[macropod]

You could try:
{={SUM(Table1 B:B)}+{SUM(Table2 B:B)} \# ",0.00;-,0.00;''"}

Cheers

 

[oe]

I seem to be making some progress. The snag I have come across now is,
referring to Dave Rado's instructions (quote):
The bit at the end of the formula:

;''" ... means that if there are no values in column (N), don't display
anything (otherwise it would display 0.00). (end quote)

When I insert this ;"", I again get "Syntax error"

I must be missing something!

 

[Doug Robbins - Word MVP]

Just use

\# "0.00;-0.00;"

or copy and paste

\# "0.00;-0.00;''"

That ends with apostrophe apostrophe quotation mark, not two quotation
marks.

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

 

[oe]

Got it! Thanks Doug

Just use

\# "0.00;-0.00;"

or copy and paste

\# "0.00;-0.00;''"

That ends with apostrophe apostrophe quotation mark, not two quotation
marks.

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

 

[macropod]

Sorry, oe -
The reply suffered from copy & paste without close editing. Formula should
be:
{={SUM(Table1 B:B)}+{SUM(Table2 B:B)} \# ,0.00;-,0.00;}
No quote characters are required for the numeric picture switch.

Cheers