Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel 2003 IF formula returns non-zero value instead of zero
I have recently begun to experience random problems with conditional IF
formulas not returning zero but a very very tiny negative number, which causes wrong results in other cells which rely on a zero value from the IF formula to calculate correctly. In a reservoir routing speadsheet with say 2000 rows of calculations and 1000 that should be zero, two are not, but tracing them can take hours expanding every cell display to 15 or more decimal places to find which are not zero. I have never had this problem before. It started in Office XP, but today I upgraded to Office 2003 and the error is still there. Using ROUND everywhere is a work around but significantly increases the size of the spreadsheet. Does anyone know the cause and solution? -- Hydrotechchris |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel 2003 IF formula returns non-zero value instead of zero
Could you post an example of one of the formulae giving rise to this
problem? Pete On Jul 26, 7:50 pm, HydrotechChris wrote: I have recently begun to experience random problems with conditional IF formulas not returning zero but a very very tiny negative number, which causes wrong results in other cells which rely on a zero value from the IF formula to calculate correctly. In a reservoir routing speadsheet with say 2000 rows of calculations and 1000 that should be zero, two are not, but tracing them can take hours expanding every cell display to 15 or more decimal places to find which are not zero. I have never had this problem before. It started in Office XP, but today I upgraded to Office 2003 and the error is still there. Using ROUND everywhere is a work around but significantly increases the size of the spreadsheet. Does anyone know the cause and solution? -- Hydrotechchris |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel 2003 IF formula returns non-zero value instead of zero
The formula is
=IF(D467=$D$25,IF(G4670,0,G467),IF(E467+G467*B46 7*24*60*60/1000000$D$26,($D$26-E467)*1000000/(B467*24*60*60),G467)) The error occurs with the first nested IF because it should enter zero when G4670, but occasionally it enters -0.00000000000001061145 -- Hydrotechchris "Pete_UK" wrote: Could you post an example of one of the formulae giving rise to this problem? Pete On Jul 26, 7:50 pm, HydrotechChris wrote: I have recently begun to experience random problems with conditional IF formulas not returning zero but a very very tiny negative number, which causes wrong results in other cells which rely on a zero value from the IF formula to calculate correctly. In a reservoir routing speadsheet with say 2000 rows of calculations and 1000 that should be zero, two are not, but tracing them can take hours expanding every cell display to 15 or more decimal places to find which are not zero. I have never had this problem before. It started in Office XP, but today I upgraded to Office 2003 and the error is still there. Using ROUND everywhere is a work around but significantly increases the size of the spreadsheet. Does anyone know the cause and solution? -- Hydrotechchris |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel 2003 IF formula returns non-zero value instead of zero
I deal with these problems by setting Precision As Displayed on under Tools,
Options, Calculation. Any chance you had it on and turned it off? -- Jim "HydrotechChris" wrote in message ... | The formula is | =IF(D467=$D$25,IF(G4670,0,G467),IF(E467+G467*B46 7*24*60*60/1000000$D$26,($D$26-E467)*1000000/(B467*24*60*60),G467)) | | The error occurs with the first nested IF because it should enter zero when | G4670, but occasionally it enters -0.00000000000001061145 | -- | Hydrotechchris | | | "Pete_UK" wrote: | | Could you post an example of one of the formulae giving rise to this | problem? | | Pete | | On Jul 26, 7:50 pm, HydrotechChris | wrote: | I have recently begun to experience random problems with conditional IF | formulas not returning zero but a very very tiny negative number, which | causes wrong results in other cells which rely on a zero value from the IF | formula to calculate correctly. In a reservoir routing speadsheet with say | 2000 rows of calculations and 1000 that should be zero, two are not, but | tracing them can take hours expanding every cell display to 15 or more | decimal places to find which are not zero. I have never had this problem | before. It started in Office XP, but today I upgraded to Office 2003 and the | error is still there. Using ROUND everywhere is a work around but | significantly increases the size of the spreadsheet. Does anyone know the | cause and solution? | -- | Hydrotechchris | | | |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel 2003 IF formula returns non-zero value instead of zero
Hi Jim,
I have never used Precision As Displayed because my spreadsheets are often in the order of 40MB with thousands of rows and 100 columns or more. To keep the display manageable I minimize the digits displayed, and I cannot accept the inaccuracy that would result if everything was rounded to the displayed precision. -- Hydrotechchris "Jim Rech" wrote: I deal with these problems by setting Precision As Displayed on under Tools, Options, Calculation. Any chance you had it on and turned it off? -- Jim "HydrotechChris" wrote in message ... | The formula is | =IF(D467=$D$25,IF(G4670,0,G467),IF(E467+G467*B46 7*24*60*60/1000000$D$26,($D$26-E467)*1000000/(B467*24*60*60),G467)) | | The error occurs with the first nested IF because it should enter zero when | G4670, but occasionally it enters -0.00000000000001061145 | -- | Hydrotechchris | | | "Pete_UK" wrote: | | Could you post an example of one of the formulae giving rise to this | problem? | | Pete | | On Jul 26, 7:50 pm, HydrotechChris | wrote: | I have recently begun to experience random problems with conditional IF | formulas not returning zero but a very very tiny negative number, which | causes wrong results in other cells which rely on a zero value from the IF | formula to calculate correctly. In a reservoir routing speadsheet with say | 2000 rows of calculations and 1000 that should be zero, two are not, but | tracing them can take hours expanding every cell display to 15 or more | decimal places to find which are not zero. I have never had this problem | before. It started in Office XP, but today I upgraded to Office 2003 and the | error is still there. Using ROUND everywhere is a work around but | significantly increases the size of the spreadsheet. Does anyone know the | cause and solution? | -- | Hydrotechchris | | | |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel 2003 IF formula returns non-zero value instead of zero
Hi Pete,
I don't know if it helps, but I also noticed the same issue occurring in an optimization macro that I wrote using Solver based on an example included in the Excel add-in. Coding that should have set one of the conditions to zero did not result in a zero, but a very very tiny negative value, which produced major problems when I used Sort to sort the results, because I first had to go through thousands of results and copy/paste 0.0 into every cell that appeared to be zero, but often wasn't zero. -- Hydrotechchris "Pete_UK" wrote: Could you post an example of one of the formulae giving rise to this problem? Pete On Jul 26, 7:50 pm, HydrotechChris wrote: I have recently begun to experience random problems with conditional IF formulas not returning zero but a very very tiny negative number, which causes wrong results in other cells which rely on a zero value from the IF formula to calculate correctly. In a reservoir routing speadsheet with say 2000 rows of calculations and 1000 that should be zero, two are not, but tracing them can take hours expanding every cell display to 15 or more decimal places to find which are not zero. I have never had this problem before. It started in Office XP, but today I upgraded to Office 2003 and the error is still there. Using ROUND everywhere is a work around but significantly increases the size of the spreadsheet. Does anyone know the cause and solution? -- Hydrotechchris |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel 2003 IF formula returns non-zero value instead of zero
Hi Chris,
I think your problem is not specifically the first nested IF, i.e.: =IF(D467=$D$25,IF(G4670,0,G467), ... but the formula which is in G467 - such a small value as -0.00000000000001061145 is still not greater than 0, and so this value will be returned. You could perhaps change the comparator to = rather than just , and Excel might treat this small number as being close enough to zero, but I think you need to make the adjustment to the formula in G467 to ensure that such small numbers are treated as zero. Only you know what accuracy and precision is required for the rest of the sheet. Hope this helps. Pete On Jul 26, 9:00 pm, HydrotechChris wrote: Hi Pete, I don't know if it helps, but I also noticed the same issue occurring in an optimization macro that I wrote using Solver based on an example included in the Excel add-in. Coding that should have set one of the conditions to zero did not result in a zero, but a very very tiny negative value, which produced major problems when I used Sort to sort the results, because I first had to go through thousands of results and copy/paste 0.0 into every cell that appeared to be zero, but often wasn't zero. -- Hydrotechchris "Pete_UK" wrote: Could you post an example of one of the formulae giving rise to this problem? Pete On Jul 26, 7:50 pm, HydrotechChris wrote: I have recently begun to experience random problems with conditional IF formulas not returning zero but a very very tiny negative number, which causes wrong results in other cells which rely on a zero value from the IF formula to calculate correctly. In a reservoir routing speadsheet with say 2000 rows of calculations and 1000 that should be zero, two are not, but tracing them can take hours expanding every cell display to 15 or more decimal places to find which are not zero. I have never had this problem before. It started in Office XP, but today I upgraded to Office 2003 and the error is still there. Using ROUND everywhere is a work around but significantly increases the size of the spreadsheet. Does anyone know the cause and solution? -- Hydrotechchris- Hide quoted text - - Show quoted text - |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel 2003 IF formula returns non-zero value instead of zero
Hi Pete,
In both cases of non-zero results, the value in the column G cell is a large number, i.e. 14.95 and 37.45, whereas other rows return the correct zero result when the G cell is as small as 0.01. -- Hydrotechchris "Pete_UK" wrote: Hi Chris, I think your problem is not specifically the first nested IF, i.e.: =IF(D467=$D$25,IF(G4670,0,G467), ... but the formula which is in G467 - such a small value as -0.00000000000001061145 is still not greater than 0, and so this value will be returned. You could perhaps change the comparator to = rather than just , and Excel might treat this small number as being close enough to zero, but I think you need to make the adjustment to the formula in G467 to ensure that such small numbers are treated as zero. Only you know what accuracy and precision is required for the rest of the sheet. Hope this helps. Pete On Jul 26, 9:00 pm, HydrotechChris wrote: Hi Pete, I don't know if it helps, but I also noticed the same issue occurring in an optimization macro that I wrote using Solver based on an example included in the Excel add-in. Coding that should have set one of the conditions to zero did not result in a zero, but a very very tiny negative value, which produced major problems when I used Sort to sort the results, because I first had to go through thousands of results and copy/paste 0.0 into every cell that appeared to be zero, but often wasn't zero. -- Hydrotechchris "Pete_UK" wrote: Could you post an example of one of the formulae giving rise to this problem? Pete On Jul 26, 7:50 pm, HydrotechChris wrote: I have recently begun to experience random problems with conditional IF formulas not returning zero but a very very tiny negative number, which causes wrong results in other cells which rely on a zero value from the IF formula to calculate correctly. In a reservoir routing speadsheet with say 2000 rows of calculations and 1000 that should be zero, two are not, but tracing them can take hours expanding every cell display to 15 or more decimal places to find which are not zero. I have never had this problem before. It started in Office XP, but today I upgraded to Office 2003 and the error is still there. Using ROUND everywhere is a work around but significantly increases the size of the spreadsheet. Does anyone know the cause and solution? -- Hydrotechchris- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel: How to insert carriage returns in a formula to manipulate t | Excel Worksheet Functions | |||
Carriage Returns Excel 2003 | Excel Discussion (Misc queries) | |||
Formula format in excel that returns an "error" if there is a blan | Excel Discussion (Misc queries) | |||
formula returns error in version 2003 only | Excel Worksheet Functions | |||
Date Value Funtion in Excel 2003 returns an error, but not in Exce | Excel Worksheet Functions |