# 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))
byundt

It would have been helpful to see the workbook so we could tell which numbers might be negative. Absent that information, I put each ROUNDUP in an IF function testing whether its parameter was negative. If positive, ROUNDUP. If negative, ROUNDDOWN.

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

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
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(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

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

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

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

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.