How can I change this formula?

[michaelhunter]

Hi folks,

GG26:GR26 contains simple formulas that produce values ranging from about 10 to 60 or so. These values change day to day.

GG27:GR27 also contains simple formulas that produce values ranging from 1 to 12. For example:

32 42 14 54 59 44 17 23 31 58 39 42
01 02 03 04 05 06 07 08 09 10 11 12
5

In GG28 I need the value in GG27:GR27 that corresponds to the highest one in GG26:GR26. In the example above, 59 is the highest value in GG26:GR26 and the number 5 is the corresponding value in GG27:GR27.

So in GG28 I have the formula:

=IF(ROWS($GG$28:GG28)<=COUNTIF($GG$26:$GR$26,MAX($GG$26:$GR$26)),
INDEX($GG$27:$GR$27,SMALL(IF($GG$26:$GR$26=MAX($GG$26:$GR$26),
COLUMN($GG$27:$GR$27)-COLUMN($GG$27)+1),ROWS($GG$28:GG28))),"")

entered with control+shift+enter and it returns the correct value: 5.

Now, in GH28 I need the value in GG27:GR27 that corresponds to the SECOND highest one in GG26:GR26. In the example, 58 would be the second highest value in GG26:GR26 and the corresponding value in GG27:GR27 is 10.

So in GH28 I would need that value: 10.

How can I modify the above formula to get it?

I've tried following a suggestion in a previous post but I haven't been able to make the right adjustment.

Once I know how to modify the formula, I think I can get the third, fourth and so on.

I would appreciate your suggestions.

 

[ukmikeb]

Michael

Whilst the original Index formula that I supplied with adjustments by Peter SSs works apparently satisfactorily, if the case that there is 42 in cells 2 and 12 respectively is a true sample of your data then IMHO that formula does not produce the correct result!

Hopefully, the following does (in GG28 and copied across) -

=IF(AND((COLUMNS($GG$26:GG$26)>=2),LARGE($GG$26:$GR$26,COLUMNS($GG$26:GG$26))=LARGE($GG$26:$GR$26,COLUMNS($GG$26:GG$26)-(--(COLUMNS($GG$26:GG$26)>1)))),MATCH(LARGE($GG$26:$GR$26,COLUMNS($GG$26:GG$26)),OFFSET($GG$26,0,MATCH(LARGE($GG$26:$GR$26,COLUMNS($GG$26:GG$26)),$GG$26:$GR$26,0)+1,1,COLUMNS($GG$26:$GR$26)-MATCH(LARGE($GG$26:$GR$26,COLUMNS($GG$26:GG$26)),$GG$26:$GR$26,0)))+MATCH(LARGE($GG$26:$GR$26,COLUMNS($GG$26:GG$26)),$GG$26:$GR$26,0)+1,INDEX($GG$27:$GR$27,,MATCH(LARGE($GG$26:$GR$26,COLUMNS($GG$26:GG$26)),$GG$26:$GR$26,0)))

A brief explanation of the formula is as follows -

1. Test if the current column is > 1 and that the result of the LARGE formula for the current column equals the result of the LARGE formula for the previous column

2. Test is True then find the appropriate value in row 27 through MATCH statements determining the next instance of the value returned by the LARGE formula in that part of the array beginning one column to the right of the previous instance of the value returned by the LARGE formula.

3. Test is False by INDEX find the appropriate value in row 27

Please ask if you need more information about the solution.

@Armando - You didn't test your solution!

@Peter SSs - Thank you for your tip regarding handling the column offset.

Mike

 

[michaelhunter]

Thanks much Mike,

I got it working now.I appreciate your time.

Michael

 

[ukmikeb]

Michael

You're welcome.

Just one last bit. I have managed to change the TRUE part of the formula to INDEX but it results in the formula being a lot larger!

Here is the final formula -

=IF(AND((COLUMNS($GG$26:GG$26)>=2),LARGE($GG$26:$GR$26,COLUMNS($GG$26:GG$26))=LARGE($GG$26:$GR$26,COLUMNS($GG$26:GG$26)-(--(COLUMNS($GG$26:GG$26)>1)))),INDEX(OFFSET($GG$27,0,MATCH(LARGE($GG$26:$GR$26,COLUMNS($GG$26:GG$26)),$GG$26:$GR$26,0)+1,1,COLUMNS($GG$26:$GR$26)-MATCH(LARGE($GG$26:$GR$26,COLUMNS($GG$26:GG$26)),$GG$26:$GR$26,0)),MATCH(LARGE($GG$26:$GR$26,COLUMNS($GG$26:GG$26)),OFFSET($GG$26,0,MATCH(LARGE($GG$26:$GR$26,COLUMNS($GG$26:GG$26)),$GG$26:$GR$26,0)+1,1,COLUMNS($GG$26:$GR$26)-MATCH(LARGE($GG$26:$GR$26,COLUMNS($GG$26:GG$26)),$GG$26:$GR$26,0)),0)),INDEX($GG$27:$GR$27,MATCH(LARGE($GG$26:$GR$26,COLUMNS($GG$26:GG$26)),$GG$26:$GR$26,0)))

Pleased to have helped you. It's been a bit of a learning curve for me too.

Good luck with your project.

Mike

 

[michaelhunter]

Wow!
One of the longest formulas I have seen.
I'll try it out.

Thanks again for your time Mike.

 

[Armando Montes]

You might try sorting descending

 

[Peter_SSs]

To deal with ties, consider using a helper 'Rank' row, which could be hidden if you want after formulas entered in it. The result is that much simpler formulas can be used. It should also cope with any number of ties.

Formulas copied across.

Excel Workbook
	GF	GG	GH	GI	GJ	GK	GL	GM	GN	GO	GP	GQ	GR
26		32	42	14	54	59	44	17	23	31	58	39	42
27		1	2	3	4	5	6	7	8	9	10	11	12
28	Result	5	10	4	6	2	12	11	1	9	8	7	3
29	Rank	8	5	12	3	1	4	11	10	9	2	7	6
Formula

 

[michaelhunter]

Yes, it did help.

Ties can be a nuisance.

Thanks Peter.