Frank .S

asked on

# Excel 2016 - IF statement with a round down formula

Hi Experts, i need this ifstatement to be modified so that when a result = a negative qty, instead of rounding up, to round down.

So if the result is a positive qty then round up otherwise round down.

Is there a way in this formula to do this?

=IF(D9="","",ROUNDUP((D9/$G$5*C9)+G9%,0)*ROUNDUP((E9/$H$5*C9)+G9%,0))

So if the result is a positive qty then round up otherwise round down.

Is there a way in this formula to do this?

=IF(D9="","",ROUNDUP((D9/$

ASKER

sorry, i have attached the wksht for your reference, col 'c' is what is required to have this formula round down if a negative qty.

wall---floor-tiles-worksheet_1.xlsx

wall---floor-tiles-worksheet_1.xlsx

I believe your waste percentage is being calculated incorrectly. I think it should be CalculatedQuantity*(1+G7%).

If all you need to worry about is cell C7*D7*E7 = F7 being negative, you may use ROUNDUP with the absolute value of the quantity, then multiply that by the + or - sign of cell F7.

If all you need to worry about is cell C7*D7*E7 = F7 being negative, you may use ROUNDUP with the absolute value of the quantity, then multiply that by the + or - sign of cell F7.

`=IF(D7="","",SIGN(F7)*ROUNDUP(ABS(D7/$G$5*C7)*(1+G7%),0)*ROUNDUP(ABS(E7/$H$5*C7)*(1+G7%),0))`

wall---floor-tiles-worksheet_1.xlsx
ASKER

basically all i need in the formula if negative is for it to round down otherwise roundup, has this been added to the worksheet if so in what cell?

I put that feature in cells H7:H12

ASKER

i have tried a number of combinations with the formula you have in column h7:h12 but the rounddown qty result is wrong.. cell H9 should = 2

1.00 / 0.4 = 2.5, rounded down to 2

0.5 / 0.4= 1.25 rounded down to 1

therefore 2x1=2

please check formula.

wall---floor-tiles-worksheet_2.xlsx

1.00 / 0.4 = 2.5, rounded down to 2

0.5 / 0.4= 1.25 rounded down to 1

therefore 2x1=2

please check formula.

wall---floor-tiles-worksheet_2.xlsx

I can produce your requested answers using:

Note that at most one of the three values C7, D7 and E7 may be negative. If two of them are negative, you will round up rather than down.

wall---floor-tiles-worksheet_2.xlsx

`=IF(D7="","",CEILING(SIGN(F7)*ABS(D7/$G$5*C7)*(1+G7%),1)*CEILING(SIGN(F7)*ABS(E7/$H$5*C7)*(1+G7%),1))`

Note that at most one of the three values C7, D7 and E7 may be negative. If two of them are negative, you will round up rather than down.

wall---floor-tiles-worksheet_2.xlsx

ASKER

hi byundt, F9 is a negative so H9 should also be shown as negative, therefore the qty should equal -2 not positive. could you please change your formula?

otherwise the total in H13 will be wrong because it will add the negative qtys instead of deducting them.

otherwise the total in H13 will be wrong because it will add the negative qtys instead of deducting them.

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

ASKER

very prompt reply & solution, thankyou

Open in new window