Help with YTD Average formula.

xcarl86x

New Member
Joined
Feb 13, 2012
Messages
4
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.
 

Some videos you may like

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
Joined
Oct 10, 2006
Messages
32
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
Joined
Feb 13, 2012
Messages
4
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
Joined
Oct 10, 2006
Messages
32
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

New Member
Joined
Feb 13, 2012
Messages
4

ADVERTISEMENT

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.
 
Last edited:

secondtry

New Member
Joined
Oct 10, 2006
Messages
32
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

New Member
Joined
Feb 13, 2012
Messages
4
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,064
Messages
5,569,984
Members
412,301
Latest member
excelmaps
Top