Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 51
  • Last Modified:

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
0
racepro
Asked:
racepro
  • 5
  • 5
2 Solutions
 
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
 
Ejgil HedegaardCommented:
0
 
Martin LissRetired ProgrammerCommented:
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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 LissRetired ProgrammerCommented:
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 LissRetired ProgrammerCommented:
Take a look at the attached.
Named-range-Tutorial.xlsx
0
 
raceproAuthor Commented:
Thanks Martin for the tutorial. Very helpful
Ian
0
 
Martin LissRetired ProgrammerCommented:
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 LissRetired ProgrammerCommented:
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

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 5
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now