60% formula

[wally]

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.

[Bob Phillips]

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" <[email protected]> wrote in message
news:[email protected]...
> 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.
>

[wally]

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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.
> >