sort and select based on criteria

[Iriemon]

I have a spreadsheet with the following data, what I would like to do is:

1. sort this in descending order by number of sales (got the macro doing this)
2. Determine the total sales ( a simple SUM in an adjacent cell. (done)
3. Calculate what 90% of total sales are. ( done in another adjacent cell)
4. Determine which parts make up the 90% of sales. (HELP)
5. Graph only the 90% parts. Graph is already created but how do I capture
the 90% dataset. (HELP)

The idea is to take the entire dataset, sort by number of sales and then
graph only those parts that make up 90% of the sales.

Part number sales
part1 8
part2 5
part3 84
part4 10
part5 4
part6 59
part7 105
part8 9
part9 3
part10 8
part11 1
part12 134
part13 148
part14 3
part15 33
part16 8

With the above data, it should "grab"

part13 148
part12 134
part7 105
part3 84
part6 59
part15 33


Any ideas are greatly appreciated.

Irie

 

[Matt Geare]

Hi,

Don't get your hopes up - I've been out of the game for a while so don't
have the full answer but the good news is this is eminently "doable". I am
only posting this partial reply because I have been looking at this on and
off for the last 4 or 5 hours and no one else has answered.

The simple mechanical way is to do exactly as you say and sort the data in
descending order. Then add a third column to the right with a running total
in it (in C1 put a formula that says =B1, and then in C2 put a formula that
says C1+B2 and copy this down). Then add a fourth column that compares the
running total to your 90% cutoff figure and returns a Y/N flag - something
like =If(c1<f1,"Y","N") where f1 is the cell that has the figure for the 90%
cutoff in it.

And the clever part you still need is the bit where you graph data ranges
are dependent on this Y/N flag and therefore is what I would call "floating"
rather than "fixed". There is a technique for this which I have used but
cannot for the life of me remember where I found it - it was on one of the
usual suspects websites.

If you've still drawn a blank tomorrow I will do my utmost to come up with a
full solution.

I suspect there is a far more elegant solution available so fingers crossed!

Cheers,

Matt