Sum formula only works if placed directly below values??

ericblair

New Member
Joined
Sep 18, 2009
Messages
5
Here are my steps:
1: Open a sheet and highlight a cell
2: Open the Format box and enter this: [$-10409]$- , click OK
3: Enter 0 in that cell, note that the format takes effect and $- is displayed
4: Right below that cell enter 1 and format it as a Number
5: Highlight the cell right below that second cell and click AutoSum and hit Enter

The result I expect is 1, but instead a $- is displayed.

What's going on here?
 

Some videos you may like

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

ericblair

New Member
Joined
Sep 18, 2009
Messages
5
After some playing around it looks like what's happening is that the Sum function is not format agnostic. All cells that you are summing up must have the identical formatting.
 

mgirvin

Well-known Member
Joined
Dec 15, 2005
Messages
1,213
It is not that:<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p></o:p>
<o:p></o:p>
"All cells that you are summing up must have the identical formatting."<o:p></o:p>
<o:p></o:p>
What is happening is that the SUM function "sucks" the formatting from the top cell in the range that has the formatting:<o:p></o:p>
<o:p></o:p>
[$-10409]$-<o:p></o:p>
<o:p></o:p>
If you do your same procedures (1 to 5) and add a sixth step, it will work:<o:p></o:p>
<o:p></o:p>
1: Open a sheet and highlight a cell
2: Open the Format box and enter this: [$-10409]$- , click OK
3: Enter 0 in that cell, note that the format takes effect and $- is displayed
4: Right below that cell enter 1 and format it as a Number
5: Highlight the cell right below that second cell and click AutoSum and hit Enter
6: Apply a Number format to the cell with the SUM function.<o:p></o:p>
<o:p></o:p>
The way I think of Number formatting and formulas is that "The calculated result of a formula doesn't look at formatting, but the Number format can disguise the calculated result temporarily." In your case with the added step 6, we can see that the calculated result of 1 was just temporally disguised and when the Number format is applied you can see that the formula does calculate correctly.<o:p></o:p>
 

Watch MrExcel Video

Forum statistics

Threads
1,108,624
Messages
5,523,967
Members
409,547
Latest member
AW2020

This Week's Hot Topics

Top