|
formula returns blank
I have entered the following formula and it keeps giving me a blank when the
reference field is a formula. If I change to a number it will populate. Can someone tell me what I'm doing wrong? =IF((AND(' Yrly Log'!B160%,' Yrly Log'!B16<96%)),1,IF((AND(' Yrly Log'!B16=96%,' Yrly Log'!B16<97%)),2,IF((AND(' Yrly Log'!B16=97%,' Yrly Log'!B16<99%)),3,IF((AND(' Yrly Log'!B16=99%,' Yrly Log'!B16<=100%)),4,IF((AND(' Yrly Log'!B16=101%,' Yrly Log'!B16<500%)),5,""))))) If the cell it points to is: =AVERAGE(B3:B15) the result is a blank. -- Thanks for your assistance! Karen |
formula returns blank
I presume =AVERAGE(B3:B15) is in B16? How is that calculation formatted? As
a decimal? Try replacing the percentages in your formula with their decimal equivalents, i.e., 0% = 0, 96% = .96, etc. -- Brevity is the soul of wit. "Karen" wrote: I have entered the following formula and it keeps giving me a blank when the reference field is a formula. If I change to a number it will populate. Can someone tell me what I'm doing wrong? =IF((AND(' Yrly Log'!B160%,' Yrly Log'!B16<96%)),1,IF((AND(' Yrly Log'!B16=96%,' Yrly Log'!B16<97%)),2,IF((AND(' Yrly Log'!B16=97%,' Yrly Log'!B16<99%)),3,IF((AND(' Yrly Log'!B16=99%,' Yrly Log'!B16<=100%)),4,IF((AND(' Yrly Log'!B16=101%,' Yrly Log'!B16<500%)),5,""))))) If the cell it points to is: =AVERAGE(B3:B15) the result is a blank. -- Thanks for your assistance! Karen |
formula returns blank
Yes, =AVERAGE(B3:B15) in in B16. Changing the format to decimal didn't work.
It still returns a blank. -- Thanks for your assistance! Karen "Dave F" wrote: I presume =AVERAGE(B3:B15) is in B16? How is that calculation formatted? As a decimal? Try replacing the percentages in your formula with their decimal equivalents, i.e., 0% = 0, 96% = .96, etc. -- Brevity is the soul of wit. "Karen" wrote: I have entered the following formula and it keeps giving me a blank when the reference field is a formula. If I change to a number it will populate. Can someone tell me what I'm doing wrong? =IF((AND(' Yrly Log'!B160%,' Yrly Log'!B16<96%)),1,IF((AND(' Yrly Log'!B16=96%,' Yrly Log'!B16<97%)),2,IF((AND(' Yrly Log'!B16=97%,' Yrly Log'!B16<99%)),3,IF((AND(' Yrly Log'!B16=99%,' Yrly Log'!B16<=100%)),4,IF((AND(' Yrly Log'!B16=101%,' Yrly Log'!B16<500%)),5,""))))) If the cell it points to is: =AVERAGE(B3:B15) the result is a blank. -- Thanks for your assistance! Karen |
formula returns blank
101% is the value returned.
-- Thanks for your assistance! Karen "Sandy Mann" wrote: Karen, What is the value returned by the AVERAGE() formula in B16? -- Regards, Sandy In Perth, the ancient capital of Scotland and the crowning place of kings with @tiscali.co.uk "Karen" wrote in message ... Yes, =AVERAGE(B3:B15) in in B16. Changing the format to decimal didn't work. It still returns a blank. -- Thanks for your assistance! Karen "Dave F" wrote: I presume =AVERAGE(B3:B15) is in B16? How is that calculation formatted? As a decimal? Try replacing the percentages in your formula with their decimal equivalents, i.e., 0% = 0, 96% = .96, etc. -- Brevity is the soul of wit. "Karen" wrote: I have entered the following formula and it keeps giving me a blank when the reference field is a formula. If I change to a number it will populate. Can someone tell me what I'm doing wrong? =IF((AND(' Yrly Log'!B160%,' Yrly Log'!B16<96%)),1,IF((AND(' Yrly Log'!B16=96%,' Yrly Log'!B16<97%)),2,IF((AND(' Yrly Log'!B16=97%,' Yrly Log'!B16<99%)),3,IF((AND(' Yrly Log'!B16=99%,' Yrly Log'!B16<=100%)),4,IF((AND(' Yrly Log'!B16=101%,' Yrly Log'!B16<500%)),5,""))))) If the cell it points to is: =AVERAGE(B3:B15) the result is a blank. -- Thanks for your assistance! Karen |
formula returns blank
Karen,
If B16 is formatted to show percentages as whole numbers then the actual value in B16 may be something like 100.9% *displaying* as 101%. If that were to be the case then there is a hole in your testing between Log'!B16<=100%)),4,IF((AND(' Yrly Log'!B16=101%, 100.9% is bigger than 100% but it is smaller then 101% so it would not meet any criteria and therefore will run through to the FALSE part of the IF() function and so return "" Try making your formula: Log'!B16<101%)),4,IF((AND(' Yrly Log'!B16=101%, and see if that helps. Just a thought. -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings with @tiscali.co.uk "Karen" wrote in message ... 101% is the value returned. -- Thanks for your assistance! Karen "Sandy Mann" wrote: Karen, What is the value returned by the AVERAGE() formula in B16? -- Regards, Sandy In Perth, the ancient capital of Scotland and the crowning place of kings with @tiscali.co.uk "Karen" wrote in message ... Yes, =AVERAGE(B3:B15) in in B16. Changing the format to decimal didn't work. It still returns a blank. -- Thanks for your assistance! Karen "Dave F" wrote: I presume =AVERAGE(B3:B15) is in B16? How is that calculation formatted? As a decimal? Try replacing the percentages in your formula with their decimal equivalents, i.e., 0% = 0, 96% = .96, etc. -- Brevity is the soul of wit. "Karen" wrote: I have entered the following formula and it keeps giving me a blank when the reference field is a formula. If I change to a number it will populate. Can someone tell me what I'm doing wrong? =IF((AND(' Yrly Log'!B160%,' Yrly Log'!B16<96%)),1,IF((AND(' Yrly Log'!B16=96%,' Yrly Log'!B16<97%)),2,IF((AND(' Yrly Log'!B16=97%,' Yrly Log'!B16<99%)),3,IF((AND(' Yrly Log'!B16=99%,' Yrly Log'!B16<=100%)),4,IF((AND(' Yrly Log'!B16=101%,' Yrly Log'!B16<500%)),5,""))))) If the cell it points to is: =AVERAGE(B3:B15) the result is a blank. -- Thanks for your assistance! Karen |
| All times are GMT +1. The time now is 02:27 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com