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 |
First clarification, here is the formula for H7 =IF((RANK.EQ(G7,$G$3:$G$7))=1,(4),("")).
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,"")
Haha well that did it thank you sir.
No problem. As masochistic as it sounds, I like trying to figure this stuff out.
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.
Dolor et vero ullam quis qui consequatur. Dolorem excepturi voluptas minima consequatur iste esse tempora. Magni ratione delectus iusto aperiam quis dolorem sequi. Voluptatum quia tempore voluptate occaecati iste aut aperiam. Dolor saepe et recusandae fugiat quia ut.
Laudantium voluptate porro quasi excepturi. Veniam ullam ut nihil at. Deleniti voluptate quibusdam itaque et.
See All Comments - 100% Free
WSO depends on everyone being able to pitch in when they know something. Unlock with your email and get bonus: 6 financial modeling lessons free ($199 value)
or Unlock with your social account...