+ Reply to Thread
Results 1 to 8 of 8

I need helping adding ROUND into this formula

  1. #1
    Registered User
    Join Date
    10-14-2014
    Location
    Texas
    MS-Off Ver
    MS Office 2010
    Posts
    6

    I need helping adding ROUND into this formula

    I have created a spreadsheet that utilizes a drop down list. What I am looking to do is when "BRK" or "BRK*2" is used, the final answer will be rounded to the next tens.
    This is the formula I am using. Where should the ROUND be included?

    =IF(A8="BRK",G8*0.25,IF(A8="BRK x2",G8*0.25*2,IF(A8="SLD",G8*1,IF(A8="SLD x2",G8*2,IF(A8="DBL",G8*2,IF(A8="DBL x2",G8*4,IF(A8="@40",G8/40,IF(A8="@80",G8/80,IF(A8="@80 x2",G8/80*2,IF(A8="@40 x2",G8/40*2))))))))))

    When "BRK" or "BRK*2" I get an answer of 1687. I need that total to be rounded to 1690.

    Thanks,

  2. #2
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: I need helping adding ROUND into this formula

    Try

    =ROUND(yourlongformula,-1)

  3. #3
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: I need helping adding ROUND into this formula

    Try this:

    =IF(A8="BRK",ROUND(G8*0.25,-1),IF(A8="BRK x2",ROUND(G8*0.25*2,-1),IF(A8="SLD",G8*1,IF(A8="SLD x2",G8*2,IF(A8="DBL",G8*2,IF(A8="DBL x2",G8*4,IF(A8="@40",G8/40,IF(A8="@80",G8/80,IF(A8="@80 x2",G8/80*2,IF(A8="@40 x2",G8/40*2))))))))))

    Additions shown in red.

    Hope this helps.

    Pete

  4. #4
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: I need helping adding ROUND into this formula

    Ah, sorry...
    My suggestion would do the rounding in all cases, not tjust the BRK and BRK*2

  5. #5
    Registered User
    Join Date
    10-14-2014
    Location
    Texas
    MS-Off Ver
    MS Office 2010
    Posts
    6

    Re: I need helping adding ROUND into this formula

    Pete

    Thank you so much, it worked. If you do not mind, I had a couple of other things I needed help with.

    The fist is, referring back to the ROUNDing from above, is it possible to get my number to round up even if the value is below 5? For example, when (G8*0.25=123.46), I would like for the answer to be rounded to 130.

    Second, on an entire different spreadsheet, I have to take those numbers and add items to them. Is it possible to create a sheet where I can enter an item number in one cell and have the description of the item populate the next cell over automatically?

    Again, thank you very much.

  6. #6
    Registered User
    Join Date
    10-14-2014
    Location
    Texas
    MS-Off Ver
    MS Office 2010
    Posts
    6

    Re: I need helping adding ROUND into this formula

    Nonetheless, thank you for your help, Jonmo1.

  7. #7
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: I need helping adding ROUND into this formula

    Use Ceiling instead of round

    =CEILING(G8*0.25,10)


    And FYI, FLOOR is the opposite of ceiling....to round down.

  8. #8
    Registered User
    Join Date
    10-14-2014
    Location
    Texas
    MS-Off Ver
    MS Office 2010
    Posts
    6

    Re: I need helping adding ROUND into this formula

    Jonmo1, that is exactly what i wanted. Thanks very much for your help.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 2
    Last Post: 08-29-2014, 03:12 AM
  2. adding round function to if formula
    By jrortiz2 in forum Excel General
    Replies: 1
    Last Post: 10-01-2011, 06:28 AM
  3. Request for helping the right formula creation
    By PRADEEPB270 in forum Excel General
    Replies: 1
    Last Post: 05-06-2010, 06:45 AM
  4. Need helping adding Rows
    By rbpd5015 in forum Excel General
    Replies: 0
    Last Post: 04-06-2010, 02:00 PM
  5. Need helping with my formula please...
    By Altstatten in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-14-2006, 04:50 PM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1