Trace Precedent

[George Cuartero]

Hi everyone. I am confused about the wa Excel auditing tool results
provide me.

Php 100.00 - written as text with the letters Php format general
200.00 - 200 to 500 are formated in currency
300.00
400.00
500.00
--------------
1,400.00 - TOTAL is correct

using the summation tool this simple calculation give me the correct
answer. However if i were to use the auditing tool it will show
me that Php 100.00 is included in the trace that MS Excel has
summarized or performed addition to.

I do not get the logic since. It is not of the same format.
all others are currency and Php 100.00 is in text general format.
What I am execting is excel will prompt me with an error #NAME?
- format error calculation has value that excel cant perform
calculation.

But it doesnt. It still adds all currency formated cells and shows
in the trace that Php 100.00 in included though it didnt add its
value since it is in text format.

But what is confuding is if I have 100 items to check using t he
auditing tool and this text formated cell value is in the middle
i will not be able to detect that it is not included in the total
- making the total inaccurate.


I wonder if there is something I can do or tools to use in
excel that can accurately tell me that there is something wrong with
the values that I am summarizing with minimal effort.


Looking forward for a solution.


George

 

[JE McGimpsey]

If I understand you correctly, you're getting the correct total because
the cell with Php 100.00 is interpreted as Text.

Text is ignored by SUM(), so while it will appear in the precedents, it
contributes nothing to the result.

One way to ensure that a range contains only numbers is to, in another
cell, set a flag:

=IF(COUNTA(A1:A100)=COUNT(A1:A100), "All numbers", "CAUTION: Text
values in range A1:A100")