buggy cell miscalculates SUM function

[lindasf]

Cell B19 does not SUM the preceding 5 cells correctly. It gives a valu
of 9 when it should be 42.

The problem might be with cell B16, which it seems to pass over.
TIA

Spreadsheet attache

Attachment filename: calwin foster care training analysis - demo.xl
Download attachment: http://www.excelforum.com/attachment.php?postid=58266

 

[Dave Peterson]

Try formatting that B16 cell as General and then reenter the value.

(excel may have been seeing your value in B16 as text)

 

[Ron Rosenfeld]

Cell B19 does not SUM the preceding 5 cells correctly. It gives a value
of 9 when it should be 42.

The problem might be with cell B16, which it seems to pass over.
TIA

Spreadsheet attached

Attachment filename: calwin foster care training analysis - demo.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=582665

Your number in B16 was entered as TEXT. SUM ignores text.

After the number has been incorrectly entered, changing the Cell Format will
not change it from TEXT to NUMBER.

You either have to enter it as a number, or perform a numeric calculation on
it. The operators usually will convert it properly.

What you should do precisely depends on the that value got entered as TEXT in
the first place.


--ron

 

[lindasf]

Ron and Dave,

Your advice worked great on the first spreadsheet. I cleared out th
cell and re-entered the format and the formula then worked.

However, it's not working in this spreadsheet and I can't see an
difference between the 2. See cell B25. Thx.

file attache

Attachment filename: calwin food stamps training analysis- demo.xl
Download attachment: http://www.excelforum.com/attachment.php?postid=58627

 

[Ron Rosenfeld]

Ron and Dave,

Your advice worked great on the first spreadsheet. I cleared out the
cell and re-entered the format and the formula then worked.

However, it's not working in this spreadsheet and I can't see any
difference between the 2. See cell B25. Thx.

file attached

Attachment filename: calwin food stamps training analysis- demo.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=586272

Well, the obvious reason is that the criteria you list in the SUMIF function in
B25 does not appear in the Curricula range.

And, in fact, it does not. In the Curricula range, when you have entered what
appears to be "Clerical 2", in fact, you have entered "Clerical 2 ". (Note the
trailing space). These two entries are not the same, hence SUMIF finds no
matches to sum.


--ron

 

[lindasf]

Ron,

Thx much. I looked at all the cells until I was blue in the face an
couldn't see any differences.

A new pair of eyes always helps!

Thx.

lindas