I have a bowling league with 16 teams; each team has its own
statistical sheet named 1 thru 16. Each week I manually compare the
team averages of the teams that will play each other, take 60% of the
difference and give the difference to the team with the lower average.
I am seeking a formula that will do this automatically. In cell b1 of
each sheet is the name of the team. In cell q3 of each sheet is the
team average. On a sheet named "Schd" starting with cell d61 I have a
list of the teams that are to be playing each other for the current
week, for example; #1 Wonders - #2 Magic, #3 Deuces - #4 High Five - #5
Dynamite, etc for the sixteen match ups.
I would like a formula that would compare the two averages (from q3 of
each sheet) of the teams playing each other (from list on the "schd"
sheet), then take 60% of the difference (rounded down) and post the
figure on the "schd" sheet starting with cell j61which would be along
side the name of the team (without the # sign and number of the team)
that has the lower average and receives the handicap.
This is what the end result will look like;
Wonders 12
Deuces 10
Dynamite 2, etc
If the team averages are the same for two teams (which no handicap is
given) or the handicap is less that one, then nothing would be shown
for either team on the handicap sheet. Thank you in advance.

Just use something like

=INDIRECT("'"&D61&"'!Q3)
and
=INDIRECT("'"&D62&"'!Q3)

to get two teams average. The 60% difference would be

ABS(Ave1-Ave2)*60%

give it to whichever is higher. I think it would be simplest to work this
all out on the Schd sheet and just manually input the result on the correct
sheet(s)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"wally" wrote in message
ps.com...
I have a bowling league with 16 teams; each team has its own
statistical sheet named 1 thru 16. Each week I manually compare the
team averages of the teams that will play each other, take 60% of the
difference and give the difference to the team with the lower average.
I am seeking a formula that will do this automatically. In cell b1 of
each sheet is the name of the team. In cell q3 of each sheet is the
team average. On a sheet named "Schd" starting with cell d61 I have a
list of the teams that are to be playing each other for the current
week, for example; #1 Wonders - #2 Magic, #3 Deuces - #4 High Five - #5
Dynamite, etc for the sixteen match ups.
I would like a formula that would compare the two averages (from q3 of
each sheet) of the teams playing each other (from list on the "schd"
sheet), then take 60% of the difference (rounded down) and post the
figure on the "schd" sheet starting with cell j61which would be along
side the name of the team (without the # sign and number of the team)
that has the lower average and receives the handicap.
This is what the end result will look like;
Wonders 12
Deuces 10
Dynamite 2, etc
If the team averages are the same for two teams (which no handicap is
given) or the handicap is less that one, then nothing would be shown
for either team on the handicap sheet. Thank you in advance.

Thanks Bob but I am not sure exactly what I am supposed to do. I need
more help and clarification I'm not that familar with the formula. What
are the marks (''''') that preceed the "&" sign?. What is "ave"?, is
that supposed to be "avg"?.
Thanks, Wally
Bob Phillips wrote:
Just use something like

=INDIRECT("'"&D61&"'!Q3)
and
=INDIRECT("'"&D62&"'!Q3)

to get two teams average. The 60% difference would be

ABS(Ave1-Ave2)*60%

give it to whichever is higher. I think it would be simplest to work this
all out on the Schd sheet and just manually input the result on the correct
sheet(s)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"wally" wrote in message
ps.com...
I have a bowling league with 16 teams; each team has its own
statistical sheet named 1 thru 16. Each week I manually compare the
team averages of the teams that will play each other, take 60% of the
difference and give the difference to the team with the lower average.
I am seeking a formula that will do this automatically. In cell b1 of
each sheet is the name of the team. In cell q3 of each sheet is the
team average. On a sheet named "Schd" starting with cell d61 I have a
list of the teams that are to be playing each other for the current
week, for example; #1 Wonders - #2 Magic, #3 Deuces - #4 High Five - #5
Dynamite, etc for the sixteen match ups.
I would like a formula that would compare the two averages (from q3 of
each sheet) of the teams playing each other (from list on the "schd"
sheet), then take 60% of the difference (rounded down) and post the
figure on the "schd" sheet starting with cell j61which would be along
side the name of the team (without the # sign and number of the team)
that has the lower average and receives the handicap.
This is what the end result will look like;
Wonders 12
Deuces 10
Dynamite 2, etc
If the team averages are the same for two teams (which no handicap is
given) or the handicap is less that one, then nothing would be shown
for either team on the handicap sheet. Thank you in advance.