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.
Bookmarks