1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Roundup within a if, product formula

Discussion in 'Business Applications' started by Mcullin, Nov 9, 2011.

Thread Status:
Not open for further replies.
Advertisement
  1. Mcullin

    Mcullin Thread Starter

    Joined:
    Nov 9, 2011
    Messages:
    7
    =IF((PRODUCT(C6*B7)/144)<10, PRODUCT((C6*B7)/144*3.84+10)*1.3, PRODUCT(C6*B7)/144*3.84*1.3)

    I need to figure out how to get this to round up the (C6*B7)/144) to the 0 decimal.

    example would be 36"*15"=540/144=3.75 (I need 3.75 to roundup to 4) but let the rest of the formula continue. If less than 10 sq ft and $10 fee *1.3. If 10 sq ft or more then *1.3 only.

    Any help would be greatly appreciated.
    Thanks,
    Michael
     
  2. Rockn

    Rockn

    Joined:
    Jul 29, 2001
    Messages:
    21,334
    Use the ROUND function and the number of decimal points.
     
  3. Mcullin

    Mcullin Thread Starter

    Joined:
    Nov 9, 2011
    Messages:
    7
    Using the round or roundup is kinda of obvious, my problem is where? do I replace Product?
     
  4. Rockn

    Rockn

    Joined:
    Jul 29, 2001
    Messages:
    21,334
    If(Round(Product(C6*B7)/144),0)<10

    Something like that
     
  5. Mcullin

    Mcullin Thread Starter

    Joined:
    Nov 9, 2011
    Messages:
    7
    =IF(ROUND(PRODUCT(D4*B5/144),0)<10, PRODUCT((D4*B5)/144*3.84+10)*1.3, PRODUCT(D4*B5)/144*3.84*1.3)
    Didn't work on rounding up to the next whole square foot. But at least this time it did not give any kind of error either.
     
  6. Rockn

    Rockn

    Joined:
    Jul 29, 2001
    Messages:
    21,334
    Put the function alone into a cell and see if it is rounding correctly.
     
  7. Mcullin

    Mcullin Thread Starter

    Joined:
    Nov 9, 2011
    Messages:
    7
    =ROUND(((D4*B5)/144),0) worked at rounding up but the second I add the product or if it stops working
     
  8. Rockn

    Rockn

    Joined:
    Jul 29, 2001
    Messages:
    21,334
    The product function is used to multiply values of cells. PRODUCT(A1,A2,etc)

    You are already doing that with C6*B7 so you do not need the PRODUCT function at all.
     
  9. Mcullin

    Mcullin Thread Starter

    Joined:
    Nov 9, 2011
    Messages:
    7
    Okay I got that the product does not need to be there for the short round up only formula, but what about a more complex one.
    =IF(ROUND((D4*B5/144),0)<10,((D4*B5)/144*3.84+10)*1.3,(D4*B5)/144*3.84*1.3). This still calculates without the round up. or
    =IF(ROUND((D4*B5/144),0)<10,product((D4*B5)/144*3.84+10)*1.3,product(D4*B5)/144*3.84*1.3)
    neither of these are rounding up the sq ft to the next whole number and then calculating the rest of the formula. Even though the short round up formula does work to roundup only.
    I do appreciate your patience and assistance and sorry if I am not communicatiing what it is I need.
    Michael
     
  10. Rockn

    Rockn

    Joined:
    Jul 29, 2001
    Messages:
    21,334
    You will need to add the round function to each of the instances within the If function.

    =IF(ROUND((D4*B5/144),0)<10,Round(((D4*B5)/144*3.84+10)*1.3),0),Round((D4*B5)/144*3.84*1.3),0)

    I am not sure I put it in the correct place for your usage, but you should get the idea.
     
  11. Mcullin

    Mcullin Thread Starter

    Joined:
    Nov 9, 2011
    Messages:
    7
    =IF((ROUND((D4*B5/144),0)<10,Round(((D4*B5)/144*3.84+10)*1.3),0),Round((D4*B5)/144*3.84*1.3),0)
    This gives an error message as too few arguments. i think I tried this one yesterday night.
     
  12. Rockn

    Rockn

    Joined:
    Jul 29, 2001
    Messages:
    21,334
    You might be missing a round bracket at the end
     
  13. Rockn

    Rockn

    Joined:
    Jul 29, 2001
    Messages:
    21,334
    =if(round((d4*b5/144),0)<10,round(((((d4*b5)/144)*3.84)+10)*1.3,0),round((((d4*b5)/144)*3.84)*1.3,0))
     
  14. Mcullin

    Mcullin Thread Starter

    Joined:
    Nov 9, 2011
    Messages:
    7
    Finally got it. I appreciate the help, it was invaluble.

    =IF(ROUNDUP(C4*B5/144,0)<10,(ROUNDUP(C4*B5/144,0)*3.84+10)*1.3,(ROUNDUP(C4*B5/144,0)*3.84*1.3))
     
  15. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1026146

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice