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.
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
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.
 
Upvote 0
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:
Upvote 0
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.
 
Upvote 0
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:
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,253
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top