We help IT Professionals succeed at work.

ifstatement formula - qty result not correct

on
hi experts, could you help with the following ifstatement formula which is not returning the correct qty result..
im using excel 2016.

- the result should = 26.3 not 27.31

Comment
Watch Question

View Solution Only

Finance Analyst

Commented:
How do you get 26.3?

Working the formula in mathematical order

F6 * G6 = 100.03 * 0.21 = 21.0063

CEILING(1 + H6%,0.1) = 1.3

21.0063 * 1.3 = 27.30819
Building Estimator

Commented:
1 x 100.03 x 1.00 = 100.03 x 0.21 = 21.01 +25% = 26.26, round up to 0.1 = 26.3
Finance Analyst

Commented:
In which case the CEILING is in the wrong place, your rounding up the percentage value rather than the result so multiplying by 1.3 rather than 1.25. Try this:

=IFERROR(IF(D6="","",CEILING(F6*G6*1+H6%,0.1)),"")
Building Estimator

Commented:
no this gives me a result of 21.3
Finance Analyst
Commented:
Sorry, missed some brackets:

=IFERROR(IF(D6="","",CEILING(F6*G6*(1+H6%),0.1)),"")
Building Estimator

Commented: