Offset from MAX value

[gtd526]

Hello,
Offset from MAX value of certain cells: MAX(BB6,BB15,BB24,BG6,BG15,BG24,BB33,BG33)
Which does give me the MAX value, but I want to Offset from that cell(MAX value) by (-2,-2).
Thank you.

 

[pgc01]

Remark: the formula that I posted would solve a more complex problem, where the location of the cells you are using have ao pattern.

Looking at post 8, if that's the real layout of the data you simply want to find the max value in columns BB and BG.
If that's the case I would use a much simpler formula.
I'd try to find the max in BB and if not there then would find it in BG.
Although a bigger formula it's very simple, not array, no names necessary, not volatile:

=IFERROR(INDEX(AX4:AX31,MATCH(MAX(BB:BB,BG:BG),BB6:BB33,0)),INDEX(BC4:BC31,MATCH(MAX(BB:BB,BG:BG),BG6:BG33,0)))

 

[gtd526]

Remark: the formula that I posted would solve a more complex problem, where the location of the cells you are using have ao pattern.

Looking at post 8, if that's the real layout of the data you simply want to find the max value in columns BB and BG.
If that's the case I would use a much simpler formula.
I'd try to find the max in BB and if not there then would find it in BG.
Although a bigger formula it's very simple, not array, no names necessary, not volatile:

=IFERROR(INDEX(AX4:AX31,MATCH(MAX(BB:BB,BG:BG),BB6:BB33,0)),INDEX(BC4:BC31,MATCH(MAX(BB:BB,BG:BG),BG6:BG33,0)))

Works perfect. Thank you.
How do you create an array as a named range? #19 shows what I have as named ranges. I copied the arrays from your previous post. Maybe you type in the bracket and select cells you want to include??

 

[pgc01]

Hi

I'm not sure I understand correctly, but maybe there's a confusion.

You say:

How do you create an array as a named range?

The array is not a named range, it's not a range at all.
The array is an array of strings. In this case the strings are cells adresses but still just strings.
We defined a name that refers to the array so that we can use it in a formula and make the formula easier to write and read.
You can think of this Name as a shorthand abbreviation.


A Name may refer to several things
- a range, in this case the range will be a named range because you can refer to the range by a name
- an array, like in this case. It will be a named array
- a constant in which case we have a named constant

To define a name you can simply use the Ribbon->Formulas->Name Manager->New and write whatever you need in the Refers To box.

For ex.
Name: Animals
Refers to: ={"Tiger","Lion","Bear","Leopard"}

Having defined this name you can then refer to the array by name, like:

=INDEX(Animals,3)

In post #19, the CellsList name refers to an array and is just that, an array with strings, it's not a range.
The MyList name, however, does refer to a range, in this case a multi-area range.

Hope this helps
or, if I misunderstood, please post back.