Challenge experts will LOVE!!, can you get the formula??
 

Ok MS Excel experts the challenge is set!,

Say you have a worksheet where you have to post the results
for 5 ongoing rounds of tennis,
You have 5 columns representing each round and with these cells
number has been input a for each round (2 for a win, 0 for a draw,
for a loss)
At the end of the five rounds an ongoing weighted score is displayed i
column G using a formula, this formula is based on rounds 1-3@10% an
4-5@35% therefore the formula (for Gemma) is:

=1B*10%+1C*10%+1D*10%+1E*35%+1F*35%

(obviously given the formula only has relative references it has bee
dragged down for the other 14 players and displays their ongoin
weighted score aswell)

THE CHALLENGE is to post what the formula would be for the weighting o
each individual round using absolute/mixed cell references in Ongoin
Weighted Score Field (column G represented by the number with th
decimal), looking at the weighting for each round.
The weightings would be posted at the bottom of each round

GOOD LUCK!

A B C D E
F G

1 Gemma 0 2 2 1 1 1.1
2 Mark 0 1 2 1 1 1.0
3 Giles 0 2 2 2 0 1.1
4 Matt 0 0 2 0 0 0.2
5 Owen 0 0 0 0 2 0.7
6 Kevin 2 2 0 2 2 1.8
7 Alex 0 0 0 0 0 0.0
8 Vala 2 0 0 1 2 1.3
9 Katie 2 0 0 1 2 1.3
10 lea 2 0 0 0 0 0.2
11 Tara 0 0 0 0 0 0.0
12 Tahli 2 1 2 0 1 0.9
13 Tyler 1 2 0 2 0 1.0
14 Kylie 1 2 2 2 1 1.6
15 Mark 0 0 0 0 0 0.0

Round weighting ? ? ? ? ?
?

GOOD LUCK!!

--
Drumm
-----------------------------------------------------------------------
Drummy's Profile: http://www.excelforum.com/member.php...fo&userid=3478
View this thread: http://www.excelforum.com/showthread.php?threadid=54538

Challenge experts will LOVE!!, can you get the formula??
 

Do you mean

=SUM(B1:B15)*CHOOSE(COLUMN(A1),0.1,0.1,0.1,0.35,0. 35)

--
HTH

Bob Phillips

(replace xxxx in email address with googlemail if mailing direct)

"Drummy" wrote in
message ...

Ok MS Excel experts the challenge is set!,

Say you have a worksheet where you have to post the results
for 5 ongoing rounds of tennis,
You have 5 columns representing each round and with these cells a
number has been input a for each round (2 for a win, 0 for a draw, 1
for a loss)
At the end of the five rounds an ongoing weighted score is displayed in
column G using a formula, this formula is based on rounds 1-3@10% and
4-5@35% therefore the formula (for Gemma) is:

=1B*10%+1C*10%+1D*10%+1E*35%+1F*35%

(obviously given the formula only has relative references it has been
dragged down for the other 14 players and displays their ongoing
weighted score aswell)

THE CHALLENGE is to post what the formula would be for the weighting of
each individual round using absolute/mixed cell references in Ongoing
Weighted Score Field (column G represented by the number with the
decimal), looking at the weighting for each round.
The weightings would be posted at the bottom of each round

GOOD LUCK!

A B C D E
F G

1 Gemma 0 2 2 1 1 1.1
2 Mark 0 1 2 1 1 1.0
3 Giles 0 2 2 2 0 1.1
4 Matt 0 0 2 0 0 0.2
5 Owen 0 0 0 0 2 0.7
6 Kevin 2 2 0 2 2 1.8
7 Alex 0 0 0 0 0 0.0
8 Vala 2 0 0 1 2 1.3
9 Katie 2 0 0 1 2 1.3
10 lea 2 0 0 0 0 0.2
11 Tara 0 0 0 0 0 0.0
12 Tahli 2 1 2 0 1 0.9
13 Tyler 1 2 0 2 0 1.0
14 Kylie 1 2 2 2 1 1.6
15 Mark 0 0 0 0 0 0.0

Round weighting ? ? ? ? ?
?

GOOD LUCK!!!


--
Drummy
------------------------------------------------------------------------
Drummy's Profile:
http://www.excelforum.com/member.php...o&userid=34780
View this thread: http://www.excelforum.com/showthread...hreadid=545382