Return cell value if only 1 result is found, or average if more than 1 result

[paun_shotts]

Hi,

Im having some troubles in creating a formula and hope someone can help me out.

I want to get the average winning margin of NBA sports games if certain criteria are met. I can get it working using AVERAGEIFS formula, but in the case that one team have played another team only 1 time, it cannot return the average of that event, and is returning an error.

If there is only 1 game played between two teams, I want to return that 1 winning margin, if there are more than 1 games, then return the average winning margin of all games between the two teams.

Below is the formula that I have so far:
=AVERAGEIFS('NBA Model'!$S:$S,'NBA Model'!$BD:$BD,A19,'NBA Model'!$T:$T,H18)
Column S shows the winning margin on the sheet "NBA Model"
Column BD shows the game between two teams, eg, "DET v ATL"
Column T shows the team that won
So above we are averaging the winning margin (column S), of the game "DET v ATL",where ATL is the winner of the game (column T)

I have a few screenshots to help out with this. The result returned should be "-25"

Thanks for your help.

 

[paun_shotts]

Anyone have any ideas? Would a macro work?

 

[paun_shotts]

Nevermind, I have worked out a workaround for this, using an if statement to see if the number of games played is greater than or less than and equal to 1, then if less than or equal to 1, do sumifs, if more than 1, do averageifs

 

[StephenCrump]

You shouldn't need a workaround - AVERAGEIFS will cope perfectly well with one match.

There is something else causing the problem. It looks like you have merged cells in column BD, which may play havoc with your results. I'd start looking there!