ROUNDUP inside an IF formula

MrQuestion

New Member
Joined
Mar 3, 2013
Messages
7
=IF(C13<=B3*D3,"IN STOCK","BACKORDER"),IF(B3*D3/C13>=1,"IN STOCK ",ROUNDUP(B3*D3/C13,0))

Can someone tell me what is wrong with this formula? The first part works, but when I combine a second if function it doesn't work. It gives me a value error.

This is what was assigned to me.


  • A number.
    This means we do not have enough on-hand in our inventory, but we can get it in time. And, we need this many boxes from our supplier.
    Let's look at the glue rows. The order requests 40 glue sticks but we have only 36 in stock. It takes 1 day for us to get more from our supplier and we have 2 days to fill the order. So we have time to get this item in stock and fulfill the order, and we need 1 box of glue sticks to do it. You may want to use the =ROUNDUP function here.
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
Hi and welcome to the Board
The reason your function doesn't work past the first part is becasue the conditions have been met.

=IF(C13<=B3*D3,"IN STOCK","BACKORDER"),IF(B3*D3/C13>=1,"IN STOCK ",ROUNDUP(B3*D3/C13,0))

IF(C13<=B3*D3, then if true "IN STOCK", if False "BACK ORDER".....therefore no reason to do the rest !!

As this is an assignment, we won't provide the answer, but why a ROUNDUP function if you are having an IN STOCK or BACKORDER result, neither of which are a numeric result

What do you want to see in the cell, how many to order, or the fact that they are on back order !
 
Upvote 0
Hi and welcome to the Board
The reason your function doesn't work past the first part is becasue the conditions have been met.

=IF(C13<=B3*D3,"IN STOCK","BACKORDER"),IF(B3*D3/C13>=1,"IN STOCK ",ROUNDUP(B3*D3/C13,0))

IF(C13<=B3*D3, then if true "IN STOCK", if False "BACK ORDER".....therefore no reason to do the rest !!

As this is an assignment, we won't provide the answer, but why a ROUNDUP function if you are having an IN STOCK or BACKORDER result, neither of which are a numeric result

What do you want to see in the cell, how many to order, or the fact that they are on back order !

Well, we have to use two IF functions together so they can answer all the questions needed. One of them wants us to show a number one, which requires the usage of roundup.
 
Upvote 0
I need to use roundup so I can complete the second problem. Any hints on how to do this? I need to use roundup in an IF function.
 
Upvote 0
An IF statement can only has 3 arguments (expression,true,false).
You need to use a nested IF to do get the result you need.
ie =IF(arg,TRUE,IF(arg,TRUE,FALSE))
You can use upto 6 IF statements in one formula
Hope this helps
 
Last edited:
Upvote 0
An IF statement can only has 3 arguments (expression,true,false).
You need to use a nested IF to do get the result you need.
ie =IF(arg,TRUE,IF(arg,TRUE,FALSE))
You can use upto 6 IF statements in one formula
Hope this helps

All right this worked, but then it changed both of my outcomes to one, one of them need to stay as a back order. Any hints to that? thanks by the way
 
Upvote 0
Okay
Have a look at the =IF(AND( function

Something like
Rich (BB code):
=IF(AND(C13<=B3*D3,B3*D3/C13>=1), do something if true,ELSE show the ROUNDUP calculation if false)
 
Upvote 0
=IF(C16<=B6*D6,"IN STOCK",IF(C16>=B6*D6,ROUNDUP((B6*D6)/C16,0),"BACK ORDER"))

This is what I have so far, I can't get the second one to change to a back order though. We won't receive the goods in time, so it has to be a back order. While the first one stays a one because we will receive the goods on time.
 
Upvote 0
Okay, look at the logic

If C16 <=.....In stock
If C16 >=....do roundup

So C16 is cancelled out.......what makes C16 create the result "Back order" if the 2 criteria above cover all options ???

Have a think about what criteria is needed to result in "IN STOCK"
what criteria is needed to result in "BACK ORDER"
and what criteria is needed to result in the ROUNDUP function

I understand this may be frustrating for you.......but it's the only way to learn the method
Based on your original formula, look at
=IF(C13<=B3*D3,"IN STOCK","BACKORDER"),IF(B3*D3/C13>=1,"IN STOCK ",ROUNDUP(B3*D3/C13,0))

IF C13 <= ......do instock
if B3*D3/C13=0 .....Do ROUNDUP
else.....do back order
 
Upvote 0
Okay, look at the logic

If C16 <=.....In stock
If C16 >=....do roundup

So C16 is cancelled out.......what makes C16 create the result "Back order" if the 2 criteria above cover all options ???

Have a think about what criteria is needed to result in "IN STOCK"
what criteria is needed to result in "BACK ORDER"
and what criteria is needed to result in the ROUNDUP function

I understand this may be frustrating for you.......but it's the only way to learn the method
Based on your original formula, look at
=IF(C13<=B3*D3,"IN STOCK","BACKORDER"),IF(B3*D3/C13>=1,"IN STOCK ",ROUNDUP(B3*D3/C13,0))

IF C13 <= ......do instock
if B3*D3/C13=0 .....Do ROUNDUP
else.....do back order

Yeah I finally figured it out. I never use excel, so doing this was kind of frustrating to say the least. Thanks for your guys' help.
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,245
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top