In excel I would like to get the average salary of the top 4 performing baseball players on each team. I can not for the life of me get around which criteria to make this work. I can get the average salary of the players on one team, I can even get the average salary of players with Proj > 7, just not the average of the top 4.
Anyy suggestions to help me get over this hump?
averagifs.PNG
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.
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
I have attached a JPG, but I am still trying to figure out how to add the sample xls. Still new to the forums so working things out but will catch up eventually
averagifs.jpg
Try something like this.
Array entered**:
=AVERAGE(IF(Team="Mil",IF(Proj>=LARGE(IF(Team="Mil",Proj),4),$$)))
** 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.
Thanks for the reply. I am still trying to figure out how to attach the sample xls. for some reason no files show up in the drop down even after going to manage attachments. But all that aside. Here is the current formula I am using to get average salary
=AVERAGEIFS(HITTERPROJECTIONS[$$],HITTERPROJECTIONS[team],[@TEAM],HITTERPROJECTIONS[Proj],">7")
This gets the average of players on a team projected to get more than 7 pts. Sometimes a team will have 7 or 8 players projected above 7pts, so I only want the top 4 players.
I hope that adds some clarification.
Unfortunately, it doesn't!Originally Posted by sangamc
Is there something I am missing when it comes to attaching a sample xls? That would probably be the best way to show what I have?
In the meanwhile, using the code tags. Here is the source table with the data. Below it is the destination table where I am trying to get the average salary of the top 4 projected players for each team, currently =AVERAGEIFS(HITTERPROJECTIONS[$$],HITTERPROJECTIONS[team],[@TEAM],HITTERPROJECTIONS[Proj],">7") gives me the average salary of players above 7pts
Table = HITTERPROJECTIONS
Please Login or Register to view this content.
Destination table
Please Login or Register to view this content.
Ok, so what exactly do you want?
Do you want the average of the top 4 salaries per team or do you want the average of the top 4 salaries with a "proj" >7 per team?
I really can't advise you on how to attach a sample file as the process seems to be different depending on what broswer you use.
I would like to see the average salary of the top 4 projected players. So for example On the following team, I would like to see the average salary of Paul Golds, Jake Lamb, Jean Segura & Chris Herrmann.
When I use Proj > 7, This specific team has 5 players that meet that condition which would mess up my results.
Please Login or Register to view this content.
Try this...
Data Range
This array formula** entered in G2 and copied down:
=AVERAGE(IF(B$2:B$14=F2,IF(C$2:C$14>=LARGE(IF(B$2:B$14=F2,C$2:C$14),4),D$2:D$14)))
** 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.
That works perfectly!!!
Thank you for the assistance and the extra patience with me. Much appreciated
You're welcome. Thanks for the feedback!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks