Finding largest combination of cell values

[omni72]

Hi all - I'm looking for an elegant way to calculate and highlight the largest combination of 5 cells. For example:

Column A
1
2
3
1
2
3
4
1
2
3
1
2
3

I'd want the formula to show me that the five bolded numbers produce the greatest sum of any of the five numbers in the column. I'd also like to be able to highlight the cells.

Does that make sense and is it doable within Excel 2007?

Thanks, in advance, for your help!

[MarvinP]

Hi omni72,

How about a helper column that adds the numbers to the left down 5 rows. Then a Conditional Format on the helper to show the largest "Sum of 5"?

[omni72]

Hi Marvin -

Thanks for the quick reply and excellent suggestion. However, and my apologies for omitting this earlier, the combination doesn't necessarily need to be sequential. In other words, it could be A1, A4, A5, A13, & A16 or A2, A4, A9, A12, & A14. Does that make sense? If not let me know and I will try to elaborate.

I've also attached an Excel 2007 spreadsheet as a sample. It's probably a bad example because in this case it is the first 5 cells :D

[Pete_UK]

Don't you just want the 5 largest numbers?

Pete

[martindwilson]

going on from petes thought
to get the sum use
=SUM(LARGE(E2:E21,{1,2,3,4,5}))
then select e2
open name manager and create a name
call it say mylarge
refers to
=ISNUMBER(MATCH(Sheet1!E2,LARGE(Sheet1!$E$2:$E$22,{1,2,3,4,5}),0))
now in e2 select conditional format and use a formula option
put = mylarge chose a format
copy that format to rest of range

[omni72]

going on from petes thought
to get the sum use
=SUM(LARGE(E2:E21,{1,2,3,4,5}))
then select e2
open name manager and create a name
call it say mylarge
refers to
=ISNUMBER(MATCH(Sheet1!E2,LARGE(Sheet1!$E$2:$E$22,{1,2,3,4,5}),0))
now in e2 select conditional format and use a formula option
put = mylarge chose a format
copy that format to rest of range

Excellent idea, Martin! Thanks so much for the effort and elegance.

And thanks to all who chimed in - the EF community is unquestionably the best!