Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Here's a challenge...
I have a spreadsheet to list individual scores and show running averages for
each competitor. Column B - competitor's name Column C - competitor's running average (This autimatically updates as new scores are posted) Column D and above - competitors' individual scores. Column A contains an 'IF' function which checks the result of Column C and assigns an arbitrary "class" or "rank" depending on the number. For example, if the number in column C is 100, the competitor is assigned the rank of "marksman". If the number is 110, the competitor is assigned the rank of "expert". If it's 120, "master", etc. My problem is that the rules of the league I'm running state that once a competitor achieves a rank, they can NEVER drop down from that rank. They can only go up. Right now, the formula I have in Column A keys on the number in Column C and automatically changes the rank up or down as the person's average changes. How can I modify or create a formula to check the number in Column C and ONLY reasign a new rank if the average indicates it should jump UP, but do nothing if the average went down after new scores were added. Here's an example of what it currently looks like: A B C D E F 1Rank Name Average Score1 Score2 Score3 2Master Jim 120 120 120 120 3Expert Dave 110 110 110 110 Here's what the formula in column A looks like.... =IF(C2120,"Master",IF(C2110,"Expert",IF(C2105," Sharpshooter",IF(C290,"Marksman",IF(C20,"In Training"))))) I thought of perhaps adding some columns way out to the right of the spreadsheet and using them to assign arbitrary numeric values to each rank and then creating a formula to increment the numeric value by one if the current average jumps up enough to merit change, but do nothing if it doesn't, but I can't figure out how to make it work. Any Ideas? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Here's a challenge...
As I stated on your other post, your error occurs because your AVERAGE in
column C is not a running average, but a current average. The idea of columns off to the right can work for you. Say your scores are in columns D:BA, that totals 50 columns. Then, for example, give a little space, start in colmn CA2, and type this formula: =IF(C2="","",AVERAGE($C2:C2)) Then copy to the right the next 50 columns (so your range in this example is CA2:DX2. This will 'capture' the average after each score is entered. Then, in C2, your AVERAGE, your formula would be =MAX($CA2:$DW2) -- John C "thorshammer" wrote: I have a spreadsheet to list individual scores and show running averages for each competitor. Column B - competitor's name Column C - competitor's running average (This autimatically updates as new scores are posted) Column D and above - competitors' individual scores. Column A contains an 'IF' function which checks the result of Column C and assigns an arbitrary "class" or "rank" depending on the number. For example, if the number in column C is 100, the competitor is assigned the rank of "marksman". If the number is 110, the competitor is assigned the rank of "expert". If it's 120, "master", etc. My problem is that the rules of the league I'm running state that once a competitor achieves a rank, they can NEVER drop down from that rank. They can only go up. Right now, the formula I have in Column A keys on the number in Column C and automatically changes the rank up or down as the person's average changes. How can I modify or create a formula to check the number in Column C and ONLY reasign a new rank if the average indicates it should jump UP, but do nothing if the average went down after new scores were added. Here's an example of what it currently looks like: A B C D E F 1Rank Name Average Score1 Score2 Score3 2Master Jim 120 120 120 120 3Expert Dave 110 110 110 110 Here's what the formula in column A looks like.... =IF(C2120,"Master",IF(C2110,"Expert",IF(C2105," Sharpshooter",IF(C290,"Marksman",IF(C20,"In Training"))))) I thought of perhaps adding some columns way out to the right of the spreadsheet and using them to assign arbitrary numeric values to each rank and then creating a formula to increment the numeric value by one if the current average jumps up enough to merit change, but do nothing if it doesn't, but I can't figure out how to make it work. Any Ideas? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Here's a challenge...
Ok, I am now starting to see where my confusion is. The problem is that I
misled you. What I was considering to be a running average is not what you are explaining. the formula that I am using to calculate the average in column C is actually taking the last 3 most recently entered scores and averaging them. Here is the formula I'm using for that column: =AVERAGE(INDEX(1:1,MATCH(10^10,1:1)-2):JC1) Since the rules of the league require that the rank classification must always be based on the average of the competitor's 3 most recent scores, I assumed that was the same as "running" average. I realize now that it is not. Perhaps I can still adapt your method ?? "thorshammer" wrote: I have a spreadsheet to list individual scores and show running averages for each competitor. Column B - competitor's name Column C - competitor's running average (This autimatically updates as new scores are posted) Column D and above - competitors' individual scores. Column A contains an 'IF' function which checks the result of Column C and assigns an arbitrary "class" or "rank" depending on the number. For example, if the number in column C is 100, the competitor is assigned the rank of "marksman". If the number is 110, the competitor is assigned the rank of "expert". If it's 120, "master", etc. My problem is that the rules of the league I'm running state that once a competitor achieves a rank, they can NEVER drop down from that rank. They can only go up. Right now, the formula I have in Column A keys on the number in Column C and automatically changes the rank up or down as the person's average changes. How can I modify or create a formula to check the number in Column C and ONLY reasign a new rank if the average indicates it should jump UP, but do nothing if the average went down after new scores were added. Here's an example of what it currently looks like: A B C D E F 1Rank Name Average Score1 Score2 Score3 2Master Jim 120 120 120 120 3Expert Dave 110 110 110 110 Here's what the formula in column A looks like.... =IF(C2120,"Master",IF(C2110,"Expert",IF(C2105," Sharpshooter",IF(C290,"Marksman",IF(C20,"In Training"))))) I thought of perhaps adding some columns way out to the right of the spreadsheet and using them to assign arbitrary numeric values to each rank and then creating a formula to increment the numeric value by one if the current average jumps up enough to merit change, but do nothing if it doesn't, but I can't figure out how to make it work. Any Ideas? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Here's a challenge...
This formula should get rid of circular references if no data is entered, or
only 1 score is entered. =IF(COUNT(D1:E1)=0,0,IF(COUNT(D1:E1)<2,AVERAGE(D1: E1),AVERAGE(INDEX(1:1,MATCH(10^10,1:1)-2):IV1))) Since your formula goes all the way across the tab, I suggest a second tab, set up as follows, assuming your first tab is called Data Data2 is the name of the second tab: Whichever row starts with competitor names, say row 1: A1: =Data!B1 ... this just pulls the competitor's name across B1: =IF(COUNT(Data!D1:F1)=0,0,AVERAGE(Data!D1:F1)) ... copy this formula across as far as needed. Your main Data tab can be just as is, except when you are looking up the ranking, instead of referencing the current average, you reference the MAX value of the same row on Data2... ....MAX(Data2!1:1)... for your vlookup. Hope this helps. -- John C "thorshammer" wrote: Ok, I am now starting to see where my confusion is. The problem is that I misled you. What I was considering to be a running average is not what you are explaining. the formula that I am using to calculate the average in column C is actually taking the last 3 most recently entered scores and averaging them. Here is the formula I'm using for that column: =AVERAGE(INDEX(1:1,MATCH(10^10,1:1)-2):JC1) Since the rules of the league require that the rank classification must always be based on the average of the competitor's 3 most recent scores, I assumed that was the same as "running" average. I realize now that it is not. Perhaps I can still adapt your method ?? "thorshammer" wrote: I have a spreadsheet to list individual scores and show running averages for each competitor. Column B - competitor's name Column C - competitor's running average (This autimatically updates as new scores are posted) Column D and above - competitors' individual scores. Column A contains an 'IF' function which checks the result of Column C and assigns an arbitrary "class" or "rank" depending on the number. For example, if the number in column C is 100, the competitor is assigned the rank of "marksman". If the number is 110, the competitor is assigned the rank of "expert". If it's 120, "master", etc. My problem is that the rules of the league I'm running state that once a competitor achieves a rank, they can NEVER drop down from that rank. They can only go up. Right now, the formula I have in Column A keys on the number in Column C and automatically changes the rank up or down as the person's average changes. How can I modify or create a formula to check the number in Column C and ONLY reasign a new rank if the average indicates it should jump UP, but do nothing if the average went down after new scores were added. Here's an example of what it currently looks like: A B C D E F 1Rank Name Average Score1 Score2 Score3 2Master Jim 120 120 120 120 3Expert Dave 110 110 110 110 Here's what the formula in column A looks like.... =IF(C2120,"Master",IF(C2110,"Expert",IF(C2105," Sharpshooter",IF(C290,"Marksman",IF(C20,"In Training"))))) I thought of perhaps adding some columns way out to the right of the spreadsheet and using them to assign arbitrary numeric values to each rank and then creating a formula to increment the numeric value by one if the current average jumps up enough to merit change, but do nothing if it doesn't, but I can't figure out how to make it work. Any Ideas? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Here's a challenge...
Ok, I am now starting to see where my confusion is. The problem is that I
misled you. What I was considering to be a running average is not what you are explaining. the formula that I am using to calculate the average in column C is actually taking the last 3 most recently entered scores and averaging them. Here is the formula I'm using for that column: =AVERAGE(INDEX(1:1,MATCH(10^10,1:1)-2):JC1) Since the rules of the league require that the rank classification must always be based on the average of the competitor's 3 most recent scores, I assumed that was the same as "running" average. I realize now that it is not. Perhaps I can still adapt your method ?? "John C" wrote: As I stated on your other post, your error occurs because your AVERAGE in column C is not a running average, but a current average. The idea of columns off to the right can work for you. Say your scores are in columns D:BA, that totals 50 columns. Then, for example, give a little space, start in colmn CA2, and type this formula: =IF(C2="","",AVERAGE($C2:C2)) Then copy to the right the next 50 columns (so your range in this example is CA2:DX2. This will 'capture' the average after each score is entered. Then, in C2, your AVERAGE, your formula would be =MAX($CA2:$DW2) -- John C "thorshammer" wrote: I have a spreadsheet to list individual scores and show running averages for each competitor. Column B - competitor's name Column C - competitor's running average (This autimatically updates as new scores are posted) Column D and above - competitors' individual scores. Column A contains an 'IF' function which checks the result of Column C and assigns an arbitrary "class" or "rank" depending on the number. For example, if the number in column C is 100, the competitor is assigned the rank of "marksman". If the number is 110, the competitor is assigned the rank of "expert". If it's 120, "master", etc. My problem is that the rules of the league I'm running state that once a competitor achieves a rank, they can NEVER drop down from that rank. They can only go up. Right now, the formula I have in Column A keys on the number in Column C and automatically changes the rank up or down as the person's average changes. How can I modify or create a formula to check the number in Column C and ONLY reasign a new rank if the average indicates it should jump UP, but do nothing if the average went down after new scores were added. Here's an example of what it currently looks like: A B C D E F 1Rank Name Average Score1 Score2 Score3 2Master Jim 120 120 120 120 3Expert Dave 110 110 110 110 Here's what the formula in column A looks like.... =IF(C2120,"Master",IF(C2110,"Expert",IF(C2105," Sharpshooter",IF(C290,"Marksman",IF(C20,"In Training"))))) I thought of perhaps adding some columns way out to the right of the spreadsheet and using them to assign arbitrary numeric values to each rank and then creating a formula to increment the numeric value by one if the current average jumps up enough to merit change, but do nothing if it doesn't, but I can't figure out how to make it work. Any Ideas? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Challenge | Excel Worksheet Functions | |||
A Challenge | Excel Discussion (Misc queries) | |||
Challenge for all! Sort this! | Excel Discussion (Misc queries) | |||
A Challenge | Excel Worksheet Functions | |||
Who is up for a challenge? | Excel Discussion (Misc queries) |