Hi.
I'm wondering a way to highlight the highest score in incontiguous cells.
For instance, in the example below, each person has a score in three
categories and I would like to highlight the top score in each category.

I'd looked at LARGE function but it seems to only take continuous cells as
an array.

John 1 67
2 74
3 159

Richard 1 76
2 85
3 190

David 1 56
2 62
3 129

Sue 1 69
2 77
3 163


Any way to get around this? I'd also like to highlight 2nd and 3rd scores
in each category but that's probably easy once I know how to do the highest,
I suppose?

Thank you.
--
Maki @ Canberra.AU

Hi

You can use a named range for each category and the use the name as
reference in your formula.

Select alle catetory1 cells and goto InsertNames Define Enter: Cat1 as
name.

=Large(Cat1,1)

Hopes this helps.
....
Per

"Maki" skrev i meddelelsen
...
Hi.
I'm wondering a way to highlight the highest score in incontiguous cells.
For instance, in the example below, each person has a score in three
categories and I would like to highlight the top score in each category.

I'd looked at LARGE function but it seems to only take continuous cells as
an array.

John 1 67
2 74
3 159

Richard 1 76
2 85
3 190

David 1 56
2 62
3 129

Sue 1 69
2 77
3 163


Any way to get around this? I'd also like to highlight 2nd and 3rd scores
in each category but that's probably easy once I know how to do the
highest,
I suppose?

Thank you.
--
Maki @ Canberra.AU

On Sun, 12 Jul 2009 23:29:03 -0700, Maki
wrote:

Hi.
I'm wondering a way to highlight the highest score in incontiguous cells.
For instance, in the example below, each person has a score in three
categories and I would like to highlight the top score in each category.

I'd looked at LARGE function but it seems to only take continuous cells as
an array.

John 1 67
2 74
3 159

Richard 1 76
2 85
3 190

David 1 56
2 62
3 129

Sue 1 69
2 77
3 163


Any way to get around this? I'd also like to highlight 2nd and 3rd scores
in each category but that's probably easy once I know how to do the highest,
I suppose?

Thank you.


Assuming the categories are in column B and the scores are in column
C, and that scores are always positive numbers you may try the
following formula:

=LARGE((C$1:C$20)*(B$1:B$20=m),n)

Note: This is an array formula that has to be confirmed by
CTRL+SHIFT+ENTER rather than just ENTER,

Change the 20 to fit the size of your data.
This will give the n'th largest score for category m.

Hope this helps / Lars-Åke

Apply Conditional formatting on column C (containing 67,74,etc.) with this
formula:
=AND(NOT(ISBLANK(C1)),C1=SUMPRODUCT(MAX(--($B$1:$B$15=B1)*($C$1:$C$15))))

Adjust ranges as necessary!
Regards,
Stefi

€žMaki€� ezt Ã*rta:

Hi.
I'm wondering a way to highlight the highest score in incontiguous cells.
For instance, in the example below, each person has a score in three
categories and I would like to highlight the top score in each category.

I'd looked at LARGE function but it seems to only take continuous cells as
an array.

John 1 67
2 74
3 159

Richard 1 76
2 85
3 190

David 1 56
2 62
3 129

Sue 1 69
2 77
3 163


Any way to get around this? I'd also like to highlight 2nd and 3rd scores
in each category but that's probably easy once I know how to do the highest,
I suppose?

Thank you.
--
Maki @ Canberra.AU

Wow, thank you, all!

I see there are quite a few ways to do it.

I've tried Lars-Ã…ke's option and it worked brilliantly.

Will have a better look at Per's and Stefi's but thanks heaps all the same!

--
Maki @ Canberra.AU


"Lars-Ã…ke Aspelin" wrote:

On Sun, 12 Jul 2009 23:29:03 -0700, Maki
wrote:

Hi.
I'm wondering a way to highlight the highest score in incontiguous cells.
For instance, in the example below, each person has a score in three
categories and I would like to highlight the top score in each category.

I'd looked at LARGE function but it seems to only take continuous cells as
an array.

John 1 67
2 74
3 159

Richard 1 76
2 85
3 190

David 1 56
2 62
3 129

Sue 1 69
2 77
3 163


Any way to get around this? I'd also like to highlight 2nd and 3rd scores
in each category but that's probably easy once I know how to do the highest,
I suppose?

Thank you.


Assuming the categories are in column B and the scores are in column
C, and that scores are always positive numbers you may try the
following formula:

=LARGE((C$1:C$20)*(B$1:B$20=m),n)

Note: This is an array formula that has to be confirmed by
CTRL+SHIFT+ENTER rather than just ENTER,

Change the 20 to fit the size of your data.
This will give the n'th largest score for category m.

Hope this helps / Lars-Ã…ke