EXCEL: Using Sumif to find Averages... 1

[rossmcl]

Hello

I have two columns in Excel. Column A contains Football Clubs, Column B contains Goals Scored (there is a diferent row per week)

I want to create an summary for each football club, of the average number of goals they scored in the season.

Do I use Sumif (or another function) to find the average of Column B?

How do I do it? (do I need to use { } 's ?

Thanks
rossmcl

 

[xlbo]

=SUMIF(Teams,Team,Goals)/COUNTIF(Teams,Team)
where Teams is the range of data holding your tema names
Team is the name of the particular team (or a cell ref holding the name)
Goals is the range holding the goals scored


Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[gizmo1973]

=average(b2:b5)

b2 being the 1st goals scored and b5 the last

Regards, Phil

"If in doubt, hit it with an end user!

 

[cLFlaVA]

You could use a PivotTable. Create one for the entire selection (clubs and goals).

Drag clubs into the row field section, and drag goals into the data field section. Then, double-click the gray goals box and choose Average.

*cLFlaVA
----------------------------
A pirate walks into a bar with a huge ship's steering wheel down his pants.
The bartender asks, "Are you aware that you have a steering wheel down your pants?"
The pirate replies, "Arrrrr! It's driving me nuts!

 

[SkipVought]

...and yet another TOOL available is the SUBTOTAL feature in Data/Subtotals...

Data must be sorted properly. Sub breaks are possible.

Skip,
[sub]
[red]Be advised:[/red]
Alcohol and Calculus do not mix!
If you drink, don't derive! [/sub]

 

[rossmcl]

Thanks all.

xlbo:

Hi

Thanks for you suggestion, this works (kind of). The Sumif part works perfectly, but the COUNTIF part is not doing exactly what I need it to do.

Basically the COUNTIF is including ALL rows with the Team, and I only want it to average the rows where a number or 0 is present.

ie if the team did not play on a particular weekend the cell for goals will be blank. I do not want to include this when finding the average. How can I do that?

Thanks
rossmcl

 

[gizmo1973]

=Average(your data)will do this for you.
i.e. zero or score average of data
no data not part of average

Regards, Phil

"If in doubt, hit it with an end user!

 

[xlbo]

=SUMIF(Teams,Team,Goals)/SUMPRODUCT((Teams=Team)*(Goals<>""))


Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[rossmcl]

Hi Geoff, your formula suggestion didnt work for me. Let me explain exactly what I am trying to do, as I may have not been clear.

For example, this is the data I have. Some A's, soem B's etc.

A 6
A
A 6
B 1
B
BA 5
A 4
A
A 3
B 3

For the above dataset, I want the averages to be calculated as follows:

A 4.75 (ie 3 + 4 + 6 + 6 = 19/4 = 4.75)
B 2 (IE 1 + 3 = 4 / 2 = 2

IE I only want to divide by the number of As or Bs that actually have values (ie are not blank)

The equation you gave me did not find these.

I would really really really appreciate some more help, this is driving me crazy.

Thanks
rossmcl

 

[gizmo1973]

As I posted the =average will do what you want for this if you format your data correctly

Regards, Phil

"If in doubt, hit it with an end user!

 

[rossmcl]

But with average it uses all the cells you give it. I want it to get a result by telling it to average on a range of cells, and only when it is a particular value (ie A or B).

I think Average is a bit simplistic for what I am trying to do?

rossmcl

 

[gizmo1973]

From your original post I imagine your data to be like this

A B C D
Team Week 1 Goals Week 2 goals

Man U 1 3
LFC 2 4
NFC 0 3
CFC 5

If you then had =average in column D (and moved it along weekly or set it to amount of games in season) this would give you the average of goals scored in matches played.
Am I barking at the wrong end of the stick?
I only ask as I have set up something very similar

Regards, Phil

"If in doubt, hit it with an end user!

 

[DaleWatson123321]

rossmcl,

As it appears you're still in need of a (complete) solution, I've put together an example file with formulas that provide the complete results you require.

If you email me, I'll send the file via return email.

Hope this helps.

Regards, ...Dale Watson dalwatson@gov.mb.ca