- Microsoft Excel
- Microsoft Office

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/$

```
=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)))
```

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
```
=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

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

wall---floor-tiles-worksheet_2.xlsx
## Premium Content

You need an Expert Office subscription to comment.Start Free Trial