Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Trace Precedent
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Trace Precedent
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") In article , George Cuartero wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
trace dependent tool doesn't work but trace precendent doesn't | Excel Discussion (Misc queries) | |||
tracing precedent by double clicking cell | Excel Discussion (Misc queries) | |||
Auding Tool bar for Precedent Tracing Greyed Out | Excel Discussion (Misc queries) | |||
Excel Spreadsheet Trace Precedent Bars not showing up | Excel Worksheet Functions | |||
precedent | Excel Discussion (Misc queries) |