| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If And statements -- HELP!
I don't understand why this formula does not work
Cell D 10 is =IF(B10=1,$G$72,(IF(AND(D11=0,D9=$G$72),$G$71,0)) ) Cell D 11 is =IF(B11=1,$G$72,(IF(AND(D12=0,D10=$G$72),$G$71,0) )) Cell D 12 is =IF(B12=1,$G$72,(IF(AND(D13=0,D11=$G$72),$G$71,0) )) If I check off display zero values in my options menu then I do see a zero in the cell but the formula does not read it as a zero. The only way I know it doesn't is when I type in a zero in cell D 12 then cell D 11 reacts the way I expect it to.... Is there something I don't understand about formula writing or is this one of those so called glitches? This is what it looks like when the formula is working on its own Total Kms for the Day 76 ___________________________________________ This is what it looks like when I type in a zero in cell D 12 Total Kms for the Day 76 Total Kms to date 76 0 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
If And statements -- HELP!
Hi Don...
I understand the concept of .000000000001 but there is no reason for these formulas to return anything but a phrase or a zero... can you see anyway to remedy this problem? "Don Guillett" wrote: Probably something resulting in .000000000001 that you don't see and(int(d9), -- Don Guillett SalesAid Software "GI JOE" <GI wrote in message ... I don't understand why this formula does not work Cell D 10 is =IF(B10=1,$G$72,(IF(AND(D11=0,D9=$G$72),$G$71,0)) ) Cell D 11 is =IF(B11=1,$G$72,(IF(AND(D12=0,D10=$G$72),$G$71,0) )) Cell D 12 is =IF(B12=1,$G$72,(IF(AND(D13=0,D11=$G$72),$G$71,0) )) If I check off display zero values in my options menu then I do see a zero in the cell but the formula does not read it as a zero. The only way I know it doesn't is when I type in a zero in cell D 12 then cell D 11 reacts the way I expect it to.... Is there something I don't understand about formula writing or is this one of those so called glitches? This is what it looks like when the formula is working on its own Total Kms for the Day 76 ___________________________________________ This is what it looks like when I type in a zero in cell D 12 Total Kms for the Day 76 Total Kms to date 76 0 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
If And statements -- HELP!
I just checked the fx icon next to the formula bar and it indicates the
correct formula outcome so now I'm totally confused! Why would the outcome not show up in the cell? "GI JOE" wrote: I don't understand why this formula does not work Cell D 10 is =IF(B10=1,$G$72,(IF(AND(D11=0,D9=$G$72),$G$71,0)) ) Cell D 11 is =IF(B11=1,$G$72,(IF(AND(D12=0,D10=$G$72),$G$71,0) )) Cell D 12 is =IF(B12=1,$G$72,(IF(AND(D13=0,D11=$G$72),$G$71,0) )) If I check off display zero values in my options menu then I do see a zero in the cell but the formula does not read it as a zero. The only way I know it doesn't is when I type in a zero in cell D 12 then cell D 11 reacts the way I expect it to.... Is there something I don't understand about formula writing or is this one of those so called glitches? This is what it looks like when the formula is working on its own Total Kms for the Day 76 ___________________________________________ This is what it looks like when I type in a zero in cell D 12 Total Kms for the Day 76 Total Kms to date 76 0 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
If And statements -- HELP!
I may be wrong but is it not the circular reference that is stopping the
formulas calculating? -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings with @tiscali.co.uk "GI JOE" wrote in message ... I just checked the fx icon next to the formula bar and it indicates the correct formula outcome so now I'm totally confused! Why would the outcome not show up in the cell? "GI JOE" wrote: I don't understand why this formula does not work Cell D 10 is =IF(B10=1,$G$72,(IF(AND(D11=0,D9=$G$72),$G$71,0)) ) Cell D 11 is =IF(B11=1,$G$72,(IF(AND(D12=0,D10=$G$72),$G$71,0) )) Cell D 12 is =IF(B12=1,$G$72,(IF(AND(D13=0,D11=$G$72),$G$71,0) )) If I check off display zero values in my options menu then I do see a zero in the cell but the formula does not read it as a zero. The only way I know it doesn't is when I type in a zero in cell D 12 then cell D 11 reacts the way I expect it to.... Is there something I don't understand about formula writing or is this one of those so called glitches? This is what it looks like when the formula is working on its own Total Kms for the Day 76 ___________________________________________ This is what it looks like when I type in a zero in cell D 12 Total Kms for the Day 76 Total Kms to date 76 0 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
If And statements -- HELP!
Good morning Sandy (Toronto Time)
Thank you for your help... Is there a way to turn off the circular reference in order for the value to appear? Joe. "Sandy Mann" wrote: I may be wrong but is it not the circular reference that is stopping the formulas calculating? -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings with @tiscali.co.uk "GI JOE" wrote in message ... I just checked the fx icon next to the formula bar and it indicates the correct formula outcome so now I'm totally confused! Why would the outcome not show up in the cell? "GI JOE" wrote: I don't understand why this formula does not work Cell D 10 is =IF(B10=1,$G$72,(IF(AND(D11=0,D9=$G$72),$G$71,0)) ) Cell D 11 is =IF(B11=1,$G$72,(IF(AND(D12=0,D10=$G$72),$G$71,0) )) Cell D 12 is =IF(B12=1,$G$72,(IF(AND(D13=0,D11=$G$72),$G$71,0) )) If I check off display zero values in my options menu then I do see a zero in the cell but the formula does not read it as a zero. The only way I know it doesn't is when I type in a zero in cell D 12 then cell D 11 reacts the way I expect it to.... Is there something I don't understand about formula writing or is this one of those so called glitches? This is what it looks like when the formula is working on its own Total Kms for the Day 76 ___________________________________________ This is what it looks like when I type in a zero in cell D 12 Total Kms for the Day 76 Total Kms to date 76 0 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
If And statements -- HELP!
Hi GI JOE,
No there is no way that I know of to turning off the circular reference. You can turn Iteration on or off but that does not stop the circular references and your Perhaps if you explain what it is that you are trying to do some of the clever people around here will be able to give you an alternarive or work around. -- Regards, Sandy In Perth, the ancient capital of Scotland and the crowning place of kings with @tiscali.co.uk "GI JOE" wrote in message ... Good morning Sandy (Toronto Time) Thank you for your help... Is there a way to turn off the circular reference in order for the value to appear? Joe. "Sandy Mann" wrote: I may be wrong but is it not the circular reference that is stopping the formulas calculating? -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings with @tiscali.co.uk "GI JOE" wrote in message ... I just checked the fx icon next to the formula bar and it indicates the correct formula outcome so now I'm totally confused! Why would the outcome not show up in the cell? "GI JOE" wrote: I don't understand why this formula does not work Cell D 10 is =IF(B10=1,$G$72,(IF(AND(D11=0,D9=$G$72),$G$71,0)) ) Cell D 11 is =IF(B11=1,$G$72,(IF(AND(D12=0,D10=$G$72),$G$71,0) )) Cell D 12 is =IF(B12=1,$G$72,(IF(AND(D13=0,D11=$G$72),$G$71,0) )) If I check off display zero values in my options menu then I do see a zero in the cell but the formula does not read it as a zero. The only way I know it doesn't is when I type in a zero in cell D 12 then cell D 11 reacts the way I expect it to.... Is there something I don't understand about formula writing or is this one of those so called glitches? This is what it looks like when the formula is working on its own Total Kms for the Day 76 ___________________________________________ This is what it looks like when I type in a zero in cell D 12 Total Kms for the Day 76 Total Kms to date 76 0 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
If And statements -- HELP!
Hello again Sandy...
Thank you for reminding me that you cannot shut off circular reference errors and so I came up with an alternative string in an adjacent cell to avoid the error... and well I seems to have solved the problem! Thank you to all who worked on this with (and for) me! This is the first time I used a discussion group to resolve anything and I must say it is an excellent forum! Joe. "Sandy Mann" wrote: Hi GI JOE, No there is no way that I know of to turning off the circular reference. You can turn Iteration on or off but that does not stop the circular references and your Perhaps if you explain what it is that you are trying to do some of the clever people around here will be able to give you an alternarive or work around. -- Regards, Sandy In Perth, the ancient capital of Scotland and the crowning place of kings with @tiscali.co.uk "GI JOE" wrote in message ... Good morning Sandy (Toronto Time) Thank you for your help... Is there a way to turn off the circular reference in order for the value to appear? Joe. "Sandy Mann" wrote: I may be wrong but is it not the circular reference that is stopping the formulas calculating? -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings with @tiscali.co.uk "GI JOE" wrote in message ... I just checked the fx icon next to the formula bar and it indicates the correct formula outcome so now I'm totally confused! Why would the outcome not show up in the cell? "GI JOE" wrote: I don't understand why this formula does not work Cell D 10 is =IF(B10=1,$G$72,(IF(AND(D11=0,D9=$G$72),$G$71,0)) ) Cell D 11 is =IF(B11=1,$G$72,(IF(AND(D12=0,D10=$G$72),$G$71,0) )) Cell D 12 is =IF(B12=1,$G$72,(IF(AND(D13=0,D11=$G$72),$G$71,0) )) If I check off display zero values in my options menu then I do see a zero in the cell but the formula does not read it as a zero. The only way I know it doesn't is when I type in a zero in cell D 12 then cell D 11 reacts the way I expect it to.... Is there something I don't understand about formula writing or is this one of those so called glitches? This is what it looks like when the formula is working on its own Total Kms for the Day 76 ___________________________________________ This is what it looks like when I type in a zero in cell D 12 Total Kms for the Day 76 Total Kms to date 76 0 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| IF Statements (Mutliple Statements) | Excel Worksheet Functions | |||
| If statements | Excel Discussion (Misc queries) | |||
| IF & AND statements | Excel Worksheet Functions | |||
| If then statements | Excel Worksheet Functions | |||
| IF Statements | Excel Worksheet Functions | |||
Hybrid Mode
