If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
60% formula
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. |
#2
|
|||
|
|||
60% formula
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. |
#3
|
|||
|
|||
60% formula
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. |
Thread Tools | |
Display Modes | |
|
|