×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Offset & Large Function Combined

Offset & Large Function Combined

Offset & Large Function Combined

(OP)
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.

RE: Offset & Large Function Combined

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/

RE: Offset & Large Function Combined

(OP)
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,

RE: Offset & Large Function Combined

Quote:

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/

RE: Offset & Large Function Combined

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!

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources