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

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

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

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

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

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