# 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
###### Who is Participating?

Commented:
0

retiredAuthor Commented:
There are over 80,000 rows with an average of 8 in each event.
Thanks
Ian
0

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

retiredAuthor 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

Older than dirtCommented:
I'd need more tuition on that one.
I'm happy to help. Do you understand Named Ranges?
0

retiredAuthor Commented:
not really...... I know of them but never used them
0

Older than dirtCommented:
Take a look at the attached.
Named-range-Tutorial.xlsx
0

retiredAuthor Commented:
Thanks Martin for the tutorial. Very helpful
Ian
0

Older 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

retiredAuthor 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

Older 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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.