Count of Top 50 % of Values

Hey guys,

I'm having an issue that I need some help on. Basically trying to replicate what a Pivot Table Top 10 Percent of Column does in a formula. My goal is to count the number of values that add up to be the Top 50 percent of Sales.

Please see the attachment for an example.

I thought of using the Large function but the list size will vary and is in the thousands.

This is the formula I thought would work but I must be misunderstanding the Percentile function.

Formula:

Please Login or Register  to view this content.

(used as an Array Formula)

Any help will be much appreciated!!

Originally Posted by nflsales

Try below array formula (Shift + Ctrl + Enter)

Please Login or Register  to view this content.

Not going to pretend that I fully understand that formula, but in the process of trying to follow it, I tried a different approach to the logic, which appears to work with a shorter formula.

Formula:

Please Login or Register  to view this content.

Thank you guys for your help thus far but I'm still having issues. Both 'nflsales' & 'jason.b75's formulas work on the sample data set I provided but it is not working on a large data set. Please see attached.

I'm not sure if it is because of the sheer size of the data that is causing problem for the MMULT function?

Originally Posted by MrIceBreaker

I'm not sure if it is because of the sheer size of the data that is causing problem for the MMULT function?

I think that it would be a safe assumption.

Silly question, any reason why you don't just do a simple count of the pivot table records?

If that is not possible for any reason, then you're stuck with a helper column to try and reduce resource drain.

I'm trying to do away with the Pivot Table. I have multiple qualifiers that I need to apply on the data set.

What I'm trying to do in essence is find out what number (count) of the items represent: Top 50% of Sales, 30%, 10%, 5%, Bottom 5% of Sales.

I will then be drilling the data by Department, Sub-Dept, Class, etc.

It becomes extremely tedious to have to update the filters on the Pivot Tables for each Sub-Dept, Class, etc.

Can you elaborate on the 'helper column'? If it's an addition of a column to help assist in solving the problem then I'm open to it but not sure how to go about it.

Originally Posted by MrIceBreaker

It becomes extremely tedious to have to update the filters on the Pivot Tables for each Sub-Dept, Class, etc.

It may be the lesser evil.

I tried what should be a fairly simple helper formula in column C, but given the number of records in the data set, it is taking a considerable time to fill down, and that's just to process top x% of sales, adding extra criteria to drill on department, etc. will only increase the workload!