Help with YTD Average formula.

[xcarl86x]

Hi i'm working on a spreadsheet to show the monthly and YTD averages of Hands per hour/Spins per hour on 3 casino games.
the problem i have is my YTD average has DIV/0! error , i want the ytd average to ignore months that haven't been input yet. e.g BJ hands per hour in Jan=70 so at this point the ytd av. should also be 70.the ytd average will only work when all months have values, i have tried the formula:
=AVERAGE(H42:J42,L42:N42,P42:R42,T42:V42,X42:Z42,AB42:AD42)/COUNTIF(H42:J42,L42:N42,P42:R42,T42:V42,X42:Z42,AB42:AD42,''<>0'')

and just get the error mesage:

You've entered too many arguments for this function.

H42:J42 is Roulette av. spins p/h, BJ Av Hands p/h and 3 card poker Av H p/h in january and so on.
I have only 6 months in the sheet so far to get the formulas working first.

 

[secondtry]

If you have Excel 2010 I would try using an AVERAGEIF formula. What data is in every 4th column that you're ignoring for the calculation? It might help in finding a solution.

 

[xcarl86x]

The data i want to ignore is the data not input so far this year, so just the blank cells in future months.
I only want an average of the months filled out as opposed to dividing the data by 12.
Is there a way to attach the document on here? to give a better idea.

I'm using Excel starter 2010

Sorry every forth column is just a space between the months.

 

[secondtry]

Okay, so for your example the following formula should return the average for 6 months (where data is input) per game:

=AVERAGEIF(H42:AD42,"<>0")

If you want the average of all 3 games per month just multiply by 3. Adjust the range to cover all 12 months and it shouldn't change the result.

 

[xcarl86x]

I need 3 formulas to determine each seperate game rather than all three.
so Av of H42,L42,P42,T42,X42,AB42.
then AC42,Y42,U42,Q42,M42,I42
then J42,N42,R42,V42,Z42,AD42.
I realised my earlier mistake i posted the wrong formula.
I tried =AVERAGEIF then selected each cell to average and ,"<>0" on the end and the cell just said NAME.

i've tried
=AVERAGEIF(H42,L42,P42,T42,X42,AB42,''<>0'') and still too many arguments.

 

[secondtry]

Ah, I see. In that case, you'd use AVERAGEIFS.

I'm going to assume you have a header row somewhere above the monthly data identifying, for example, "Roulette", "Blackjack" and "Poker". I'll assume this is found in row 41 and you can adjust as fits.

=AVERAGEIFS(H42:AD42,H42:AD42,"<>0",H41:AD41,"Roulette")

Same formula for the other 2 games but replace criteria as "Blackjack", "Poker", etc.

 

[xcarl86x]

Thanks for all of your help i didn't get it to work using 'AVERAGEIF', i don't know if it has something to do with the data in column 42 already being an average of the data in rows 3-40 but anyway i did it by just:

=AVERAGE(H3:H40,L3:L40,P3:P40,T3:T40,X3:X40,AB3:AB40)

Maybe i was over complicating things but this seems to work anyway.

Thanks again.