  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))
Microsoft ExcelMicrosoft Office Last Comment
Frank .S

8/22/2022 - Mon
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)))
``````
Frank .S

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
byundt

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
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Frank .S

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?
byundt

I put that feature in cells H7:H12
Frank .S

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
Get an unlimited membership to EE for less than \$4 a week.
Unlimited question asking, solutions, articles and more.
byundt

``````=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
Frank .S

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.
byundt