Offset from MAX value

gtd526

Active Member
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

MrExcel MVP
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)))

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

gtd526

Active Member
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

MrExcel MVP
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.

Replies
2
Views
43
Replies
8
Views
61
Replies
1
Views
74
Replies
9
Views
134
Replies
1
Views
140

1,129,403
Messages
5,636,080
Members
416,896
Latest member
Hozier

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back