# Flag if data is in top 80% of range

• I need a formula that will flag a cell if it is in the top 80% of the range total value. Column A = Salesman Column B = Material Column C = Revenue Column D = Top 80% flag?. Need it to see if the material is in the top 80% for each Salesman's total sales.

• Re: Flag if data is in top 80% of range

Hi goin4boge, you could try something like:

=IF(C1>=MAXIFS(C:C,A:A,A1,B:B,B1)*0.8,"Top 80% of "&B1&" for "&A1,"")

• Re: Flag if data is in top 80% of range

Excel is not liking the maxifs function, is that a valid function?

• Re: Flag if data is in top 80% of range

looks like maxif(s) was added for 2016. I have 2013. any ideas for excel 2013?

• Re: Flag if data is in top 80% of range

Quote

looks like maxif(s) was added for 2016

My bad - I always assume eveyone else uses the same version you know what they say... to assume makes an "ass" of "u" and "me"!

I'll keep playing...

• Re: Flag if data is in top 80% of range

I tried to change it to =if(c1>=max(if(A:A=A1,C:C)*.8,"Top 80%","") but that did not work. This formula checks to see if the value of the single data point is >= 80% of the max value. What I need is to identify all the parts that make up the top 80%. Does that make sense?

• Re: Flag if data is in top 80% of range

Ah okay, yes that makes sense. It's bed time for me here in the UK though I'm afraid so will have to take a look tomorrow if no-one has answered by then. Best of luck

• Re: Flag if data is in top 80% of range

easy after several hours of thinking on it 8-)

created a column with % of total
Created a column that sumif the % is greater than that lines data
created a column that said if this value was < 80% then "In Top 80%", else, "NOT in Top 80%"

• Re: Flag if data is in top 80% of range

aha, of course - helper columns. I mean, obviously I just wanted you to get there by yourself.

Just testing....

...honest