“AverageIf” with too many arguments. What am I doing wrong?

[Richard J. Summers]

I have a budget spreadsheet where I need an average of numbers from a particular cell located in 12 different sheets, ‘January’ through ‘December’, without it counting the blank cells as part of the divisor in the average calculation. If only “12” and “6” are entered, I want it to give an average of “9” (the average of 12 and 6), and not “1.5” (the total of 18 divided by 12 (months)).


The “Average” function does not exclude those blanks, so I tried the “AverageIf” with the ,"<>" at the end. It looks like this:

In the C column:

=AVERAGEIF(January!D2,February!D2,March!D2,April!D2,May!D2,June!D2,July!D2,August!D2,September!D2,October!D2,November!D2,December!D2,"<>")


This I think, would work, but it doesn’t. I have a Google Sheets spreadsheet that does this, with that exact formula, but I want it to work in Excel, which I believe to be a much better program. Excel processes the inputs much faster than Google Sheets. Plus, more people have it.


My problem is, that in Excel I get a warning that states

“You’ve entered too many arguments for this function”​

Too many arguments? How do I get around that?


I have a strong feeling that I am close, but am not doing something quite right. Of course I am open to any suggestions, or even an entirely different way to accomplish this, and I would really be thankful for any help. The spreadsheet is attached if you need to see the whole thing.

I really hope someone can help.

 

[Shteven]

Without opening your attachment, does this work?

=AVERAGE(January:December!D2)

 

[xlstime]

Sir,

As I understand with sample data set

You are using sum function in "Month D2" and by nature sum returns 0 if no value to sum hence average would consider this as value.

I suggest you to please change your sum formula like
instant of SUM(F2:AJ2) use if(SUM(F2:AJ2)=0,"",SUM(F2:AJ2))

than cell E2 would be =SUM(B2-SUM(F2:AJ2)).

One more think, excel provides 3D reference functionality, 3D-reference in Excel refers to the same cell or range on multiple worksheets

with 3D reference your average formula would be

=AVERAGE(January:December!D2)

I have a budget spreadsheet where I need an average of numbers from a particular cell located in 12 different sheets, ‘January’ through ‘December’, without it counting the blank cells as part of the divisor in the average calculation. If only “12” and “6” are entered, I want it to give an average of “9” (the average of 12 and 6), and not “1.5” (the total of 18 divided by 12 (months)).


The “Average” function does not exclude those blanks, so I tried the “AverageIf” with the ,"<>" at the end. It looks like this:

In the C column:

=AVERAGEIF(January!D2,February!D2,March!D2,April!D2,May!D2,June!D2,July!D2,August!D2,September!D2,October!D2,November!D2,December!D2,"<>")


This I think, would work, but it doesn’t. I have a Google Sheets spreadsheet that does this, with that exact formula, but I want it to work in Excel, which I believe to be a much better program. Excel processes the inputs much faster than Google Sheets. Plus, more people have it.


My problem is, that in Excel I get a warning that states

“You’ve entered too many arguments for this function”​

Too many arguments? How do I get around that?


I have a strong feeling that I am close, but am not doing something quite right. Of course I am open to any suggestions, or even an entirely different way to accomplish this, and I would really be thankful for any help. The spreadsheet is attached if you need to see the whole thing.

I really hope someone can help.

 

[vletm]

1) Your 'Last 12 Month Average' ? but You try to calculate always from same months? Isn't last 12 Month in January = previous years values and so on?
2) If You really want to use AVERAGE then those 'unwanted' cells should be empty!
3) Could You recheck SYNTAX? AVERAGEIF(range,criteria,average_range)
4) Check again what do You want to solve.
Ideas ... questions?

 

[David Evans]

Can you get around the Average issue by Counting the number of cells that contain numbers greater than 0, and using that as the denominator in your calculation?

Vletm is on to something in how you calculate your trailing 12 months - your current setup does not appear to accommodate that ...

Also try using the 3-d reference in your averageif for the range

=Averageif(January:December!D2, etc ....