Averaging Question

[civilred]

Hi All,

I am trying to pull together a spreadsheet that will allow me to average a "total" column using dates.

A2: TO A500 are cells that hold dates " 01/03/2012 etc
H2: TO H500 are cells that hold totals ie 15, 56, 27 etc
K2 is called "First Week"
L2 is called "Second Week"
these two cells have drop downs that allow me to choose dates that correspond to the dates in cells A2 - A5
M2 is called Average - this is the cell that i need the formula in. I would like it to calculate the average of two dates selected in K2 and L2, from the totals from H2 TO H500. The problem i have is i want it to only use certain cell numbers in the H column ie H2,H6,H9,H12 etc.

Can this be done?

I know this is long winded by i try my best.

Thank you in advance.

Red

 

[pplstuff]

Are there multiples of the same dates? Like, if you have "1/3/12", will there be multiple dates that match? So you'd want to sum all the matching dates, and then divide by n?

 

[civilred]

Hi Thanks for getting back to me. No there will be no double dates.

Red

Are there multiples of the same dates? Like, if you have "1/3/12", will there be multiple dates that match? So you'd want to sum all the matching dates, and then divide by n?

 

[buxtongt]

Sounds like you could do this with a sumif formula divided by a countif. This would allow you to total up all the numbers you wanted and then divide by the number of entries, giving you the average. Hope this helps.

 

[civilred]

Thanks for you advice, so how would I do this as a formula?

Red

Sounds like you could do this with a sumif formula divided by a countif. This would allow you to total up all the numbers you wanted and then divide by the number of entries, giving you the average. Hope this helps.

 

[buxtongt]

=SUMIF(A:A,14,B:B)/COUNTIF(A:A,14)

This adds up the values in column B where the same row in A is 14 and divides that by the number of times 14 appears in column A.

 

[civilred]

Sorry don't think this would work for me. I have title days - Mon Tue etc in c,d,e,f,g columns, numbers go into each day and I total these up and the total goes into cell H2.

=SUMIF(A:A,14,B:B)/COUNTIF(A:A,14)

This adds up the values in column B where the same row in A is 14 and divides that by the number of times 14 appears in column A.

 

[buxtongt]

You are going to have to modify it some to make it fit your application exactly, but if I'm reading everything correctly that formula will do what you need it to. It will pull and sum the values that correspond to the date you tell it and divide that by the total number of entries it added up, to give you an average.

 

[civilred]

This is an example of what i am trying to do:
I need to put a date in K and L eg 01/07/2012 & 08/07/2012 and and it will work out the average totals from the two weeks selected but i want it to only count for example option ESA which would be totals in h2 and h6 etc( i could have it count straight down the column counting only esa totals.

A B C D E F G H K L M

Date Option Mon Tues Wed Thur Fri Total First Week Second Week Average
01/07/2012 esa 6 10 12 14 13 55
01/07/2012 lp
01/07/2012 trt
01/07/2012 tyr
08/0702012 esa
08/0702012 lp
08/0702012 trt
08/0702012 tyr