Calculations in tables

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
Fri Feb 24 12:23:56 CST 2006

oe wrote:
> 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
Fri Feb 24 13:45:59 CST 2006

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" <stephen.glynn@ntlworld.com> wrote in message
news:0bILf.47324$K42.20439@newsfe7-win.ntli.net...
> oe wrote:
>> 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

Stephen
Fri Feb 24 16:59:02 CST 2006

oe wrote:
> 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

>
> "Stephen Glynn" <stephen.glynn@ntlworld.com> wrote in message
> news:0bILf.47324$K42.20439@newsfe7-win.ntli.net...
>> oe wrote:
>>> 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
>
>

macropod
Sat Feb 25 00:52:50 CST 2006

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

Cheers


"oe" <a9h9904-oue@yahoo.co.uk> wrote in message
news:Ol8ItvWOGHA.3556@TK2MSFTNGP10.phx.gbl...
> 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
>
>
>

oe
Sat Feb 25 03:38:59 CST 2006

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!

"macropod" <invalid@invalid.invalid> wrote in message
news:46ad32F9pj3kU1@individual.net...
> You could try:
> {={SUM(Table1 B:B)}+{SUM(Table2 B:B)} \# ",0.00;-,0.00;''"}
>
> Cheers
>
>
> "oe" <a9h9904-oue@yahoo.co.uk> wrote in message
> news:Ol8ItvWOGHA.3556@TK2MSFTNGP10.phx.gbl...
>> 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
>>
>>
>>
>
>

Doug
Sat Feb 25 04:12:51 CST 2006

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" <a9h9904-oue@yahoo.co.uk> wrote in message
news:%23tNGV9eOGHA.3888@TK2MSFTNGP10.phx.gbl...
>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!
>
> "macropod" <invalid@invalid.invalid> wrote in message
> news:46ad32F9pj3kU1@individual.net...
>> You could try:
>> {={SUM(Table1 B:B)}+{SUM(Table2 B:B)} \# ",0.00;-,0.00;''"}
>>
>> Cheers
>>
>>
>> "oe" <a9h9904-oue@yahoo.co.uk> wrote in message
>> news:Ol8ItvWOGHA.3556@TK2MSFTNGP10.phx.gbl...
>>> 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
>>>
>>>
>>>
>>
>>
>
>

oe
Sat Feb 25 05:32:22 CST 2006

Got it! Thanks Doug
"Doug Robbins - Word MVP" <dkr@REMOVETHISmvps.org> wrote in message
news:e%23g0LQfOGHA.2236@TK2MSFTNGP15.phx.gbl...
> 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" <a9h9904-oue@yahoo.co.uk> wrote in message
> news:%23tNGV9eOGHA.3888@TK2MSFTNGP10.phx.gbl...
>>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!
>>
>> "macropod" <invalid@invalid.invalid> wrote in message
>> news:46ad32F9pj3kU1@individual.net...
>>> You could try:
>>> {={SUM(Table1 B:B)}+{SUM(Table2 B:B)} \# ",0.00;-,0.00;''"}
>>>
>>> Cheers
>>>
>>>
>>> "oe" <a9h9904-oue@yahoo.co.uk> wrote in message
>>> news:Ol8ItvWOGHA.3556@TK2MSFTNGP10.phx.gbl...
>>>> 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
>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
>

macropod
Sat Feb 25 05:58:04 CST 2006

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


"oe" <a9h9904-oue@yahoo.co.uk> wrote in message
news:%23tNGV9eOGHA.3888@TK2MSFTNGP10.phx.gbl...
> 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!
>
> "macropod" <invalid@invalid.invalid> wrote in message
> news:46ad32F9pj3kU1@individual.net...
> > You could try:
> > {={SUM(Table1 B:B)}+{SUM(Table2 B:B)} \# ",0.00;-,0.00;''"}
> >
> > Cheers
> >
> >
> > "oe" <a9h9904-oue@yahoo.co.uk> wrote in message
> > news:Ol8ItvWOGHA.3556@TK2MSFTNGP10.phx.gbl...
> >> 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
> >>
> >>
> >>
> >
> >
>
>