Quick (hopefully) Excel question.

WSO lend me your excel genius! (Ace Ventura anyone?)

Ok so I am trying to create a formula that will rank data in an array and then spit out a value dependent on how well a company did relative to its peers. For example say you have ten companies and want to rank them based on yield. The highest yielding co. would be ranked #1, but I would want the formula to spit out a score, for simplicity make the score similar to GPA, so this #1 gets a 4, #2 a 3 etc. Issue is that I need a formula that I can adapt to evaluate the same list of companies on various metrics.

Here is what I have made that doesn't work, returns #VALUE! Silly mistake? Wrong way to go about it? Shitty explanation?

=IF((RANK.EQ(G3,$G$3:$G$7))=1,(4),("")),IF((RANK.EQ(G3,$G$3:$G$7))=2,(3),("")),IF((RANK.EQ(G3,$G$3:$G$7))=3,(2),("")),IF((RANK.EQ(G3,$G$3:$G$7))=4,(1),(""))

So if CAM's rank in the range G3:G7 is #1 return 4, if not ranked #1 return blank, ranked #2 return 3 etc etc. Not the best example b/c CAM yields nothing, I will screen those instances out in the initial dump going forward.

See image below of rough spread. I would then adapt this formula to rank and then score each company on its performance for each metric then sum the score at the end (just keep it simple for now and say equally weighted). Then make it update real time.

Thanks in advance guys, ill be around to offer any clarification needed.

Attachment Size
Capture.PNG 13.83 KB 13.83 KB
 

Idk how to simplify it with a singular formula (seems like nested and could work), but the reason I saw for returning #Value was that to separate if statements, use &. e.g

=IF((RANK(D167,D166:D169))=4,0,"")&IF((RANK(D167,D166:D169))=3,1,"")

People demand freedom of speech as a compensation for freedom of thought which they seldom use.
 

You can put a table on the side/new sheet where you have each rank and the desired point output, then do a index/match (or vlookup) on that table with the rank.eq inside of it. That way you can easily update your scoring system without having to change all those nested formulas.

This to all my hatin' folks seeing me getting guac right now..
 

Aspernatur mollitia repellendus veritatis laboriosam quo voluptas. Dicta in quae sed dolor. Dolor minima qui occaecati suscipit possimus culpa dolores. Illum in voluptatibus earum et.

Ducimus laborum magnam temporibus dignissimos sed. Magnam est illum dolorem ad praesentium iusto.

Career Advancement Opportunities

March 2024 Hedge Fund

  • Point72 98.9%
  • D.E. Shaw 97.9%
  • Magnetar Capital 96.8%
  • Citadel Investment Group 95.8%
  • AQR Capital Management 94.7%

Overall Employee Satisfaction

March 2024 Hedge Fund

  • Magnetar Capital 98.9%
  • D.E. Shaw 97.8%
  • Blackstone Group 96.8%
  • Two Sigma Investments 95.7%
  • Citadel Investment Group 94.6%

Professional Growth Opportunities

March 2024 Hedge Fund

  • AQR Capital Management 99.0%
  • Point72 97.9%
  • D.E. Shaw 96.9%
  • Citadel Investment Group 95.8%
  • Magnetar Capital 94.8%

Total Avg Compensation

March 2024 Hedge Fund

  • Portfolio Manager (9) $1,648
  • Vice President (23) $474
  • Director/MD (12) $423
  • NA (6) $322
  • 3rd+ Year Associate (24) $287
  • Manager (4) $282
  • Engineer/Quant (71) $274
  • 2nd Year Associate (30) $251
  • 1st Year Associate (73) $190
  • Analysts (225) $179
  • Intern/Summer Associate (22) $131
  • Junior Trader (5) $102
  • Intern/Summer Analyst (249) $85
notes
16 IB Interviews Notes

“... there’s no excuse to not take advantage of the resources out there available to you. Best value for your $ are the...”

Leaderboard

1
redever's picture
redever
99.2
2
Secyh62's picture
Secyh62
99.0
3
Betsy Massar's picture
Betsy Massar
99.0
4
BankonBanking's picture
BankonBanking
99.0
5
kanon's picture
kanon
98.9
6
CompBanker's picture
CompBanker
98.9
7
dosk17's picture
dosk17
98.9
8
DrApeman's picture
DrApeman
98.9
9
GameTheory's picture
GameTheory
98.9
10
bolo up's picture
bolo up
98.8
success
From 10 rejections to 1 dream investment banking internship

“... I believe it was the single biggest reason why I ended up with an offer...”