Using LARGE function in incontiguous cells
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 |
Using LARGE function in incontiguous cells
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 |
Using LARGE function in incontiguous cells
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 |
Using LARGE function in incontiguous cells
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 |
Using LARGE function in incontiguous cells
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 |
All times are GMT +1. The time now is 10:36 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com