MIN and MAX with Named Ranges

[ephilli2]

Hello All,

I'm encountering limitations using the MIN and MAX functions when referring to named ranges. When I refer to a named range using simple sum calculations, the formula considers only the cells in the named range in the same row. However, when I use MIN or MAX functions, the formula looks at all values in the named range, not just those in the same row as the formula.

This can be confusing to explain in words, so please see the attached file for examples. Is there a way to limit Excel to look at specific rows when using named ranges, specifically regarding MIN and MAX functions?

Please advise; thanks!

[daddylonglegs]

Why don't you just use =MAX(B3,C3) etc?

It seems logical to me that if you use a formula like MAX(apples,oranges) that you would get the maximum value from the whole range or ranges. This would apply to the SUM formula too if you used =SUM(apples,oranges) or used your original formula confirmed with CTRL+SHIFT+ENTER

[ephilli2]

Hi daddylonglegs,

The example I provided is just to illustrate my point. My real worksheet is 250+ columns long and lists 100+ rows of data. The keep formulas somewhat comprehensible, I began using named ranges, but I discovered the limitation when MIN and MAX are involved. As a workaround, I ended up reverting back to the original formulas, using cell references rather than named ranges, but I was just wondering if there was a way to specify a row number when using a named range.

[Paul]

Hi ephilli,

You could try the somewhat more complex formula:

Please Login or Register  to view this content.

These are array formulas, and need to be committed using CTRL+SHIFT+ENTER

[ephilli2]

Hi Pjoaquin,

Good idea! I'll see if I can incorporate the ROW function to narrow the named range down to a specific row.

Thanks!