Formula Headaches

[Question Boy]

I'm still struggling with the Numeric Formatting of a Formula of a table.

Can someone explain what is wrong. I have

=-2020596 \# "$#,##0;$(#,##0);'--'"

Now if I do a summation on this value, it will not include/calculate the
above number. However, when I modify the formula as such (simply moving the
$ inside the ())

=-2020596 \# "$#,##0;($#,##0);'--'"

The summation will now work????

Yes, I have Identified what is causing the issue. However, I need the value
to be displayed

$#;$(#);--

How can I place the $ outside the paranthesis and still be able to perform
calculation with values?

Thank you,

QB

 

[macropod]

Hi QB,

It works for me (Word 2000 & 2007) - and returns $(2,020,596).

An observation: unless you need to pad values less than 1000 with spaces between the $ and the amount, you should use:
=-2020596 \# "$,0;$(,0);'--'"

Cheers

 

[Question Boy]

Let me try to reexplain.

Yes, it return the wanted format. However, the returned format cannot be
used in calculation performed elsewhere in the table such as sum(above).

ie: if I have a table with three entry

120
2000
=-3000\# "$#,##0;$(#,##0);'--'"

and then I create a new summation row with

=sum(above)

it returns 2120 rather than -1120

This said all works if the $ is put inside the ()??? It gives the format
but you can work with it in any way, shape or form for some reason that I'm
hoping someone can explain so that I finally learn and understand the
formula/numeric formatting. I need it formatted per above but also need to
then be able to use the formatted number to do further calculation.

QB

Hi QB,

It works for me (Word 2000 & 2007) - and returns $(2,020,596).

An observation: unless you need to pad values less than 1000 with spaces between the $ and the amount, you should use:
=-2020596 \# "$,0;$(,0);'--'"

Cheers
--
macropod
[MVP - Microsoft Word]
-------------------------

I'm still struggling with the Numeric Formatting of a Formula of a table.

Can someone explain what is wrong. I have

=-2020596 \# "$#,##0;$(#,##0);'--'"

Now if I do a summation on this value, it will not include/calculate the
above number. However, when I modify the formula as such (simply moving the
$ inside the ())

=-2020596 \# "$#,##0;($#,##0);'--'"

The summation will now work????

Yes, I have Identified what is causing the issue. However, I need the value
to be displayed

$#;$(#);--

How can I place the $ outside the paranthesis and still be able to perform
calculation with values?

Thank you,

QB

 

[macropod]

Hi QB,

There's no way I know of handling that - the negative format you've used converts the value to text, which then can't be operated on
mathematically or even referred to via cell referencing.

The only way around it that I can see is to use another reference back to the original value without the formatting.

Cheers
--
macropod
[MVP - Microsoft Word]
-------------------------

Let me try to reexplain.

Yes, it return the wanted format. However, the returned format cannot be
used in calculation performed elsewhere in the table such as sum(above).

ie: if I have a table with three entry

120
2000
=-3000\# "$#,##0;$(#,##0);'--'"

and then I create a new summation row with

=sum(above)

it returns 2120 rather than -1120

This said all works if the $ is put inside the ()??? It gives the format
but you can work with it in any way, shape or form for some reason that I'm
hoping someone can explain so that I finally learn and understand the
formula/numeric formatting. I need it formatted per above but also need to
then be able to use the formatted number to do further calculation.

QB

Hi QB,

It works for me (Word 2000 & 2007) - and returns $(2,020,596).

An observation: unless you need to pad values less than 1000 with spaces between the $ and the amount, you should use:
=-2020596 \# "$,0;$(,0);'--'"

Cheers
--
macropod
[MVP - Microsoft Word]
-------------------------

I'm still struggling with the Numeric Formatting of a Formula of a table.

Can someone explain what is wrong. I have

=-2020596 \# "$#,##0;$(#,##0);'--'"

Now if I do a summation on this value, it will not include/calculate the
above number. However, when I modify the formula as such (simply moving the
$ inside the ())

=-2020596 \# "$#,##0;($#,##0);'--'"

The summation will now work????

Yes, I have Identified what is causing the issue. However, I need the value
to be displayed

$#;$(#);--

How can I place the $ outside the paranthesis and still be able to perform
calculation with values?

Thank you,

QB