|
formula returns a blank
I have the following formula that I've enterd in a spreadsheet that returns a
blank when the reference field contains a formula. If I substitue the formula for a percentage, it returns the correct value. How can I fix this? =IF((AND(' Yrly Log'!B160,' Yrly Log'!B16<0.96)),1,IF((AND(' Yrly Log'!B16=0.96,' Yrly Log'!B16<0.97)),2,IF((AND(' Yrly Log'!B16=0.97,' Yrly Log'!B16<0.99)),3,IF((AND(' Yrly Log'!B16=0.99,' Yrly Log'!B16<=1)),4,IF((AND(' Yrly Log'!B16=1.01,' Yrly Log'!B16<5)),5,""))))) Formula in 'Yrly Log'!B16 is =AVERAGE (B3:B15) value is 101%. Doesn't seem to matter if format of B16 is decimal or %. -- Thanks for your assistance! Karen |
formula returns a blank
Hi Karen:
The average that you are calculating is giving a number that is correct to more than two decimal places. For the specific number that you are questioning 1.01 is really between 1.005 and 1.014999999999999. But in your formula you have a gap in the testing range for the numbers between 1.00000000000001 and 1.009999999999999. Therefore change the last part of your formula: (B16=1.01,B16<5)),5,""))))) to: (B161,B16<5)),5,""))))) and it will work. You will get the same problem with other numbers in your formula as the display could show 0.96 but the number is really .9599999 and this will give 1 and not 2 as you think. I would increase the display digits to at least one morew than you want. You could also consider using round on the average answer but it depends on your requirments. -- Hope this helps Martin Fishlock, Bangkok, Thailand Please do not forget to rate this reply. "Karen" wrote: I have the following formula that I've enterd in a spreadsheet that returns a blank when the reference field contains a formula. If I substitue the formula for a percentage, it returns the correct value. How can I fix this? =IF((AND(' Yrly Log'!B160,' Yrly Log'!B16<0.96)),1,IF((AND(' Yrly Log'!B16=0.96,' Yrly Log'!B16<0.97)),2,IF((AND(' Yrly Log'!B16=0.97,' Yrly Log'!B16<0.99)),3,IF((AND(' Yrly Log'!B16=0.99,' Yrly Log'!B16<=1)),4,IF((AND(' Yrly Log'!B16=1.01,' Yrly Log'!B16<5)),5,""))))) Formula in 'Yrly Log'!B16 is =AVERAGE (B3:B15) value is 101%. Doesn't seem to matter if format of B16 is decimal or %. -- Thanks for your assistance! Karen |
formula returns a blank
Think you have a gap in this term:
... AND(' Yrly Log'!B16=1.01,' Yrly Log'!B16<5),.. which should be replaced by: ... AND(' Yrly Log'!B16=1,' Yrly Log'!B16<5), .. Try either: =IF((AND(' Yrly Log'!B160,' Yrly Log'!B16<0.96)),1, IF((AND(' Yrly Log'!B16=0.96,' Yrly Log'!B16<0.97)),2, IF((AND(' Yrly Log'!B16=0.97,' Yrly Log'!B16<0.99)),3, IF((AND(' Yrly Log'!B16=0.99,' Yrly Log'!B16<=1)),4, IF((AND(' Yrly Log'!B16=1,' Yrly Log'!B16<5)),5,""))))) Or, perhaps its neater & better to do it with a VLOOKUP: =VLOOKUP(' Yrly Log'!B16,{0,1;0.96,2;0.97,3;0.99,4;1,5;5,""},2) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Karen" wrote: I have the following formula that I've enterd in a spreadsheet that returns a blank when the reference field contains a formula. If I substitue the formula for a percentage, it returns the correct value. How can I fix this? =IF((AND(' Yrly Log'!B160,' Yrly Log'!B16<0.96)),1,IF((AND(' Yrly Log'!B16=0.96,' Yrly Log'!B16<0.97)),2,IF((AND(' Yrly Log'!B16=0.97,' Yrly Log'!B16<0.99)),3,IF((AND(' Yrly Log'!B16=0.99,' Yrly Log'!B16<=1)),4,IF((AND(' Yrly Log'!B16=1.01,' Yrly Log'!B16<5)),5,""))))) Formula in 'Yrly Log'!B16 is =AVERAGE (B3:B15) value is 101%. Doesn't seem to matter if format of B16 is decimal or %. -- Thanks for your assistance! Karen |
formula returns a blank
Max, thanks for her help. I ended up utilizing the VLOOKUP and it was much
cleaner! -- Thanks for your assistance! Karen "Max" wrote: Think you have a gap in this term: ... AND(' Yrly Log'!B16=1.01,' Yrly Log'!B16<5),.. which should be replaced by: .. AND(' Yrly Log'!B16=1,' Yrly Log'!B16<5), .. Try either: =IF((AND(' Yrly Log'!B160,' Yrly Log'!B16<0.96)),1, IF((AND(' Yrly Log'!B16=0.96,' Yrly Log'!B16<0.97)),2, IF((AND(' Yrly Log'!B16=0.97,' Yrly Log'!B16<0.99)),3, IF((AND(' Yrly Log'!B16=0.99,' Yrly Log'!B16<=1)),4, IF((AND(' Yrly Log'!B16=1,' Yrly Log'!B16<5)),5,""))))) Or, perhaps its neater & better to do it with a VLOOKUP: =VLOOKUP(' Yrly Log'!B16,{0,1;0.96,2;0.97,3;0.99,4;1,5;5,""},2) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Karen" wrote: I have the following formula that I've enterd in a spreadsheet that returns a blank when the reference field contains a formula. If I substitue the formula for a percentage, it returns the correct value. How can I fix this? =IF((AND(' Yrly Log'!B160,' Yrly Log'!B16<0.96)),1,IF((AND(' Yrly Log'!B16=0.96,' Yrly Log'!B16<0.97)),2,IF((AND(' Yrly Log'!B16=0.97,' Yrly Log'!B16<0.99)),3,IF((AND(' Yrly Log'!B16=0.99,' Yrly Log'!B16<=1)),4,IF((AND(' Yrly Log'!B16=1.01,' Yrly Log'!B16<5)),5,""))))) Formula in 'Yrly Log'!B16 is =AVERAGE (B3:B15) value is 101%. Doesn't seem to matter if format of B16 is decimal or %. -- Thanks for your assistance! Karen |
formula returns a blank
that did work.
-- Thanks for your assistance! Karen "Martin Fishlock" wrote: Hi Karen: The average that you are calculating is giving a number that is correct to more than two decimal places. For the specific number that you are questioning 1.01 is really between 1.005 and 1.014999999999999. But in your formula you have a gap in the testing range for the numbers between 1.00000000000001 and 1.009999999999999. Therefore change the last part of your formula: (B16=1.01,B16<5)),5,""))))) to: (B161,B16<5)),5,""))))) and it will work. You will get the same problem with other numbers in your formula as the display could show 0.96 but the number is really .9599999 and this will give 1 and not 2 as you think. I would increase the display digits to at least one morew than you want. You could also consider using round on the average answer but it depends on your requirments. -- Hope this helps Martin Fishlock, Bangkok, Thailand Please do not forget to rate this reply. "Karen" wrote: I have the following formula that I've enterd in a spreadsheet that returns a blank when the reference field contains a formula. If I substitue the formula for a percentage, it returns the correct value. How can I fix this? =IF((AND(' Yrly Log'!B160,' Yrly Log'!B16<0.96)),1,IF((AND(' Yrly Log'!B16=0.96,' Yrly Log'!B16<0.97)),2,IF((AND(' Yrly Log'!B16=0.97,' Yrly Log'!B16<0.99)),3,IF((AND(' Yrly Log'!B16=0.99,' Yrly Log'!B16<=1)),4,IF((AND(' Yrly Log'!B16=1.01,' Yrly Log'!B16<5)),5,""))))) Formula in 'Yrly Log'!B16 is =AVERAGE (B3:B15) value is 101%. Doesn't seem to matter if format of B16 is decimal or %. -- Thanks for your assistance! Karen |
formula returns a blank
Good to hear that, Karen.
You're welcome ! -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- Karen wrote: Max, thanks for the help. I ended up utilizing the VLOOKUP and it was much cleaner! -- Thanks for your assistance! Karen |
| All times are GMT +1. The time now is 07:07 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com