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