Ranking formula

[Ricardo Mass]

Hello all,

I am trying to find a formula to pull out the project numbers for Income, per Practice.

In the attached, columns ABC are the input, and columns F:M are what I would like to end up with. Ideally I would like to use a formula for this, so I can update by simply refreshing the input data.

So I need the top 30 projects ranked by income for SC01, the for SC02, then for SC03.

I have tried using SUMPRODUCT and a helper column, but find this too confusing for the people I would like to give this spreadsheet to.

If anyone has any ideas they would be much appreciated.

Many thanks

Rich

[Fotis1991]

Rich i way could be using this ARRAY formula in G1 and copy down. Then use custom sorting for ranking this.

Please Login or Register  to view this content.

[benishiryo]

hi Rich. try this array formula in G2:

Please Login or Register  to view this content.

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

it's a little long because i had to consider the possibility of 2 or more numbers being the same

[Ricardo Mass]

Hi,

Thank you both, I am currently trying to adapt these to my main file. I will come back with feedback when I have managed it.

Thanks again!

Rich

[Ricardo Mass]

Thanks again, they both work perfectly. I have used benishiryo's formula in this instance, but will keep Fotis' for future use.

Now I just have to try to understand why they work! (/10000??)

Rich

[benishiryo]

glad to help. as i mentioned, i have to consider the possibility of 2 or more numbers being the same. hence, i added a very small decimal of the ROW number divided by 10000. so if C2 is 1241864.91, it will become 1241864.9102

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
=)

[samba_ravi]

=IFERROR(INDEX($A$2:$A$59,MATCH(LARGE(INDEX(($B$2:$B$59=F$1)*($C$2:$C$59)+ROW($A$2:$A$59)*0.000001,0),ROWS($F$2:F2)),INDEX($C$2:$C$59+ROW($A$2:$A$59)*0.000001,0),0),1),"")
try this without array formula

[Ricardo Mass]

Hi nfsales,

Thanks for this, some people are afraid of array formulae so its good to have the option.

Cheers