+ Reply to Thread
Results 1 to 8 of 8

Combining MAX, ROUNDUP & IF for the same cell

  1. #1
    Registered User
    Join Date
    02-17-2010
    Location
    New York
    MS-Off Ver
    Excel 2003
    Posts
    9

    Question Combining MAX, ROUNDUP & IF for the same cell

    Hello!
    I am trying to create one formula (in one cell) so that it performs the following three things:
    1) Rounds any number to the nearest dollar
    2) Makes the minimum dollar value $2.00
    3) If another cell (G2) reaches $100.00 or more, I want it to revert to another cell's (B2) value.

    So, in essence, I want to combine the following two things:

    =MAX(2,ROUNDUP(F2,0))
    AND
    =IF(G2>="100",B2)

    I do not know how to correctly order these so that it performs all three things I'm looking for it to do. I've posted this before, but was given an answer that created a circular reference and did not make the minimum value $2.00.
    Any help would be appreciated!
    Thanks in advance!
    Last edited by babydee0413; 02-22-2010 at 02:51 PM.

  2. #2
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Combining MAX, ROUNDUP & IF for the same cell

    Presumably the G2 test must take precedence:

    =IF(G2>=100,B2,MAX(2,ROUNDUP(F2,0))

    If the cell in which this function resides is any of G2,B2,F2 or any of G2,B2,F2 reference this cell in their calculations then this would be a circular calculation.

  3. #3
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Combining MAX, ROUNDUP & IF for the same cell

    Based on the info given, your two formulas are pretty much ready to go, simply insert the first formula into the missing FALSE section of the second formula:

    =IF(G2>="100", B2, MAX(2,ROUNDUP(F2,0)))
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  4. #4
    Registered User
    Join Date
    02-17-2010
    Location
    New York
    MS-Off Ver
    Excel 2003
    Posts
    9

    Re: Combining MAX, ROUNDUP & IF for the same cell

    It does still create a circular calculation because I'm entering the formula in cell G2, but it needs to check G2 to see if it's 100 or more. Should I change my formula?

  5. #5
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Combining MAX, ROUNDUP & IF for the same cell

    If you put a formula IN cell G2 that refers to itself, that is a circular reference. You will continue to receive errors until the formula stops referring to itself.
    Last edited by JBeaucaire; 02-22-2010 at 01:38 PM.

  6. #6
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Combining MAX, ROUNDUP & IF for the same cell

    How about this:

    =IF(MAX(2,ROUNDUP(F2,0))>100, B2, MAX(2,ROUNDUP(F2,0)))

  7. #7
    Registered User
    Join Date
    02-17-2010
    Location
    New York
    MS-Off Ver
    Excel 2003
    Posts
    9

    Re: Combining MAX, ROUNDUP & IF for the same cell

    Yes this worked; thank you!

  8. #8
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Combining MAX, ROUNDUP & IF for the same cell

    Quote Originally Posted by babydee0413 View Post
    1) Rounds any number to the nearest dollar
    Note: ROUNDUP, as the name implies, won't round to the nearest dollar, it rounds to the next higher dollar, so $2.01 will round to $3. If you want to round to the nearest dollar use ROUND

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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