+ Reply to Thread
Results 1 to 8 of 8

Formula to Check Max Value in Multiple Sections with Data

  1. #1
    Registered User
    Join Date
    08-12-2018
    Location
    America
    MS-Off Ver
    2016
    Posts
    3

    Formula to Check Max Value in Multiple Sections with Data

    I have a very long list (14000+ rows) that is separated by category and each category has different values associated with it.

    For example:

    Category 1 65465
    Category 1 23563
    Category 1 32165

    etc.
    The category list is extensive, and the amount of values per category also varies (some are 47+).

    What i'm looking for is to have a formula auto-adjust to the amount of values for that category and then give me the Max value from that list.

    The current formula i have now is:
    =MAX(INDIRECT(ADDRESS(IF(ROW()-1="",ROW()-1,IF(ROW()-2="",ROW()-2,IF(ROW()-3="",ROW()-3,IF(ROW()-4="",ROW()-4,IF(ROW()-5="",ROW()-5,IF(ROW()-6="",ROW()-6,IF(ROW()-7="",ROW()-7,IF(ROW()-8="",ROW()-8,IF(ROW()-9="",ROW()-9,IF(ROW()-10="",ROW()-10,IF(ROW()-11="",ROW()-11,IF(ROW()-12="",ROW()-12,IF(ROW()-13="",ROW()-13,IF(ROW()-14="",ROW()-14,IF(ROW()-15="",ROW()-15,IF(ROW()-16="",ROW()-16,IF(ROW()-17="",ROW()-17,IF(ROW()-18="",ROW()-18,IF(ROW()-19="",ROW()-19,IF(ROW()-20="",ROW()-20,IF(ROW()-21="",ROW()-21,IF(ROW()-22="",ROW()-22,IF(ROW()-23="",ROW()-23,IF(ROW()-24="",ROW()-24,IF(ROW()-25="",ROW()-25,IF(ROW()-26="",ROW()-26,IF(ROW()-27="",ROW()-27,IF(ROW()-28="",ROW()-28,IF(ROW()-29="",ROW()-29,IF(ROW()-30="",ROW()-30,IF(ROW()-31="",ROW()-31,IF(ROW()-32="",ROW()-32,IF(ROW()-33="",ROW()-33,IF(ROW()-34="",ROW()-34,IF(ROW()-35="",ROW()-35,IF(ROW()-36="",ROW()-36,IF(ROW()-37="",ROW()-37,IF(ROW()-38="",ROW()-38,IF(ROW()-39="",ROW()-39,IF(ROW()-40="",ROW()-40,IF(ROW()-41="",ROW()-41,IF(ROW()-42="",ROW()-42,IF(ROW()-43="",ROW()-43,IF(ROW()-44="",ROW()-44,IF(ROW()-45="",ROW()-45,IF(ROW()-46="",ROW()-46,IF(ROW()-47="",ROW()-47,IF(ROW()-48="",ROW()-48,IF(ROW()-49="",ROW()-49,IF(ROW()-50="",ROW()-50,IF(ROW()-51="",ROW()-51,IF(ROW()-52="",ROW()-52,IF(ROW()-53="",ROW()-53,IF(ROW()-54="",ROW()-54,IF(ROW()-55="",ROW()-55,IF(ROW()-56="",ROW()-56,IF(ROW()-57="",ROW()-57,IF(ROW()-58="",ROW()-58,IF(ROW()-59="",ROW()-59,IF(ROW()-60="",ROW()-60,IF(ROW()-61="",ROW()-61,IF(ROW()-62="",ROW()-62,IF(ROW()-63="",ROW()-63,IF(ROW()-64="",ROW()-64,IF(ROW()-65="",ROW()-65,IF(ROW()-66="",ROW()-66,IF(ROW()-67="",ROW()-67,IF(ROW()-68="",ROW()-68,IF(ROW()-69="",ROW()-69,IF(ROW()-70="",ROW()-70,IF(ROW()-71="",ROW()-71,IF(ROW()-72="",ROW()-72,IF(ROW()-73="",ROW()-73,IF(ROW()-74="",ROW()-74,IF(ROW()-75="",ROW()-75,IF(ROW()-76="",ROW()-76,IF(ROW()-77="",ROW()-77,IF(ROW()-78="",ROW()-78,IF(ROW()-79="",ROW()-79,IF(ROW()-80="",ROW()-80,IF(ROW()-81="",ROW()-81,IF(ROW()-82="",ROW()-82,IF(ROW()-83="",ROW()-83,IF(ROW()-84="",ROW()-84,IF(ROW()-85="",ROW()-85,IF(ROW()-86="",ROW()-86,IF(ROW()-87="",ROW()-87,IF(ROW()-88="",ROW()-88,IF(ROW()-89="",ROW()-89,IF(ROW()-90="",ROW()-90,IF(ROW()-91="",ROW()-91,IF(ROW()-92="",ROW()-92,IF(ROW()-93="",ROW()-93,IF(ROW()-94="",ROW()-94,IF(ROW()-95="",ROW()-95,IF(ROW()-96="",ROW()-96,IF(ROW()-97="",ROW()-97,IF(ROW()-98="",ROW()-98,IF(ROW()-99="",ROW()-99,IF(ROW()-100="",ROW()-100,ROW()-100)))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))),COLUMN())):INDIRECT(ADDRESS(ROW()-1,COLUMN())))

    As you can see, it has 100 nested IF statements, which in turn throws the error stating that there are too many Nests.
    I don't know how to do a loop in the formula which is why i resulted to this long formula with all of the nested IFs.
    Basically all the formula is doing is checking to see if the row above it is empty or not and if it is, it will stop there and use that address as the starter point for the MAX formula.

    Is there any way to have this shortened so i can paste it below any section and have it adjust to the amount of values above it. Each category is separated by a blank row.

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Excel 2007
    Posts
    16,188

    Re: Formula to Check Max Value in Multiple Sections with Data

    It would help if you attached a sample Excel workbook.

    To do this, click on Go Advanced (below the Edit Window) while you are composing a reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and then on Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post. Don't try to use the Paperclip icon (Attachments button), as it doesn't work on this forum.

    Hope this helps.

    Pete

  3. #3
    Registered User
    Join Date
    08-12-2018
    Location
    America
    MS-Off Ver
    2016
    Posts
    3

    Re: Formula to Check Max Value in Multiple Sections with Data

    Unfortunately i am unable to provide the exact Excel sheet i am working on, as it contains confidential information.

    But as a brief example of what it consists of:

    Category ----- Number

    Category 1 654654
    Category 1 631531
    Category 1 998877
    Category 1 111111
    Category 1 847444
    (Formula here) - This would grab the highest number out of these 5

    Category 2 877779
    Category 2 887222
    Category 2 987872
    (Formula here) - This would grab the highest number out of these 3

    Category 3 333145
    Category 3 435432
    Category 3 464642
    Category 3 787999
    Category 3 864341
    Category 3 775222
    Category 3 388888
    (Formula here) - This would grab the highest number out of these 7

    I just want to copy and paste the formula and have it auto-adjust to the amount of values between each blank rows.

    I really hope this helps!

  4. #4
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Excel 2007
    Posts
    16,188

    Re: Formula to Check Max Value in Multiple Sections with Data

    Take a look at the MAXIFS function on Excel Help - I think it is available for your version.

    It looks like you have a blank row underneath each category. Suppose you were to put the formula in cell B7 (based on your example above), The formula would be:

    =MAXIFS(B$2:B6,A$2:A6,A6)

    Note that the range goes up to the row above the one in which you put the formula (shown in red). You might want to format that cell so that it stands out, e.g. Bold and Red. Then you can copy the formula and paste it into the next blank cell (i.e. B11 in your example), then into the next blank cell (B19), and so on.

    Alternatively, you could put this in C2:

    =IF(B2="",MAXIFS(B$2:B2,A$2:A2,A1),"")

    and copy it down column C - the result for each section will only show on the blank rows for each category.

    Hope this helps.

    Pete

  5. #5
    Valued Forum Contributor
    Join Date
    05-05-2018
    Location
    Oakland, CA
    MS-Off Ver
    2016
    Posts
    628

    Re: Formula to Check Max Value in Multiple Sections with Data

    Hi,

    If you don't have Office 365, then use this Array formula, to be confirmed by CSE (Control, Shift, Enter)

    You can put the formula in the Blank row between Categories or anywhere on your sheet.

    Excel 2016 (Windows) 64 bit
    A
    B
    1
    Category 1
    654654
    2
    Category 1
    631531
    3
    Category 1
    998877
    4
    Category 1
    111111
    5
    Category 1
    847444
    6
    7
    Category 2
    877779
    8
    Category 2
    887222
    9
    Category 2
    987872
    10
    11
    Category 3
    333145
    12
    Category 3
    435432
    13
    Category 3
    464642
    14
    Category 3
    787999
    15
    Category 3
    864341
    16
    Category 3
    775222
    17
    Category 3
    388888
    18
    19
    Category 1
    998877
    20
    Category 2
    987872
    21
    Category 3
    864341
    Sheet: Sheet31

    Excel 2016 (Windows) 64 bit
    B
    19
    =MAX(IF(A$1:A$17=A19,B$1:B$17))
    Sheet: Sheet31

  6. #6
    Registered User
    Join Date
    08-12-2018
    Location
    America
    MS-Off Ver
    2016
    Posts
    3

    Re: Formula to Check Max Value in Multiple Sections with Data

    Yes! The MAXIFS function was able to get me what i needed. However, that function is apparently only available to Office 365 users, which i am, but i am unable to use this function in the Excel program. I seem to only be able to use it in the web version of Excel. But that is an issue on its own. Thank you for the help!

  7. #7
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365/2016
    Posts
    8,605

    Re: Formula to Check Max Value in Multiple Sections with Data

    Try this
    Enter in B19 and copy down

    regular formula
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    v A B
    1 Category 1 654654
    2 Category 1 631531
    3 Category 1 998877
    4 Category 1 111111
    5 Category 1 847444
    6
    7 Category 2 877779
    8 Category 2 887222
    9 Category 2 987872
    10
    11 Category 3 333145
    12 Category 3 435432
    13 Category 3 464642
    14 Category 3 787999
    15 Category 3 864341
    16 Category 3 775222
    17 Category 3 388888
    18
    19 Category 1 998877
    20 Category 2 987872
    21 Category 3 864341
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne

  8. #8
    Valued Forum Contributor
    Join Date
    05-05-2018
    Location
    Oakland, CA
    MS-Off Ver
    2016
    Posts
    628

    Re: Formula to Check Max Value in Multiple Sections with Data

    Quote Originally Posted by Garyyyyyyyyyy View Post
    Yes! The MAXIFS function was able to get me what i needed. However, that function is apparently only available to Office 365 users, which i am, but i am unable to use this function in the Excel program. I seem to only be able to use it in the web version of Excel. But that is an issue on its own.
    That's why I provided a Non-Office 365 version in my Post #5

+ 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