# Help with YTD Average formula.

#### xcarl86x

##### New Member
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:

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.

### Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

#### secondtry

##### New Member
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

##### New Member
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.

Last edited:

#### secondtry

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

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

##### New Member

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
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.

Last edited:

#### secondtry

##### New Member
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.

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

#### xcarl86x

##### New Member
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.

Replies
3
Views
185
Replies
0
Views
138
Replies
0
Views
235
Replies
6
Views
172
Replies
0
Views
3K