Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need help with a particular formula
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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need help with a particular formula
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. -- David Biddulph "wen999" wrote in message ... 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? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need help with a particular formula
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 "wen999" wrote: 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|