Masters Golf Pool
 

I am doing a Pool in which the contestant picks 10 golfers. We "throw" out
the five highest scores, resulting in the lowest five scores counting. I
have an example below using very small numbers for simplicity.

Row G10 H10:Y10
1 5 3 8 5 10 7 2 9 4 6 Result in Z10 should be
15 (1,2,3,4,5)

I'm trying to learn Excel (I'm 75 years old), but this formula is beyond my
knowledge.

Although not necessary, is it possible to highlight in color the lowest five
scores through Conditional Formatting?

Bob M.

Masters Golf Pool
 

=SUM(SMALL(G10:Y10,{1,2,3,4,5}))

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"robert morris" wrote in message
...
I am doing a Pool in which the contestant picks 10 golfers. We "throw" out
the five highest scores, resulting in the lowest five scores counting. I
have an example below using very small numbers for simplicity.

Row G10 H10:Y10
1 5 3 8 5 10 7 2 9 4 6 Result in Z10 should be
15 (1,2,3,4,5)

I'm trying to learn Excel (I'm 75 years old), but this formula is beyond
my
knowledge.

Although not necessary, is it possible to highlight in color the lowest
five
scores through Conditional Formatting?

Bob M.

Masters Golf Pool
 

Works perfectly! I was trying to make it more complicated than necessary.
Thanks so much! Any thoughts on highlighting the five highest or lowest?

Bob M.

"Bob Phillips" wrote:

=SUM(SMALL(G10:Y10,{1,2,3,4,5}))

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"robert morris" wrote in message
...
I am doing a Pool in which the contestant picks 10 golfers. We "throw" out
the five highest scores, resulting in the lowest five scores counting. I
have an example below using very small numbers for simplicity.

Row G10 H10:Y10
1 5 3 8 5 10 7 2 9 4 6 Result in Z10 should be
15 (1,2,3,4,5)

I'm trying to learn Excel (I'm 75 years old), but this formula is beyond
my
knowledge.

Although not necessary, is it possible to highlight in color the lowest
five
scores through Conditional Formatting?

Bob M.

Masters Golf Pool
 

Yeah.

First add a defined name, Ctrl-F3, and name it one2five with a RefersTo
value of ={1,2,3,4,5}

Then select G10:Y10
Goto FormatConditional Formatting
Change the condition from Cell Value Is to Formula Is
Add a formula of =ISNUMBER(MATCH(G10,SMALL($G$10:$Y$10,one2five),0) )
Click Format
Select the Pattern tab
Choose a colour
exit

All done.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"robert morris" wrote in message
...
Works perfectly! I was trying to make it more complicated than necessary.
Thanks so much! Any thoughts on highlighting the five highest or lowest?

Bob M.

"Bob Phillips" wrote:

=SUM(SMALL(G10:Y10,{1,2,3,4,5}))

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"robert morris" wrote in message
...
I am doing a Pool in which the contestant picks 10 golfers. We "throw"
out
the five highest scores, resulting in the lowest five scores counting.
I
have an example below using very small numbers for simplicity.

Row G10 H10:Y10
1 5 3 8 5 10 7 2 9 4 6 Result in Z10 should
be
15 (1,2,3,4,5)

I'm trying to learn Excel (I'm 75 years old), but this formula is
beyond
my
knowledge.

Although not necessary, is it possible to highlight in color the lowest
five
scores through Conditional Formatting?

Bob M.

Masters Golf Pool
 

"robert morris" wrote:
.. Any thoughts on highlighting the five highest or lowest?

To format 5 lowest:
Select the desired range for conditional formatting, say select G10:Y15
(with G10 active), then apply CF using the formula:
=ISNUMBER(MATCH(G10,SMALL($G10:$Y10,COLUMN($A:$E)) ,0))
Format to taste, Ok out

To format the 5 highest, use:
=ISNUMBER(MATCH(G10,LARGE($G10:$Y10,COLUMN($A:$E)) ,0))
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Masters Golf Pool
 

Max

Once again, the people on this site amaze me!

Thanks, Thanks,

Bob M.


"Max" wrote:

"robert morris" wrote:
.. Any thoughts on highlighting the five highest or lowest?

To format 5 lowest:
Select the desired range for conditional formatting, say select G10:Y15
(with G10 active), then apply CF using the formula:
=ISNUMBER(MATCH(G10,SMALL($G10:$Y10,COLUMN($A:$E)) ,0))
Format to taste, Ok out

To format the 5 highest, use:
=ISNUMBER(MATCH(G10,LARGE($G10:$Y10,COLUMN($A:$E)) ,0))
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Masters Golf Pool
 

Welcome. It's basically similar to Bob P's response.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"robert morris" wrote in message
...
Max

Once again, the people on this site amaze me!

Thanks, Thanks,

Bob M.