Help with combining OFFSET and MAX Function

**oyz79** · 02-15-2013, 12:57 AM

I have a spreadsheet with sets of numeric values in cells R54:Y54, R55:Y55, R56:Y56, etc.

I want to enter a formula in Z54 which returns the value in the cell below the cell with the highest value in R54:Y54. In other words, if S54 contains the highest value in R54:Y54, I want the formula in Z54 to return the value in S55 (which is the cell below S54).

It should be easy, I have a start but it's not what I'm looking for, it's returning the highest value in the next row =MAX(R54: (OFFSET(Y54,1,0)))

**vlady** · 02-15-2013, 01:11 AM

try it like this
=index(R55:Y55,match(max(R54:Y54),R54:Y54))

**oyz79** · 02-15-2013, 01:30 AM

I think that gets me closer, but it's still returning the inproper value.

Here is an example of the first three rows of data. When I use your formula it returns 0.094026678. It should return 0.110917104, which is the number directly below the highest value in the first row.

0.081477039 0.057894072 0.093630624 0.079991513 0.082844357 0.065840149 0.092406184 0.072061874
0.110089401 0.085882506 0.110917104 0.097062406 0.106385246 0.089011369 0.114787811 0.094026678
0.079926547 0.066077363 0.069295308 0.0643637 0.070181533 0.062582418 0.079161143 0.067954872

**vlady** · 02-15-2013, 01:36 AM

can you upload a sample with those data and the formula you are using.thanks.

To attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include BEFORE/AFTER sheets if needed to show the process you're trying to complete or automate. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

**oyz79** · 02-15-2013, 10:13 AM

Originally Posted by vlady

can you upload a sample with those data and the formula you are using.thanks.

To attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include BEFORE/AFTER sheets if needed to show the process you're trying to complete or automate. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

Attached is a sample, the cells are different but idea is the same

**oyz79** · 02-15-2013, 09:01 PM

Originally Posted by oyz79

Attached is a sample, the cells are different but idea is the same

Did my attachment help?

**icestationzbra** · 02-15-2013, 11:18 PM

in the attachment that you uploaded, you can use this in cell J2:

Please Login or Register  to view this content.

**oyz79** · 02-16-2013, 05:24 PM

Awesome, thank you so much!

**oyz79** · 02-16-2013, 06:18 PM

Originally Posted by icestationzbra

in the attachment that you uploaded, you can use this in cell J2:

Please Login or Register  to view this content.

I have one other issue - if the cells aren't in rows next to each other, how could the formula be written?

I have attached a sample workbook. I want a formula which identifies the strategy with the highest value in J3:q3 and then generates the value in cells a4:h4 that relate to the same strategy. For example, strategy 3 is highest in j3:q3, so write a formula that generates the value in c4

**daddylonglegs** · 02-16-2013, 06:23 PM

You can adjust the ranges as required - try this formula

=INDEX(A4:H4,MATCH(MAX(J3:Q3),J3:Q3,0))

Help with combining OFFSET and MAX Function

LinkBack

Thread Tools

Rate This Thread

Display

Help with combining OFFSET and MAX Function

Re: Help with combining OFFSET and MAX Function

Re: Help with combining OFFSET and MAX Function

Re: Help with combining OFFSET and MAX Function

Re: Help with combining OFFSET and MAX Function

Re: Help with combining OFFSET and MAX Function

Re: Help with combining OFFSET and MAX Function

Re: Help with combining OFFSET and MAX Function

Re: Help with combining OFFSET and MAX Function

Re: Help with combining OFFSET and MAX Function

Thread Information

Users Browsing this Thread

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions