Summing a ranking of several categories
 

I have looked at the rank function, but I do not think that it is the
function for what I need.

I have a list of salesmen, four in total, that sell three different types of
merchandise.

How would I rank number one through four, based on how they sold each of the
six different types of merchandise.

Example:

Salesman 1,6,18,4
Salesman 2,11,0,3
Salesman 3,2,5,20
Salesman 4,1,3,2

To complicate matters each set of merchandise categories is sorted
individually by amount sold.

Merchandise #1
Salesman 2 11
Salesman 1 6
Salesman 3 2
Salesman 4 1

Merchandise #2
Salesman 1 18
Salesman 3 5
Salesman 4 3
Salesman 2 0

Merchandise #3
Salesman 3 20
Salesman 1 4
Salesman 2 3
Salesman 4 2

Total Sales and Ranking
Salesman 1 28
Salesmen 3 27
Salesmen 2 14
Salesmen 4 6

So what I am looking for is a formula that will put the combined total by
the right salesman, and when I sort by using Data-Sort, each salesman keeps
its correct total.

Steve

Summing a ranking of several categories
 

We need a fuller explanation of how the data is stored and what you want to
see
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"caldog" wrote in message
...
I have looked at the rank function, but I do not think that it is the
function for what I need.

I have a list of salesmen, four in total, that sell three different types
of
merchandise.

How would I rank number one through four, based on how they sold each of
the
six different types of merchandise.

Example:

Salesman 1,6,18,4
Salesman 2,11,0,3
Salesman 3,2,5,20
Salesman 4,1,3,2

To complicate matters each set of merchandise categories is sorted
individually by amount sold.

Merchandise #1
Salesman 2 11
Salesman 1 6
Salesman 3 2
Salesman 4 1

Merchandise #2
Salesman 1 18
Salesman 3 5
Salesman 4 3
Salesman 2 0

Merchandise #3
Salesman 3 20
Salesman 1 4
Salesman 2 3
Salesman 4 2

Total Sales and Ranking
Salesman 1 28
Salesmen 3 27
Salesmen 2 14
Salesmen 4 6

So what I am looking for is a formula that will put the combined total by
the right salesman, and when I sort by using Data-Sort, each salesman
keeps
its correct total.

Steve

Summing a ranking of several categories
 

Well I as re-read my post I see that I myself was not clear. So let me try
again.

The different types of merchandise categories are in columns across the top
of the worksheet. Columns B through D. The different salesmen are list in
column A. Rows A2 through A5.

So at the end of each month I tally up how many each salesman sells of each
piece of merchandise, see example A. Then on another page, for my boss I
would like to rank them in order of total sales. That was what I was trying
to say in my first post. As each of the individual categories is sorted to
show from high to low, the salesman selling the most is in the number one
position, and the salesman selling the least is in the number four position.
This will be done for each of the different categories, see example B. Then
I want to have an overall ranking section where I rank each salesman by
overall total sales, see example C. This last step is where I am having my
problems. I can not seem to come up with a formula that will work. See in
example C, where beside salesman #2, the overall ranking number is 2.00; it
is that number that is causing me problems. Right now I am doing everything
by hand, and I want to speed up the process with a formula. So what I want
my formula to do is find salesman2, and total up his ranking in each of the
different categories shown in example B. Then display that number, i.e.
2.00, beside his name, along with the total sales amount for each salesman.

Example A:
Salesman1 6,18,4
Salesman2 11,0,3
Salesman3 2,5,20
Salesman4 1,3,2

Example B:
Merchandise #1
Salesman2 11
Salesman1 6
Salesman3 2
Salesman4 1

Merchandise #2
Salesman1 18
Salesman3 5
Salesman4 3
Salesman2 0

Merchandise #3
Salesman3 20
Salesman1 4
Salesman2 3
Salesman4 2

Example C:
Salesman1 (2+ 1+2)/3=1.67 28
Salesmen3 (3+2+1)/3=2.00 27
Salesmen2 (1+3+2)/3=2.00 14
Salesmen4 (4+2+4)/3=3.33 6

Summing a ranking of several categories
 

Please confirm you have:
in A2: Salesman 1
in B2: 6
in C2: 18
in D2: 4
rather than in A2: Salesman1,6, 18,4

If the later please experiment by selcting A2:A5 and using the command Data
| Text to Column
to separate all the items. Then we can get down to business.

best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"caldog" wrote in message
...

Well I as re-read my post I see that I myself was not clear. So let me
try
again.

The different types of merchandise categories are in columns across the
top
of the worksheet. Columns B through D. The different salesmen are list
in
column A. Rows A2 through A5.

So at the end of each month I tally up how many each salesman sells of
each
piece of merchandise, see example A. Then on another page, for my boss I
would like to rank them in order of total sales. That was what I was
trying
to say in my first post. As each of the individual categories is sorted
to
show from high to low, the salesman selling the most is in the number one
position, and the salesman selling the least is in the number four
position.
This will be done for each of the different categories, see example B.
Then
I want to have an overall ranking section where I rank each salesman by
overall total sales, see example C. This last step is where I am having
my
problems. I can not seem to come up with a formula that will work. See
in
example C, where beside salesman #2, the overall ranking number is 2.00;
it
is that number that is causing me problems. Right now I am doing
everything
by hand, and I want to speed up the process with a formula. So what I
want
my formula to do is find salesman2, and total up his ranking in each of
the
different categories shown in example B. Then display that number, i.e.
2.00, beside his name, along with the total sales amount for each
salesman.

Example A:
Salesman1 6,18,4
Salesman2 11,0,3
Salesman3 2,5,20
Salesman4 1,3,2

Example B:
Merchandise #1
Salesman2 11
Salesman1 6
Salesman3 2
Salesman4 1

Merchandise #2
Salesman1 18
Salesman3 5
Salesman4 3
Salesman2 0

Merchandise #3
Salesman3 20
Salesman1 4
Salesman2 3
Salesman4 2

Example C:
Salesman1 (2+ 1+2)/3=1.67 28
Salesmen3 (3+2+1)/3=2.00 27
Salesmen2 (1+3+2)/3=2.00 14
Salesmen4 (4+2+4)/3=3.33 6

Summing a ranking of several categories
 

One way which seems to get you there ..

Illustrated in this sample:
http://www.flypicture.com/download/ODAzMQ==
Multi product rank n average.xls

Source data as posted within A2:D5,
col A = salesman#
cols B to D = sales for merchandise1,2,3

In F2: =IF(B2=0,"",RANK(B2,B$2:B$5))
Copy F2 to H2

In I2: =AVERAGE(F2:H2)
In J2: =SUM(B2:D2)
Select F2:J2, copy down to J5

Cols F to H returns the ranking of sales by merchandise#
Col I returns the average ranking
Col J is the total sales
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"caldog" wrote:

Well I as re-read my post I see that I myself was not clear. So let me try
again.

The different types of merchandise categories are in columns across the top
of the worksheet. Columns B through D. The different salesmen are list in
column A. Rows A2 through A5.

So at the end of each month I tally up how many each salesman sells of each
piece of merchandise, see example A. Then on another page, for my boss I
would like to rank them in order of total sales. That was what I was trying
to say in my first post. As each of the individual categories is sorted to
show from high to low, the salesman selling the most is in the number one
position, and the salesman selling the least is in the number four position.
This will be done for each of the different categories, see example B. Then
I want to have an overall ranking section where I rank each salesman by
overall total sales, see example C. This last step is where I am having my
problems. I can not seem to come up with a formula that will work. See in
example C, where beside salesman #2, the overall ranking number is 2.00; it
is that number that is causing me problems. Right now I am doing everything
by hand, and I want to speed up the process with a formula. So what I want
my formula to do is find salesman2, and total up his ranking in each of the
different categories shown in example B. Then display that number, i.e.
2.00, beside his name, along with the total sales amount for each salesman.

Example A:
Salesman1 6,18,4
Salesman2 11,0,3
Salesman3 2,5,20
Salesman4 1,3,2

Example B:
Merchandise #1
Salesman2 11
Salesman1 6
Salesman3 2
Salesman4 1

Merchandise #2
Salesman1 18
Salesman3 5
Salesman4 3
Salesman2 0

Merchandise #3
Salesman3 20
Salesman1 4
Salesman2 3
Salesman4 2

Example C:
Salesman1 (2+ 1+2)/3=1.67 28
Salesmen3 (3+2+1)/3=2.00 27
Salesmen2 (1+3+2)/3=2.00 14
Salesmen4 (4+2+4)/3=3.33 6