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.
 

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

Forum statistics

Threads
1,081,933
Messages
5,362,235
Members
400,672
Latest member
ExcelGrasshopper

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top