Need help with a particular formula

[Guest]

Cells: A16 = 5, A17 = 5, A18 = 5, A15 =sum(A16:A18)/3 = 5
A20 = 2, A21 = 2, A22 = 2, A19 =sum(A16:A18)/3 = 2

F2 = (A15+A19)/2 =3.5

Herein lies the problem: In cell G2 I have the following formula:
=IF(F2=5,3,IF(F2=4,3,IF(F2=3,2,IF(F2=2,1,IF(F2=1,0,0)))))
If all A cells = the same number then cell G2 works, but when the A cells
have different numbers such as above, it does not calculate?

 

[David Biddulph]

Your formula for A19 doesn't give that result. Did you intend it to be
=sum(A20:A22)/3, rather than =sum(A16:A18)/3 ?
When you've clarified exactly which formula you are using, then if you want
help you will need to be more specific than "it does not calculate". If you
tell us what answer you get, and what your inputs are, and what you were
expecting, then we can probably tell you what you've done wrong.

You may also have problems with rounding errors. If, for example,
sum(A16:A18) were to be 16 instead of 15, the division by 3 would not give
5, but 5.333333... to infinity, and that can't be represented exactly in
fixed point binary. Similarly if sum(A20:A22) were 14, the division by 3
would give 4.666666... to infinity. If you add the fixed point binary
approximations of these two numbers, you can't be certain that the answer
will be exactly 5.

 

[Guest]

Hi
Your formula does exactly as you have asked it.
the resultant answer of 3.5 does not meet any of the IF criteria, therefore
retruns the correct answer of 0
Without knowing what you are trying to do, I might assume the = signs in G2
should maybe be either > or < signs, which would then give you a different
result.


HTH
Michael M