Offset & Large Function Combined

[iken]

Hi All,

I am trying to get a value from a list, based on it's ranking. I need to get the cell value from the cell directly above the value gained from the Large function.

I have tried using OFFSET(LARGE($B$5:$AO$5,1),-1,0,1,1), but get an error (it appears the Large function cannot be used as a reference to offset from).

I need to use the large, so I can get the 2nd, 3rd, 4th largest value (and this I think it the only way to tackle multiple values - say 3 lots equal 2nd).

Anyone have any ideas on how to achieve this?


Many Thanks.

 

[IDS]

This seems to work:
=INDEX($B$6:$B$19,MATCH(LARGE($B$6:$B$19,1),$B$6:$B$19,0)-1)

Large returns the value, Match returns the row number, and Index returns the value at that row number minus 1.

Replace Index with Offset if you prefer.

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[iken]

Thanks IDS,

Your method almost works, it just doesn't quite give the right figures for multiple values of the same freqency as below:

Formulea Results Expected Results
Number Freq Rating
1 2 1st 9 10 10
2 4 2nd 8 9 9
3 6 3rd 7 4 4
4 7 4th 7 4 7
5 2 5th 7 4 8
6 4 6th 6 3 3
7 7 7th 4 2 2
8 7 8th 4 2 6
9 8 9th 2 1 1
10 9 10th 2 1 5


I have atatched the above in a spreadsheet as well.

Any idea on how to rectify?

Cheers,

 

[IDS]

Any idea on how to rectify?

Not right now. I'll have another look later if no-one else has any bright ideas.

Some VBA would probably be the easiest way.

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[Terrifyingtesttube]

Used one of the methods from

http://www.tek-tips.com/viewthread.cfm?qid=1439840

It's not too pretty, and it will require some modifications to accommodate additional data. The blue columns are my helper columns, and the green column shows the result.

Based on a quick google search, it seems like there might be some better (and/or prettier) ways of doing this, using everything from VBA as IDS suggests to array formulae.

Good luck!

 

[iken]

Hey,

I found another forum, and had the attached as a reply

Works a treat