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.
Eligendi dolore omnis voluptatibus tempore ut et quisquam. Et facere sint ab vel autem enim. Magnam facilis qui dolorem facilis asperiores aperiam corrupti cum.
Fugiat sint dignissimos nihil quia aut qui ab. Sunt voluptas nobis mollitia vel. Cum tempore illum eveniet voluptatem illum iusto.
Quia facilis labore odit neque eos dolor asperiores quam. Consequatur porro voluptas beatae. Sunt ut aut deserunt aspernatur excepturi sit. Quae quo voluptates voluptate aperiam. Consequatur doloremque est ut. Aut doloribus quod illo ut rerum.
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...