# ROUNDUP inside an IF formula

This is a discussion on ROUNDUP inside an IF formula within the Excel Questions forums, part of the Question Forums category; =IF(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 ...

1. ## ROUNDUP inside an IF formula

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

2. ## Re: ROUNDUP inside an IF formula

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 !

3. ## Re: ROUNDUP inside an IF formula

Originally Posted by Michael M
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.

4. ## Re: ROUNDUP inside an IF formula

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.

5. ## Re: ROUNDUP inside an IF formula

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

6. ## Re: ROUNDUP inside an IF formula

Originally Posted by sabreB
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

7. ## Re: ROUNDUP inside an IF formula

Okay
Have a look at the =IF(AND( function

Something like
Code:
`=IF(AND(C13<=B3*D3,B3*D3/C13>=1), do something if true,ELSE show the ROUNDUP calculation if false)`

8. ## Re: ROUNDUP inside an IF formula

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

9. ## Re: ROUNDUP inside an IF formula

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

10. ## Re: ROUNDUP inside an IF formula

Originally Posted by Michael M
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.

Page 1 of 2 12 Last

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•