1

So I'm wondering how to find the min/max of a named range based on the value in another column. 

Col A    Col B    Col C
S_1        x      x max =                    
S_2        y      x min = 
S_3        y      y max = 
S_4        x      y min = 
S_5        x

So basically, I want to find the max/min of the named ranges S_1 through S_5 that correspond to an x in Col B separate from the max/min values that correspond to a y in Col B. The named ranges dynamically refer to data in a separate sheet (sheet2), and are equal to the formula =OFFSET(Sheet2!$A$10, Sheet1!$E$6, Sheet1!$F$25, Sheet1!$E$9,1), so each named range can refer to a different range of data based on other cells in Sheet1.

I've tried using the indirect function, but it returns a REF because I believe it doesn't work since the named range is an offset? I'd rather not have to use a macro if I can. Any ideas on how to go about this?

bakeson
  • 91
  • 1
  • 2
  • 9
  • 1
    I don't completely follow your dataset, but this sounds like a pivot table could be very useful here. It would let you rollup the B column by x or y, and take aggregations such as min or max. – Tim Biegeleisen Jul 01 '16 at 14:47
  • 1
    for the min do something like this `=MIN(IF($B$1="x",INDIRECT(A1),1E+99),IF($B$2="x",INDIRECT(A2),1E+99),IF($B$3="x",INDIRECT(A3),1E+99),IF($B$4="x",INDIRECT(A4),1E+99),IF($B$5="x",INDIRECT(A5),1E+99))` For the max change the `1E+99` to `-1E+99` – Scott Craner Jul 01 '16 at 14:54
  • is Col A always sorted? if so, using an array formula from `http://www.cpearson.com/excel/TablesAndLookups.aspx` will work, using the arbitary lookup and find the first and last matches. If it is not sorted, are you able to turn it into numbers only? if so, you can then use a similar lookup to find the max and min values relatively easily. – Bryan Davies Jul 01 '16 at 15:42
  • @BryanDavies the list is not what the OP wants for the MIN/MAX. Column A is a list of Named ranges. The OP wants to find the min and max in those named ranges not column A itself. – Scott Craner Jul 01 '16 at 16:07
  • 1
    Ah I see.... Scott, may I ask why: `{=MAX(INDIRECT(IF(D4:D8="x",C4:C8,FALSE)))}` doesn't work? – Bryan Davies Jul 01 '16 at 16:30
  • I guess it does, I was putting the indirect in the wrong place, so could not make it work as an array, hence the comment instead of an answer. I would post that as an answer. Though I would put a ridiculous low option as the False, just in case the numbers are negative. False = 0, and may cause a false result. @BryanDavies – Scott Craner Jul 01 '16 at 16:34
  • well it doesn't seem to work... it only grabs S_1 instead of all of them. I'm not sure indirect works with arrays – Bryan Davies Jul 01 '16 at 16:37
  • @BryanDavies okay I should have tested it more, You are correct in that it only looks at the first. I guess it doesn't, which is interesting in that I use it in other array formulas regularly as a counting mechanism. – Scott Craner Jul 01 '16 at 16:42
  • I think as an argument, it isn't an array... but in this case, it is acting like one, which it cannot do. Unless you have a trick? – Bryan Davies Jul 01 '16 at 16:44
  • @BryanDavies If the set of named ranges was short I would do as I said, if it were long I would write a UDF to cycle through the names and use UNION to create a range object to which I could find the min and max. – Scott Craner Jul 01 '16 at 16:54

0 Answers0