Ask Me Help Desk

Ask Me Help Desk (https://www.askmehelpdesk.com/forum.php)
-   Spreadsheets (https://www.askmehelpdesk.com/forumdisplay.php?f=395)
-   -   IF with ROUNDUP help please? (https://www.askmehelpdesk.com/showthread.php?t=648326)

  • Apr 4, 2012, 02:22 AM
    miramss
    IF with ROUNDUP help please?
    I am struggling to make this formula work. It seems to round up to 1 or 0, but not to 2 or 3. I would really appreciate any advice.

    =IF(B29<1,0,ROUNDUP(((B29/Input!$C$3/Input!$C$19/Schedule!B30)-B35)>0,0))
  • Apr 4, 2012, 02:53 AM
    ScottGem
    What are the values in the cells?

    Also can we assume you are using Excel? If so, which version? If not, what spreadsheet?
  • Apr 4, 2012, 03:02 AM
    miramss
    Hi Scottgem, thank you for replying so quickly. I really appreciate the advice.

    I'm using Excel 2003.
    The values in the cells
    =IF(B29<1,0,ROUNDUP(((B29/Input!$C$3/Input!$C$19/Schedule!B30)-B35)>0,0))
    =IF(2340<1,0,ROUNDUP(((2340/20/4/20)-1)>0,0))

    All I want the ROUNDUP function to do is to make the current cell (of the calculation answer) roundup anything with a decimal to a full number. The formula above works for 1 and 0, but not 2 or 3. It even works for negatives.

    Please help...
  • Apr 4, 2012, 03:23 AM
    ScottGem
    Try this:
    =IF(B29<1,0,ROUNDUP(((B29/Input!$C$3/Input!$C$19/Schedule!B30)-B35),0))

    I don't know why you had the >0 in there. But what that does is return either a 0 or 1, not the results of the arithmetic.
  • Apr 4, 2012, 04:11 AM
    miramss
    The only problem with this calculation is that it produces negative rounded numbers. I would like the negative numbers to round up to 0 (not -1). Do you know of a solution?
    Thanks so much for your help.
  • Apr 4, 2012, 04:29 AM
    ScottGem
    Then you need to nest an IF:

    =IF(B29<1,0,IF(((B29/Input!$C$3/Input!$C$19/Schedule!B30)-B35)<0,0,Roundup(((B29/Input!$C$3/Input!$C$19/Schedule!B30)-B35))))

    So, If B29 is <1, you get a 0. Otherwise it does the calculation. If the result of the calculation is less than 0, then it returns a 0 otherwise it returns the rounded calc.
  • Apr 5, 2012, 03:56 PM
    JBeaucaire
    Maybe you can employ the MAX function to make sure it never goes below 0.

    =Roundup(B29/Input!$C$3/Input!$C$19/Schedule!B30)-B35)

    =MAX(0, Roundup(B29/Input!$C$3/Input!$C$19/Schedule!B30)-B35))

  • All times are GMT -7. The time now is 01:20 PM.