Calculation within an IF formula is incorrect. Any idea why?

[Dan969]

Hi,



Perhaps this has a simple explanation, I hope....



I'm trying to use the IF function to calculate some standard corrections. I'm using the following formula:

=IF(AC9<1, Q9*AC9, Q9)

Q9 = 53.00
AC9 = 0.74

The idea is that AC9 is a ratio, so if it is below 1 I want excel to multiply the figure in Q9 by that ratio, or if 1 or above, to use the value of Q9.



The result comes up as 39.00. However, when I calculate 53.00*0.74 I get 39.22.



Moreover, when I create two separate boxes with these figures, and multiply them together, I also obtain 39.22.



I've tried formatting to the correct number of decimals, and have run error checks. Everything looks fine, except the calculations are consistantly wrong.



Any ideas why the calculation within the IF formula is incorrect?

Thanks

 

[FormR]

Hi, are you sure Q9 and AC9 are exactly what you believe them to be? Are they the result of a formula?

To help diagnose - what does this return..

=AND(Q9=53,AC9=0.74)

 

[steve the fish]

Hi. It most probably is formatting. Format the cells in question, including the result cell, to 5 or 6 decimal places and paste the value of the cells here.

 

[Dan969]

Interesting. It replies FALSE.

The thing is, I'm not giving those numbers specifically, but indicating the squares to be calculated. Doesn't Excel just look at what's in the box and calculate that?

 

[Dan969]

Q9 = 53.00000
AC9 = 0.735849
AP9 (results square)=39.000000

 

[FormR]

Doesn't Excel just look at what's in the box and calculate that?

Assuming by box you mean cell, then no - it's not what you see that gets used in the calculation, it's what the actual underlying value is that gets used.

What formula are you using to generate the AC9 value?

Try wrapping it with a ROUND() function - for example =ROUND(currentcalculation,2)

 

[Dan969]

Cell AC9 contains the formula =$AC$2/Q9
AC2 is a standardised figure = 39.00

I might be using it incorrectly (i've not used ROUND before, but I used the following =IF(AC9<1, (ROUND(Q9*AC9, 2)), Q9)
It made no difference to my result.

 

[FormR]

Cell AC9 contains the formula =$AC$2/Q9

Try changing the formula in AC9 to:

=ROUND($AC$2/Q9,2)

 

[Dan969]

That seemed to work, thanks for the help.

I'll go over the rest of the data-set and see how it looks.