Avatar of Frank .S
Frank .S
Flag for Australia 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

Avatar of undefined
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)))

Open in new window

Frank .S

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

Open in new window

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

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

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

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

I can produce your requested answers using:
=IF(D7="","",CEILING(SIGN(F7)*ABS(D7/$G$5*C7)*(1+G7%),1)*CEILING(SIGN(F7)*ABS(E7/$H$5*C7)*(1+G7%),1))

Open in new window


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

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.
ASKER CERTIFIED SOLUTION
byundt

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Frank .S

ASKER
very prompt reply & solution, thankyou
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes