CODEWITHSUNDEEP
excelsumvlookuppercentageconditional-formatting
user4100980
user4100980

Reputation: 147

How to return corresponding string values of largest numerical values in column up to certain percentage in Excel

I need to identify names of employees who have generated the top 80% of revenue. Column A is employee name, Columns B-E are revenue totals for each employee by Quarter. How can I identify these employees either by highlighting or VLOOKUP?

I started by trying to sum the largest values in the revenue a column up to a certain percentage or number.? I was trying to somehow use SUM() and SUMIF() with LARGE(), but I haven't been able to figure it out. Ideally, I would like to be able to do this without having to sort the column in descending order since this formula will be applied to multiple columns.

So ultimately I need a formula that returns the employee names, not just the sum.

Upvotes: 0

Views: 64

Answers (1)

Scott Craner
Scott Craner

Reputation: 152505

To get a list of the top values use:

=IF(AND(SUM($D$1:$D1)+ AGGREGATE(14,6,$B$2:$B$16,ROW(1:1))<SUM($B$2:$B$16)*$G$1,OR(D1<>"",ROW(1:1)=1)),AGGREGATE(14,6,$B$2:$B$16,ROW(1:1)),"")

Then to get the name assciated with that value:

=IFERROR(INDEX($A$2:$A$16,AGGREGATE(15,6,ROW($1:$16)/($B$2:$B$16=D2),COUNTIF($D$2:D2,D2))),"")

enter image description here

Upvotes: 0

Related Questions