• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Averageif: criteria in Column B is met, average range Help

A

AlanS1337

New Member
Hello Everyone,


I am putting together a spreadsheet that is composed of 2 teams. I want to list the players in alpha order, which is no problem, but this mixes up the teams. They get a score every month and I want to break down the average by team while excluding the other team's scores. So


A B C D

Player Team Score 1 Score 2

Me A 10 20

you B 20 25

Gary A 12 12


Say I only want the average for Team A. I tried using:


=Averageif((If(B2:B4="A",IF(B2:B4<>"B",C2:D4))),">0")


But that is still returning the whole average of both teams. Any ideas/thoughts? I would prefer to keep this as a formula instead of a VBA or marco but I am open to all suggestions. The goal is to have the Team A Average (which is 13.5 in my example) but I keep getting 16.5 which is the average of everyone.


Thank you in advance!
 
Try this array formula:

=AVERAGE(IF(B2:B4="A",C2:D4))


Array formulas need to be confirmed using Ctrl+Shift+Enter, not just Enter.
 
LUKE!!!


Nevermind! I was trying to use a Range Name for the B2:B4 part of the formula and that seemed to be throwing it off so I changed it to be just the cell range and VIOLA! it worked like a charm! Thank you so much for your help! Now I am going to try to use that same formula but as an Averageif!


You the man Luke!
 
Glad I could help.

Let us know if you get the AVERAGEIF to work. I fiddled with it for a little bit, but it only seemed to want to return the first column of the range (aka, averaged 10 and 12 and gave me 11).
 
I think I have probably cracked the AverageIF function here.


By using the formula as follows:

=AVERAGEIF(B2:C4,"A",D2:E4) = 13.5


no CSE used ;)


Trick -

The trick is I copied the column team name twice,

Reason: As my analysis showed, the range (first parameter's dimension) is taken into account for [average_range] (3rd parameter's dimension) as well, of course wherever the criteria meets.


Hope I am able to explain what I have used above.


Regards,

Prasad DN
 
AlanS1337,


You could also do something like this


Code: 
=SUMPRODUCT((((B2:B4="A")*C2:D4)/SUM((C2:D4>0)*(B2:B4="A"))))

or

=SUMPRODUCT((((B2:B4="A")*C2:D4)/SUM((C2:D4<>"")*(B2:B4="A"))))


Only difference between the two is the C2:D4>0 and C2:D4<>"", depending on how you would input and subsequently ignore instances where a player did not play (by a 0 or blank input)
 
You must log in or register to reply here.
Back
Top