I am trying to insert TWO functions or formulas- One in Column P that computes a running average of DthruO (Jan- Dec) that ignores blanks if someone misses a month. And then Q is really tricky- It needs to be the average of the TOP 5 scores only if there are a minimum of six scores or it should indicate not enough matches. The X count seems to be a problem as well.
Each I try comes back with asst errors, when I solve that error I cause another.
Hello Juli ,
The Average function requires numbers, names, arrays, or references that contain numbers. Your entries, like 1428-45X, appear to be text. Text will cause the Average function to error. What do want the average of?
Sincerely,
Leith Ross
Remember To Do the Following....
1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.2. Thank those who have helped you by clicking the Starbelow the post.
3. Please mark your post [SOLVED] if it has been answered satisfactorily.
Old Scottish Proverb...
Luathaid gu deanamh maille! (Rushing causes delays!)
Hi Leith,
The 1428 is a numeric score with the 45 being the X or (bullseye) hits which act as a tie breaker if the score is a tie. thus 1428-45X. Do I need to write it a different way? or leave the X count off and somehow put it back in after the calculation? I tried that on my own but then it doesn't understand the - between the numbers.
Juli
Hello Juli,
I think you need to explain to me what this is being used for so I can follow you.
Try this array formula for the average:
{=AVERAGE(IF(LEN(D2:O2)>0,SUBSTITUTE(D2:O2,MID(D2:O2,FIND("-",D2:O2),LEN(D2:O2)-FIND("-",D2:O2)+1),"")+0))}
Running average
{=IF(COUNTA(D2:O2)>=6,AVERAGE(LARGE(LEFT(D2:O2,4)+0,{1,2,3,4,5})),"")}
Last edited by Palmetto; 04-13-2012 at 09:11 PM.
Palmetto
Do you know . . . ?
You can leave feedback and add to the reputation of all who contributed a helpful response to your solution by clicking the star icon located at the left in one of their post in this thread.
Palmetto,
Running Avg Top 5 Avg
#VALUE! #VALUE!
Tried it it gives me value errors in both- I am reattaching it with your formula in it in case I just entered it improperly.
Leith,
It is a tally sheet for a monthly pistol match. They shoot once a month and the scores are recorded. The running average column is because they like to keep track of how they are doing throughout the year. At the end of the year the awards are given out based on the average of their top five scores. To qualify for any of the end of year "awards" they have to have participated in a minimun of six matches during the year.
Juli
Hello Juli,
Thanks for clearing that up for me. How would you figure the tie breaker into the score?
The formulas are array formula and must be entered using the key combination of Ctrl + Shift + Enter
It would be as the average as well. If you think it would help I can private send you the real workbook, I just didn't want to post it. The X count works like this:
scores: 1476-24X, 1423-12X, 1392-30X, 1416-16X, 1425-17X would equal 1426-20X which would beat a 1426-19X. If that makes sense.
Hello Juli,
Okay, that makes sense. The format and the meaning behind the numbers was a bit confusing. Thanks.
Try using this for the running average (I admit to being a bit lazy on this one, but it works)
Assuming your numbers will *ALWAYS* be in the format of 1234-56X
{=TEXT(AVERAGE(IF(ISNUMBER(LEFT(D2:O2,4)+0),LEFT(D2:O2,4)+0)),"0")&"-"&TEXT(AVERAGE(IF(ISNUMBER(MID(D2:O2,6,2)+0),MID(D2:O2,6,2)+0)),"0")&"X"}
Again, the above is an array formula, which means you must use the key combination of Ctrl + Shift + Enter to commit the formula.
Excel will automatically add the curly brackets - do not type them.
The formula for the top-five will be problematic. While we can certainly average the top five (leading 4-digits only using the formula I already gave you), if we include the tie breaker values, it gets more difficult because we need to average the tie breaker values that match the same five top X values used in the initial average.
This would be far easier if you simply used two columns to hold the data; (1) for main score (2) for tie breaker.
Palmetto,
Your formula worked great. Until.... I see what you mean about making it easier if I divide them out. I am attaching the revised version with them seperated. Sheet "Averages" populated from Sheet "Match Scores". The Data in "Match Scores" is populated via a user form. If it would be easier should the form also populate the averages sheet? If so I don't know how to make it find the right name to select the line to populate. On the match score sheet the - and the X are used so that the match report prints those in the word document. If I can make that work a different way I can make them numbers only.(that seems like it should be easier using a template..) The only place that the - and "X" would need to be in the Averages is in the report as well- the shooters only see the report, noit the spreadsheet. So by dividing them into two columns how can I modify the array?
Juli
Running average:
{=AVERAGE(IF((MOD(COLUMN(D2:Z2),2)=0)*(D2:Z2<>0),D2:Z2))}
Average Top-5
{=AVERAGE(LARGE(IF((MOD(COLUMN(E2:AA2),2)<>0)*(E2:AA2<>0),E2:AA2),{1,2,3,4,5}))}
Note: leave off the "X" for the tie breakers on the Averages sheet. By appending the number with "X", Excel views the entry as text and not a number, which means we would have to use a more complex formula to extract the numeric characters and convert them to true numbers before we could calculate an average.
Both formulas are array entered as before.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks