Average If...

[RyanNoodles]

Hi,

I am trying to create a spreadsheet to monitor golf statistics.

If you're unfamiliar with golf, you typically have 18 holes and they are either par 3, par 4 or par 5. I specifically want to monitor the average score on par 3s and the same for par 4s and par 5s.

So far headings look like this:

A1 - Course Played
B1 - Date
C1 - avergae par 3 score
D1 - average par 4 score
E1 - average par 5 score
F1 - hole 1 score
G1 - hole 2 score
and so on through to hole 18

I input each players' score for holes 1-18.

My issue concerns the average score. Lets take the par 3 for example. I have a drop down list for the course played and each course will have different pars for each hole. So I want the average par 3 score cell to say 'If A2 = 'Pebble Beach' then take the average of cells G2, J2, L2 and O2'

So A2 being the course played and G2, J2, L2 and O2 are the holes that are par 3s so they are the scores I want to average for that round.

In addition to that I want the formula to average different holes if A2 = a diffent course that has different holes as its Par 3s.

So if A2 was a different course then different cells should be averaged.

Is this clear?

Btw, the above is simplified, the actually formula I started with is below.

=IF(B4=AT3,"(=AVERAGE(U4,W4,AA4,AE4,AG4,AK4))")

B4 is Course Played
AT3 is also the course played but where it is listed for the purpose of the drop down list in B4
the cells being averaged are the cells that the course in question has as par 3s.

The problem is that I can't get excel to recognise the average part as a formula. It just sees it as text and displays the whole formula instead of displaying the result of the calculation.

I appreciate any help.

Regards,

Ryan

[Ace_XL]

Remove the quotation marks " around the average formula.

Furthermore, you might want to create a dynamic formula on the lines of

=average(if(x=y,rangeofdata,false)
Confirm with Ctrl+Shift+Enter and not just Enter

In later versions of Excel you could also use the AVERAGEIF function.

Uploading a sample worksheet will help understand your problem better

[K m]

IF(B4=AT3,"(=AVERAGE(U4,W4,AA4,AE4,AG4,AK4))")

The Blue part of your formula is wrong
This formula will work but you had only one condition, I added the second condition as a blank ""
=IF(B4="AT3",AVERAGE(U4,W4,AA4,AE4,AG4,AK4),"")

Excel would interprete AT3 as cell AT3 not course AT3. That's why that is in quotes

A sample workbook might help

[RyanNoodles]

Here is a sample worksheet.

I have highlighted the holes taht are par 3s on each course.

Thanks.

[K m]

In your example workbook, the correct formula for cell E6 is:
=IF(C6=C12,AVERAGE(K6,O6,R6,T6),"")

Copy this formula down

[RyanNoodles]

In your example workbook, the correct formula for cell E6 is:
=IF(C6=C12,AVERAGE(K6,O6,R6,T6),"")

Copy this formula down

Thanks that worked!

I have one more issue.

How do I put more than one if formula in the same cell?

So I want to say as in the above quote:

=IF(C6=C12,AVERAGE(K6,O6,R6,T6),"")

however

if C6 = C13 then I want different cells to be used in the average formula

and also different ones for if C6 = C14 for example

Thanks again.

Ryan

[Ace_XL]

Use..

=IF(C6=C12,AVERAGE(K6,O6,R6,T6),IF(C6=C13,AVERAGE(..............),IF(C6=C14,AVERAGE(...............),"")))

[Kevin UK]

Hi Ryan

I have put the Pars in Cells E1:G1 and used the sumif function within the average function. So if the hole pars differ you should have them covered! Copy of sheet attached

In Cell: E6 and drag across to G6, down to G8.
=IF($C6=$C12,AVERAGE(SUMIF($H6:$Y6,E$1)),"")

[RyanNoodles]

Use..

=IF(C6=C12,AVERAGE(K6,O6,R6,T6),IF(C6=C13,AVERAGE(..............),IF(C6=C14,AVERAGE(...............),"")))

That's brilliant thanks it worked perfectly.

[RyanNoodles]

I have another question.

I need a formula to calculate the average score for each individual hole on the specific courses.

I've attached another sample worksheet; the '?' are the cells concerned.

Cell D17 would need to calculate the avergae of all the scores on hole 1 for each round played, but only when that round was played at Course A. D18 would do the same for Course B rounds and cell D19 for Course C rounds.

Thanks,

Ryan.

[Kevin UK]

How about you have a go. I think ever one has given you enough info!

[RyanNoodles]

How about you have a go. I think ever one has given you enough info!

I've been on this bit for over an hour. I'll keep trying but if anyone has any advice I'd appreciate it.

Thanks.

[RyanNoodles]

Got it.

Didn't know I could use AverageIf in 2003 so had to use an array.

D17=AVERAGE(IF(B6:B8=B12:B14,D6:D8))

Thanks again for the first bit I would never have got that.

Ryan

[icestationzbra]

"AVERAGEIF" is the name of a new function available only in versions 2007 onwards.

"AVERAGE(IF", as employed here "D17=AVERAGE(IF(B6:B8=B12:B14,D6:D8)", is not exactly the same as the "AVERAGEIF" function.

just to clarify...