LinkBack URL
About LinkBacks
help.PNG
So In box d2 I would like excel to find palmer's last 20 appearance's and average his yards in these appearances. If he is not found 20 times I would excel to average whatever is available. Thank you so much for any help
help.PNG
So In box d2 I would like excel to find palmer's last 20 appearance's and average his yards in these appearances. If he is not found 20 times I would excel to average whatever is available. Thank you so much for any help
Last edited by richhhh; 08-27-2016 at 05:32 PM.
What do you mean by last 20? Do you mean the last 20 entries in the list?
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
Yes last 20 entries in the list. Sorry for any confusion. Thank you for checking in
Are you able to attach a sample workbook?
I would have to start another thread for that lol. I originally tried that and uploaded file but When I scroll over attachment section I had no option to post. So I left to take a picture and upload that instead.
No, you wouldn't. You can add an attachment to any post: use the Go Advanced button and scroll down until you see Manage Attachments in blue - click it.
File is attached thank you
Try this...
D2 = Palmer
This array formula** entered in E2:
=AVERAGE(IF(ROW(B2:B169)>=LARGE(IF(A2:A169=D2,ROW(A2:A169)),MIN(20,COUNTIF(A:A,D2))),IF(A2:A169=D2,B2:B169)))
** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
Thank you for your help. Altho it did produce some numbers it did not calculate correctly. Most cells had errors and the ones that received a calculation were all the same for each person which means it did not average the last 20 appearances. I will spend some time on it to see if I can figure something out. Thank you again
Works in your posted sample file...
Maybe this will help someone help me. Here I managed to get averages and will sort each name and there averages all the way down. I want instead the averages for the last 20 instead of the average of everytime the name appears. Also if the name does not appear 20 times I want the average of everytime the name appeared.
help.PNG
Last edited by richhhh; 08-27-2016 at 07:47 PM.
Many members are unable to see images in the *.png format due to forum compatibility issues with some browsers.
If you need to post an image post it in the *.jpg format.
Even better than posting images... post a SMALL sample file. That way we can test solutions directly in the file with the relevant data.
Ok tony I think I see whats going on. Its likey how I am explaining. I see in your sample that it did in fact calculate kolb.
If A2 is Palmers name then I would like E2 to be palmers average of last 20 appearance's. If he has not appeared 20 times then average of everyone he has.
If A2 is Kolbs name then I would like E2 to be kolbs average of last 20 appearance's. If he has not appeared 20 times then average of everyone he has.
I would like this to roll all the way down getting the name from A? and place currents averages in same section . A15/e15 etcc..
I will upload another excel.
Cant thank you enough for your time and apologizes if my explaining is not sufficient.
I added another work book to my most recent post.
Hi richhhh. I am pretty confident that we understand what you need very well. The problem is that you did not apply formula properly and that's why your results did not come out right.
I will offer you another array formula so may be it work better for you.
See image below and verify that numbers came out right.
In my example I entered formula in cell D2 and copied down. I also have unique list of names in Column C
Formula:
Please Login or Register to view this content.
***Array formula
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER.
You will know the array is active when you see curly braces { } appear around your formula.
If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
v A B C D 1 Player yards Unique Name List Average last 20 or less 2 Palmer 235 Palmer 271.35 3 Palmer 349 Lindley 177.25 4 Palmer 213 Stanton 218.38 5 Palmer 269 Hoyer 225.00 6 Palmer 274 Skelton 235.13 7 Palmer 310 Kolb 240.23 8 Palmer 362 Bartel 242.00 9 Palmer 271 Anderson 217.70 10 Palmer 317 Hall 160.00 11 Palmer 363 Warner 277.30 12 Palmer 374 Leinart 220.38 13 Palmer 275 Rattay 180.00 14 Palmer 421 15 Palmer 171 16 Palmer 352 17 Palmer 311
If you like my answer please click on * Add Reputation
Don't forget to mark threads as "Solved" if your problem has been resolved
"Nothing is so firmly believed as what we least know."
--Michel de Montaigne
Based on the data in the help3 file.Originally Posted by richhhh
Data Range
This array formula** entered in e2 and copied down:
=AVERAGE(IF(ROW(B$2:B$169)>=LARGE(IF(A$2:A$169=D2,ROW(A$2:A$169)),MIN(20,COUNTIF(A:A,D2))),IF(A$2:A$169=D2,B$2:B$169)))
** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.
You are definitely skilled in excel! But again I dont think I have clearly pointed out what I am looking for.
I am not looking for a list of all the players and there averages. I want the players averages of there last 20 beside where there name is. For example if Palmer names goes down 10 straight I want the average of his last 20 beside it everytime( the average will change everytime) When the player the name changes it will search that players last 20 and calculate the average of his last 20 and put it beside it. (if he does not appear 20 times in the entire list it will average everything that is there)
My very simply formula went down the list and gave a average every time each player no matter what order but it just does not give me the last 20 or the condition of if the name does not appear often.
Much appreciated all the work you have done.
Richhhh - if you look at AlKey's last file, you will see a working formula. The formulae in column D are array formulae and have been entered using CTRL+SHIFT+ENTER (not just ENTER). If you enter them using just ENTER, they will not work correctly.
Here's the output I think you want using AlKey's latest formula, which I have made one tweak to so that it is checking the name from column A instead of the extra column C that he had added:
Excel 2016 (Windows) 32 bit
Player yards Average last 20. If less then 20 average all avaible Palmer Palmer Palmer Palmer Palmer Palmer Palmer Palmer Palmer Palmer Palmer Palmer
Sheet: Sheet1
Excel 2016 (Windows) 32 bit
Sheet: Sheet1
Formula confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
try this (See column G in attached) [array formula control + Shift + enter]
=AVERAGE(IF(IF(A2=A2:A$169,B2:B$169)*IF(COUNTIF(INDIRECT("A"&ROW(A2)&":A"&ROW(2:$169)),A2)<=20,1)=0,FALSE,IF(A2=A2:A$169,B2:B$169)*IF(COUNTIF(INDIRECT("A"&ROW(A2)&":A"&ROW(2:$169)),A2)<=20,1)))
Happy with my advice? Click on the * reputation button below
Ah, I think I see now - it's an incremental average.
@Crooza - I see what you've done, but should Palmer's first average on the list not just be 235?
@Richhh - this would be so much easier if you would give us some expected outcomes!!!
I interpreted it as the average of the most recent 20 scores. I saw this as the 20 scores below the present score.
So you are assuming that the latest score is at the top of the list? I was assuming that the latest score would be at the bottom.
Richhhh really needs to give us some expected outcomes, as there have been several different interpretations of his requirements so far!
Yeah I assumed working from the top being the most recent scores. This could be a totally wrong assumption. If so I'll need to rework the formula but the logic should hold true for the structure of the formula (I think)
Are you able to sort the data so that the names are grouped together?
If so, could you post a new file and show us what results you expect?
This appears to be exactly what I am looking for! Thank you so much! Looks like I can easily change the average to median if I choose to as well!
I want to thank everyone who replied.Tony I apologize if I caused any confusion. Your time was greatly appreciated.
I am going to spend some time when I get home verifying formula and the results. I also need to modify it to do other things other then yards which have different numbers etc.... With some things I also want to change how many games it goes back etc..
You guys are awesome !
You're welcome. In future you should ALWAYS include some expected outcomes in your sample data AND explain how you've arrived at them.
Good deal. Thanks for the feedback!
=AVERAGE(IF(IF(A2=A2:A$169,B2:B$169)*IF(COUNTIF(INDIRECT("A"&ROW(A2)&":A"&ROW(2:$169)),A2)<=20,1)=0,FALSE,IF(A2=A2:A$169,B2:B$169)*IF(COUNTIF(INDIRECT("A"&ROW(A2)&":A"&ROW(2:$169)),A2)<=20,1)))
Ok This formula is working!! So amazing!
I would like to ask for just a little more help.(The nerve of me
So the above is working but I would like to make some adjustments.. If A2 is Palmer it does search A2:A169 for the last 20 times Palmer appeared and average the numbers in the corresponding B section. So if Palmer appeared in A2,A4,A6,A7,A8,A9,A10,A11(untill it has 20) ETCC.. It would average B2,B4,B6,B7,B8,10,B11 numbers etc. But the number in B2 is not part of his last 20 as it is his current. I would like it to still search for 20 but not use B2.
Also When the scenario arises where a player does not appear 20 times it is in fact averaging the available cells from the current cell down all the way to row169. This is great but in this scenario I actually want it to search A2:A169 and average everytime his name appeared. So if there is not a past 20 it will use past/current/future to obtain its average.
Sample file added. Column J contains results that I would like. Thank you again guys.
Last edited by richhhh; 08-28-2016 at 04:47 PM.
Different approach. (using your sample file from post #11).
In C2, filled down
=COUNTIF(A$2:A2,A2)
In D2, filled down
=AVERAGEIFS($B3:$B$169,$A3:$A$169,A2,$C3:$C$169,"<="&(C2+20))
Does that give the expected results?
It is giving the expected results! Except when the player does not appear 20 times. As I want it to average all that players numbers from the start of the list a2:a169 when there is less then 20 appearance in the past (down the list)
Last edited by richhhh; 08-28-2016 at 04:49 PM.
So using Lindley as an example, the formula in D21 should include the figures from B20 and B21 as part of the average?
=IF(COUNTIF($A$2:$A$169,A2)>20,AVERAGEIFS($B3:$B$169,$A3:$A$169,A2,$C3:$C$169,"<="&(C2+20)),AVERAGEIF($A$2:$A$169,A2,$B$2:$B$169))
Yep absolutely. Cant thank you enough. This appears to working . I am going to put it the test.
I believe I need to change (COUNTIF($A$2:$A$169,A2) to (COUNTIF(A2:A169,A2) as what will happen is a player like palmer who appears more then 20 but at some point will appear less then 20 in the past will not get the condition in those situations that is in place for players who do not appear 20 times. Where in the situation where he does not have 20 past results I want all of his games averaged (B2:b169) I think that makes sense. The theory being less relevant data is much better then small sampled data.
Palmer appears 40 times in the list, if you use that method then it will average all 40 entries once it gets past the point of less than 20 remaining in the list.
Is that what you want or do you just want to average a max of 20 appearances from the bottom up?
Which version of excel are you using?
If you have an office 365 subscription then some of the extra features that come with it could prove useful, but not essential.
Yes the max 20 appearances from the bottom up would be the best!
Yes I have a office 365 subscription
That's Great and happy to assist
Ok This formula is working!! So amazing!
I would like to ask for just a little more help.(The nerve of me
I've adjusted formula to do the 20 excluding the current line BUT I looked at the bottom of the table where you run out of data (hence less than 20 entries remaining) and the average seems to be working fine in regard to only averaging the available number of entries. Can you give me line number where this isn't working the way you expect it to and let me know what your expected answer for that line number would be? thanks
Here's the formula - again an array formula so control + shift + enter
=AVERAGE(IF(IF(A2=A3:A$169,B3:B$169)*IF(COUNTIF(INDIRECT("A"&ROW(A3)&":A"&ROW(3:$169)),A2)<=20,1)=0,FALSE,IF(A2=A3:A$169,B3:B$169)*IF(COUNTIF(INDIRECT("A"&ROW(A3)&":A"&ROW(3:$169)),A2)<=20,1)))
Try this one in column D
=AVERAGEIFS($B$2:$B$169,$A$2:$A$169,A2,$C$2:$C$169,">"&MIN(C2,COUNTIF($A$2:$A$169,A2)-20),$C$2:$C$169,"<="&C2+20)
That formula is something! Thank you !
Ok so it working as I want it to for entries that appear more then 20 times and have more then 20 occurrences below.
But row 20 (Lindley) is a example of what is not working as I would like it to. Lindley only appears 6 times. What I would like is in this scenario average all 6 of his appearance .Which would equal 178. So all six times lindley appears = 178. So 20,21,22,57,58,59 would all = 178.
Row 51 (palmer). In this scernario altho palmer does appear more then 20 times in total in column A he only appears once below row 51 in which he scored 327 yrds and that is what is imputed in row 51. I would prefer the average of every appearance so row 51 =284.
What would also be even better which was recently discussed above that in the Palmer case above where row 51 = 284 that the most recent 20 appearance is used instead of every appearance.(in this scenario it will average 20 starting from the bottom) using this method row 51=271.
Thank you again!
OK I think I understand now. Firstly Lindley appears 8 times so I think my solution for row 20 is correct. You've missed rows 54 and 55 which adds 246 104 so average is 177.25
For the less ones where you want it to average ALL the values if less than 20 I'll need to think about that some more and come back to you.
Bingo! Worked as expected! Thank you Thank you.
Looks like Jason got me to where I need to be. Altho I would love your formula as well please dont break a sweat on it. Cant thank you enough for all your hard work.
OK. I looked at Jason's solution and it's giving a different answer to what you expected in row 51 from your post #37.
This will at least give you your desired output in row 51 (as well as many others but I'm still not sure I I've got all the logic right for the less than 20 occurences. I think I do but I'm getting some different answers to Jason but I'm more closely aligned with your example answers in your version 4 file
=IF(COUNTIF(A3:$A169,A2)<=20,AVERAGEIF($A$2:$A$169,A2,$B$2:$B$169),AVERAGE(IF(IF(A2=A3:A$169,B3:B$169)*IF(COUNTIF(INDIRECT("A"&ROW(A3)&":A"&ROW(3:$169)),A2)<=20,1)=0,FALSE,IF(A2=A3:A$169,B3:B$169)*IF(COUNTIF(INDIRECT("A"&ROW(A3)&":A"&ROW(3:$169)),A2)<=20,1))))
As Jason has showed it is much easier with a helper column in C but if you want a single solution this might be as close as you can get in a single formula
Crooza your formula is giving exactly what I asked for. Yours and jason's are identical except the cells where a player appears more then 20 times in column A but down the list far enough that there is less then 20 below and this is only different because Jason mentioned a idea that I liked and he modified it to count most recent 20 when this occurs( bottom up)
Most definitely easier to give a helper column and not to mention it is more user friendly so a not very experienced user can make sense out it like myself. Yours on the other hand although complicated for me to read does the job and since I have to do similar things with many names in different columns/different numbers may be the route for me. I will have to play around with both. Now that I have them I am planing to learn from them.To be honest I am shocked I got exactly what I was after and giving two approaches is just surreal. No idea the fat kids ways to achieve my results you guys saved me from. Thank You Guys!
Great. Glad it worked. Have fun deciphering the two formulas. You'll learn heaps working these few examples out
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks