Conditional Formatting + Counting Cells

[jbird10]

I have been working on this but I can't seem to get it working properly so I figured I would ask for help.

I have an excel spreadsheet in which I want to distinguish the first, second and third highest ranked cells in a column. At the moment I am doing conditional formattinig using =LARGE and putting a different color for 1st, 2nd, and 3rd.

The next step I want to do is to put a column at the end of the data that adds the number of times each row had data that was 1st, 2nd, or 3rd. I will upload a picture so hopefully that makes this explaining a little easier.

Thanks for any help.

http://imageshack.us/f/220/exampledl.jpg/

 

[Andrew Poulsom]

Welcome to MrExcel.

In D2:

=SUM($A2=LARGE($A$2:$A$26,COLUMN(A$1)-COLUMN($A$1)+1),$B2=LARGE($B$2:$B$26,COLUMN(A$1)-COLUMN($A$1)+1),$C2=LARGE($C$2:$C$26,COLUMN(A$1)-COLUMN($A$1)+1))

copied down and across.

 

[konew1]

or you could use (A4=LARGE(A$4:A$17,1))+(B4=LARGE(B$4:B$17,1))+(C4=LARGE(C$4:C$17,1)) and change range and ranking as required before you copy down

 

[jbird10]

Thank you for the quick responses.

Andrew what does the end part of the formula mean?

And if I have a whole bunch of columns I want to sum just rinse and repeat?

Thanks again.

 

[Andrew Poulsom]

What do you mean by "the end part of the formula"? If you mean:

COLUMN(A$1)-COLUMN($A$1)+1

it just sets the second argument for LARGE in such a way that the formula can be copied across. Copied across it becomes:

COLUMN(B$1)-COLUMN($A$1)+1 = 2
COLUMN(C$1)-COLUMN($A$1)+1 = 3

 

[jbird10]

So far so good, it worked in one worksheet, but when I tried to copy it into another worksheet I get "#NUM".

 

[Andrew Poulsom]

What data are you using?

 

[jbird10]

I am using a database of percentages and ratios.

 

[Andrew Poulsom]

No, I mean I need to see your data to establish why the formula is returning #NUM!.

 

[jbird10]

It's for a work project, so I don't think I can upload it here.

Perhaps the problem comes in the cells without the $? I don't understand what that is referring to right after the sum.