Page 1 of 2 12 LastLast
Results 1 to 10 of 12

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. #1
    New Member
    Join Date
    Mar 2013
    Posts
    7

    Default 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. #2
    Board Regular Michael M's Avatar
    Join Date
    Oct 2005
    Location
    Western NSW
    Posts
    8,382

    Default 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 !
    Regards
    Michael M
    ---------------------------------------
    The more I learn, the less I seem to know.....A Please and Thank You cost nothing !
    It's easier to debug if we can see the whole macro !
    work 2003, Home 2007

    - Posting guidelines, forum rules and terms of use

    - To download Mr Excel HTML Maker

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes


    [CODE]Place Your Code Here[/CODE]

  3. #3
    New Member
    Join Date
    Mar 2013
    Posts
    7

    Default Re: ROUNDUP inside an IF formula

    Quote Originally Posted by Michael M View Post
    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. #4
    New Member
    Join Date
    Mar 2013
    Posts
    7

    Default 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. #5
    New Member
    Join Date
    Mar 2013
    Posts
    1

    Default 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
    Last edited by sabreB; Mar 3rd, 2013 at 08:06 PM.

  6. #6
    New Member
    Join Date
    Mar 2013
    Posts
    7

    Default Re: ROUNDUP inside an IF formula

    Quote Originally Posted by sabreB View Post
    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. #7
    Board Regular Michael M's Avatar
    Join Date
    Oct 2005
    Location
    Western NSW
    Posts
    8,382

    Default 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)
    Regards
    Michael M
    ---------------------------------------
    The more I learn, the less I seem to know.....A Please and Thank You cost nothing !
    It's easier to debug if we can see the whole macro !
    work 2003, Home 2007

    - Posting guidelines, forum rules and terms of use

    - To download Mr Excel HTML Maker

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes


    [CODE]Place Your Code Here[/CODE]

  8. #8
    New Member
    Join Date
    Mar 2013
    Posts
    7

    Default 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. #9
    Board Regular Michael M's Avatar
    Join Date
    Oct 2005
    Location
    Western NSW
    Posts
    8,382

    Default 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
    Regards
    Michael M
    ---------------------------------------
    The more I learn, the less I seem to know.....A Please and Thank You cost nothing !
    It's easier to debug if we can see the whole macro !
    work 2003, Home 2007

    - Posting guidelines, forum rules and terms of use

    - To download Mr Excel HTML Maker

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes


    [CODE]Place Your Code Here[/CODE]

  10. #10
    New Member
    Join Date
    Mar 2013
    Posts
    7

    Default Re: ROUNDUP inside an IF formula

    Quote Originally Posted by Michael M View Post
    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 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com