# 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

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

#### 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
1
Views
48
Replies
9
Views
125
Replies
1
Views
107
Replies
7
Views
88
Replies
12
Views
153

1,127,898
Messages
5,627,526
Members
416,250
Latest member
darius_rebelo

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

### Which adblocker are you using?

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