Offset from MAX value

gtd526

Active Member
Joined
Jul 30, 2013
Messages
338
Office Version
  1. 2019
Platform
  1. Windows
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
Joined
Apr 25, 2006
Messages
19,884
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 create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

gtd526

Active Member
Joined
Jul 30, 2013
Messages
338
Office Version
  1. 2019
Platform
  1. Windows
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
Joined
Apr 25, 2006
Messages
19,884
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,299
Messages
5,635,391
Members
416,856
Latest member
silentir

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top