second highest value difference

Hi,
I would like a formula to determine second highest value within a range and display difference in a cell adjacent
to highest value.
Please see attachment for fuller explanation
Thanks
Ian
rating-diff.xlsx
raceproAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Ejgil HedegaardConnect With a Mentor Commented:
0
 
raceproAuthor Commented:
There are over 80,000 rows with an average of 8 in each event.
Can you please refer to spreadsheet and place formula in column headed '???'
Thanks
Ian
0
 
Martin LissConnect With a Mentor Older than dirtCommented:
Ejil's formula is very nice and it works, but one problem I see is that if the number of rows increases or decreases the formulas would need to be changed. To avoid that problem you can create a named range from column "A" named something like IDs, and as the formula for the named range use =OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A:$A)-1,1) which will make it dynamic. Then move the explanitory text to the right somewhere to get it out of the way and create a similar range for column "B" called something like Ratings and use =OFFSET(Sheet1!$B$2,0,0,COUNTA(Sheet1!$B:$B)-1,1) as the formula. Then in C2 use the formula =IF(B2=MAX(INDEX((IDs=A2)*Ratings,,)),MAX(INDEX((IDs=A2)*Ratings,,))-LARGE(INDEX((IDs=A2)*Ratings,,),2),0) and copy down.

I've attached a  modified workbook.
28984193.xlsx
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
raceproAuthor Commented:
Ejgil's Your formula works fine. I found Martin's a bit complicated being a novice, I'd need more tuition on that one.
Thanks guys
Ian
0
 
Martin LissOlder than dirtCommented:
I'd need more tuition on that one.
I'm happy to help. Do you understand Named Ranges?
0
 
raceproAuthor Commented:
not really...... I know of them but never used them
0
 
Martin LissOlder than dirtCommented:
Take a look at the attached.
Named-range-Tutorial.xlsx
0
 
raceproAuthor Commented:
Thanks Martin for the tutorial. Very helpful
Ian
0
 
Martin LissOlder than dirtCommented:
You're welcome and I'm glad I was able to help.

If you expand the “Full Biography” section of my profile you'll find links to some articles I've written that may interest you.

Marty - Microsoft MVP 2009 to 2016
              Experts Exchange MVE 2015
              Experts Exchange Top Expert Visual Basic Classic 2012 to 2015
0
 
raceproAuthor Commented:
Thanks again Martin, I'll have a read. I have a file I'd like to send you.
If you can acknowledge email when you can.
Thanks
Ian
0
 
Martin LissOlder than dirtCommented:
I removed the email address from your post since you shouldn't publicly display things like that. If you want to send me a file, you can click on the image of me in this post and "message" me.
0
All Courses

From novice to tech pro — start learning today.