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

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

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

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

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

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