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]

Hi

An alternative way -

=INDEX($AA$26:$AL$27,2,MATCH(LARGE($AA$26:$AL$26,COLUMN()-26),$AA$26:$AL$26,0))

and copy across

Apologies for you having to change the column references and don't forget to adjust "Column()-26" to reflect the offset to your GG26:GR26.

hth

Mike

 

[michaelhunter]

Thanks for your reply Mike.

The closest I can come to what you suggest is:

=INDEX(GG26:GR27,2,MATCH(LARGE(GG26:GR26,COLUMN()-26),GG26:GR26,0))

I don't understand at all the concept of 'offset' or what the COLUMN()-26 means or how it should be changed.

Would you mind rewriting the aboveas an example for the value in GH28?

I would appreciate it.

 

[ukmikeb]

Hi

The idea of the "offset" is the relative column from Column 1.

Hence, Column "AA" in my example is the 27th column in the worksheet, so subtracting 26 from the column number you arrive at column 1 in the range "AA26:AL26".

Therefore, adding 26 for each column range of "AA:AZ", "BA:BZ", "CA:CZ", "DA:DZ", "EA:EZ", "FA:FZ" and 6 for the column range "GA:GF" you should arrive at the number you require to replace 26 in the formula.

Btw, I was assuming that the numbers in your range were unique, otherwise my formula will not work.

hth

Mike

ps I have tried your formula adjusted to my "AA:AL" range and it produces nothing, probably because I have missed something joining the formula from 3 separate lines.

If you could repost the formula wrapping it in "code tags" by selecting the formula and pressing the "#" sign I'll have a further look at it.

 

[michaelhunter]

Thanks Mike.

I appreciate your help.

 

[Armando Montes]

Try this one:
=INDEX(GG$27:GR$27,MATCH(LARGE(GG$26:GR$26,COLUMN()-188),GG$26:GR$26,0))
Copy across GG:GR

 

[michaelhunter]

Thanks Armando, it works fine for the first four cells, after that I get a number error in each.

 

[Armando Montes]

My mistake, try like this
=INDEX($GG27:$GR27,MATCH(LARGE($GG26:$GR26,COLUMN()-188),$GG26:$GR26,0))

 

[Peter_SSs]

My mistake, try like this
=INDEX($GG27:$GR27,MATCH(LARGE($GG26:$GR26,COLUMN()-188),$GG26:$GR26,0))

This will produce incorrect results if any columns are subsequently added or deleted before column GG. A more robust version of this formula would be

=INDEX($GG27:$GR27,MATCH(LARGE($GG26:$GR26,COLUMNS($GG28:GG28)),$GG26:$GR26,0))

 

[michaelhunter]

Peter, Armando,
Thanks for your suggestions.
I got it working correctly now!