You've entered too many arguments for this function

[Guillaume Hanique]

Hello,

First of all: happy new year!

I have a sheet that displays data of different weeks for a period. See
below.

A B C D E F
+--------+------------+--------+------------+---------+-------------+
| Week 1 | Week 1 dbl | Week 2 | Week 2 dbl | Average | Average dbl |
+--------+------------+--------+------------+---------+-------------+
| 8:00 | 8 | 4:30 | 4.5 | 6:25| 6.25 |

I have a formula that converts a double value to time in cells A1, C1
and E1.
F is the average of the weeks, so in this case: =AVERAGE(B1, D1)

After entering all the values and formulas I hide columns B, D and F,
giving me the following sheet.
A C E
+--------+--------+---------+
| Week 1 | Week 2 | Average |
+--------+--------+---------+
| 8:00 | 4:30 | 6:25|

This works fine for a few weeks, but when I want to have an overview for
year, I get more that 30 arguments in my average function. Excel
generates an error: You've entered too many arguments for this function.

I can't find a workaround and hope that anybody can help me.

Guillaume Hanique.

 

[Leo Heuser]

Hello Guillaume

If you use every other cell for the time, this
array formula will do the job:

=AVERAGE(IF((MOD(COLUMN(A2:IV2),2)=
MOD(COLUMN(A2),2))*(A2:IV2<>""),A2:IV2))

To be entered with <Shift><Ctrl><Enter> instead of <Enter>,
also if edited later. If done correctly, Excel will display the formula
in the formula bar enclosed in curly brackets { } Don't enter these
brackets yourself.

 

[Guillaume Hanique]

Thanks!

 

[Leo Heuser]

You're welcome.