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

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

"Iriemon" wrote:

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