Hi all,
We have spreadsheet that has muliple grades such as ...
John Smith 500 76
John Smith 500 62
Joe Green 1000 42
Joe Green 1000 76
Joe Green 1000 58
John Smith 1000 77
So what I need is a formula that for each person and category, it will take the two highest values and average them. For exam, John Smith 500 - should average 73.62, Joe Green 1000 - should average 76,58, John Smith 1000 - average should be 77
does anyone know how to do this using excel formulas
Can you attach a sample sheet?
I am not exactly sure how your table is structure but the sample file should give you a good start
Enter the formula with Ctrl + Shift + Enter =AVERAGE(LARGE(IF(($A$1:$A$6=F1),$B$1:$B$6),{1,2}))
If those are in columns A1:C6, and you have unique name/category combos listed starting in D1:E1, then in F1:
=AVERAGE(LARGE(IF($A$1:$A$6=D1,IF($B$1:$B$6=E1,$C$1:$C$6)),{1,2}))
confirmed with CTRL+SHIFT+ENTER not just ENTER and copied down
Where there is a will there are many ways.
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
What is the significance of CTRL+SHIFT+ENTER?
It tells Excel that you have input an Array Formula.
I've attached a sample and on the same sheet demonstrated the values to return in the results.. I'll take a look at the suggestions. Thanks all.
Try in K2:
=AVERAGE(LARGE(IF($A$2:$A$13=H2,IF($B$2:$B$13=I2,IF($C$2:$C$13=J2,$D$2:$D$13))),{1,2}))
confirmed with CTRL+SHIFT+ENTER not just ENTER and copied down
Note: in column A, you have the name Brown with extra space at the end.. they need to be removed.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks