ranking formula

Microsoft Excel 2003 (full product)
February 8, 2010 at 19:54:15
Specs: Windows XP
i am trying to rank 4 areas. Example:
Div =cell B4_
Reg =cell E4_
Mkt =cell H4_
St =cell K4_

I want to input a number in one of the fields above and then I want to pull back the rank.

Here is the formula i am using. Please help me create a ranking formula for the 4 cells above.

Hope this makes sense.


Edit: Post edited by Office moderator.
Formula split into multiple lines for ease of viewing.

See More: ranking formula

Report •

February 9, 2010 at 05:07:26

I am not sure what you are trying to rank.

You say that you want to rank 4 areas, and then you say you want to put a value in One of the four cells.

If all four cells are zero, your formula returns zero.
If K4 is greater than zero, and the other three are zero it returns the result of the conditional sum for E4:E4500 on the raw data sheet, where cells in A4:A4500 = K4 on sheet Report.

The formula will only return one value, based on the first of the four cells B4, E4, H4, K4 that is greater than zero.

From this am I right in thinking that cells B4, E4, H4, K4 are on the Report worksheet and that your formula is also on the Report worksheet.

This brings me back to the question what are you trying to rank?

The formula only returns a single value which is the sum of all cells in the range E4:E4500, based on a comparison using columns A, B, C or D against the value in the first of the four cells B4, E4, H4, K4 which is greater than zero.

If all four of the cells B4, E4, H4, K4 contained a value and you had the results of four conditional sums, did you want to rank the result of the four conditional sums?

For example
if SUMIF(RawData!$A$4:$A$4500,Report!$B$4,RawData!$E$4:$E$4500), was 100,
and SUMIF(RawData!$B$4:$B$4500,Report!$E$4,RawData!$E$4:$E$4500), was 20,
and SUMIF(RawData!$C$4:$C$4500,Report!$H$4,RawData!$E$4:$E$4500), was 300,
and SUMIF(RawData!$D$4:$D$4500,Report!$K$4,RawData!$E$4:$E$4500), was 40

The rank positions of B4, E4, H4, K4 are 2, 4, 1, 3.

Can you provide additional information.


Report •

February 9, 2010 at 05:56:56
You are correct in your thinking. I will only enter a number in 1 of these feilds at a time which will bring back a value based on the formula below. I am trying to bring back a rank at the same time when you enter cell B4. Example:
Div =cell B4_ Div = _rankB7_
Reg =cell E4_ Etc......
Mkt =cell H4_
St =cell K4_

How does that ranking formula need to be.


Report •

February 9, 2010 at 06:38:43

Sorry, still don't get it.

What are you trying to rank.

Your formula has nothing to do with ranking. It just returns a single value which is the sum (total) of all cells in E4:E4500 in rows where there is a match in column A, B, C or D, depending on the first of the four cells B4, E4, H4 or K4 that is greater than zero.

Can you give an example of the data and which cells are to be ranked.

Also can you explain what Div, Reg, Mkt, St are. Are they named variables or named ranges or just text headings.


Report •

Related Solutions

February 9, 2010 at 07:11:40
Div. is just a text heading. I am ranking raw data numbers. The formula brings back the number i need. Here is an example:

i have a box labeled Div. (division), when i put the letter (A) in the cell, it sums the raw data from another sheet to bring back the value for all of (A). I would like it to rank the letter (A). The reason i need to rank the letter a is because i have 23 different divisions with different letters.

Report •

February 9, 2010 at 07:12:22
re: I am trying to bring back a rank at the same time when you enter cell B4

As Humar has said, it is not clear what you are trying to rank.

What does this mean?

Div =cell B4_ Div = _rankB7_

Do the underscores ( _ ) have any significance?

Report •

February 9, 2010 at 07:49:42

If you put A in the cell next to Div. you have a formula that sums all the values that have A in a certain column.

Entering A returns a single value
Entering B returns a different value - the sum of all the values that have B in a certain column

As your approach only returns single values, you can't get a rank.

What I suggest you do is have the ranking created on the raw data sheet.
If columns X, Y and Z are available, put all division names in column X (X2 to X23) in column Y,(Y2 to Y23) in Y2 put this formula:


In column Z (Z2:Z23) in cell Z2 enter
Just drag the formulas down to extend them to row 23.

You now have a table of subtotals for each division and their ranks.
On the summary sheet use VLOOKUP() in two cells to return the subtotal for the division and its rank

Something like this :

	B		C
3	Divison name	A
4	Subtotal	100
5	Rank		21

In cell C4 enter this formula: =VLOOKUP(C$3,RawData!$X$2:$Z$23,2,FALSE)
in cell C5 enter this formula: =VLOOKUP(C$3,RawData!$X$2:$Z$23,3,FALSE)

In C3 the VLOOKUP() looks for the value in cell C3 (A in this example), in column X on the raw data sheet and returns the result on the same row in the second column, which is column Y.
The next VLOOKUP() returns the value from the third column, which is column Z.


Report •

February 9, 2010 at 08:42:28
I got it. THanks

1 more question. This is my formula.


#n/a shows up in the cell. How do i have that not showing?

Report •

February 9, 2010 at 09:10:11

The #NA is generated by the VLOOKUP() function when it does not find a matching value.

You can wrap the VLOOKUP() function in a test:
I have split the formula onto two lines for ease of viewing.

If the VLOOKUP() returns the NA error, 'IF(ISNA(' tests for it and returns ""
If it does not return NA, the original formula is used.


Report •

February 9, 2010 at 09:26:34
Actually, i got that now too.

THanks for your assistance.

Report •

February 9, 2010 at 09:28:53
You're welcome



Report •

Ask Question