Rank Vendors Based on Total Spend

bletou

Hello Community,

I'm looking for formula that will rank the 'Offsetting Acct Names" by 'Supplier Total Spend'. As you can see below, I captured the total spend per supplier but need to rank these and report on the Top 10. Any assistance would be greatly appreciated!

Ipshita

Hi @bletou , the easiest formula is

=RANKEQ([Supplier Total Spend]@row, [Supplier Total Spend]:[Supplier Total Spend])

Let me know if this works!

Thanks,

Ipshita

bletou

Hi Ipshita, that doesn't quite work, because though it returns the rank, its jumping from 1 to 16 because there are 15 entries. So how this should be reading out is Miltneyi as 1, and Cash in bank - 18 as rank 2 etc

Ipshita

Hi @bletou - are all the values in this column correct? Are there any other values higher than "Cash in bank" ? As per this formula, it would always calculate and rank the values in column "Supplier Total Spend" for the current row against all values in the same column. If you could share the whole file, I can check for the error.

Thanks,

Ipshita

bletou

Hi @Ipshita All the values are correct. Because this is an invoice report, there are multiple entries for each vendor and the 'Supplier Total Spend' is the total of all invoices for that vendor. I included the Amount in CC Crcy column to hopefully make it more clear. In the miltenyi example below, the $688k is the total of all invoices from Miltenyi and there are about 60 vendors in total. Essentially my goal is to create a report or chart conveying only the Top 10

Ipshita

Hi @bletou I don't know why Cash in Bank is coming to Rank 16, but the only other option I can suggest top of my head, is that you create another column called "Ranking" and assign the ranks manually (since there are only 60 vendors) it won't be that difficult and then use this column to pull the top 10 ranks.

Hope this helps.

Thanks,

Ipshita