Problem with a function

[Boots]

There is a schema of my excel sheet

1 | A | B | C | D | E | F | G | H |
-------------------------------------------------------------------
2 | 27 | 70 | 591 |See eq1| 14 |See eq2|See eq3|See eq4|
-------------------------------------------------------------------

Eq1 : =(C2/3,1416)^0,5 (Result : 13.72)
Eq2 : =((C2*E2)/3,1416)^0,5 (Result : 51.32)
Eq3 : =(2,4/2)*B2^0,5813 (Result : 14.18)
Eq4 : =3,74*A2^(1/3) (Result : 11.22)

The problem is when I'm trying to use this formula/function
"=((E2*D2^2)-(F2^2))*(H2^2)" it give me 4.54784 E-12 when it suppose
to be around 200.81.

When I put the result in the cell D F G and H ( no formula/function )
like this:

1 | A | B | C | D | E | F | G | H |
-------------------------------------------------------------------
2 | 27 | 70 | 591 |13.72 | 14 |51.32 |14.18 |11.22 |
-------------------------------------------------------------------

the formula/function "E2*D2^2)-(F2^2))*(H2^2)" give me the right
result: 200.81...

Anyone know Why or how to arrange this???<
Thanks for your help

 

[Bernard Liengme]

Hi Boots,
What do you mean by 'the answer is suppose to be"? I get 5.72E-11 with
Excel 2003
Here is the problem the first and send terms (E2*D2^2) and (F2^2) are (to
the precision of Excel) the same. Try cells with just these formulas and
with =first-second. I get the first line shown below. If I use your values I
get the second line.
2633.689839572190000 2633.689839572190000 0.000000000000000
2635.337600000000000 2633.742400000000000 1.595200000000200

You see the value in D2 is not 13.72 but
13.715710073728800

So we have a rounding problem. If you want to force the result to 200.81 use
formulas such as =ROUND((C2/3,1416)^0,5,2) to get values with two decimal
places.

If you have a newer version of Excel, place cursor on the cell with
=((E2*D2^2)-(F2^2))*(H2^2) and use the Tools|Formula Audition|Evaluate
formula. You will see that Excel does not make (E2*D2^2)-(F2^2)) exactly
zero when it.

 

[Bernard Liengme]

Hello again,
I have just noticed that you used Pi to an arbitrary number of places. If
you use PI() in place of 3,1416 in Eq1 and Eq2 then the result of the final
formula is zero.