Average Function - Errors in arguments - Why the function doesn't validate the arguments?

Question

There is an error in the average function. When arguments are not set average takes them as 0

AVERAGE(10,20,30) = 20

AVERAGE(10,20,30,)= 15

There is no argument after the last comma and excel is defining as "0" the value

Same for AVERAGE(19,,20,30) = 15

There are no arguments between the commas and excel is calculating as 0

Why the function doesn't validate the referenced argument?

Answer 1

That applies to cells as arguments. In a formula such as

=AVERAGE(A1:A5)

or

=AVERAGE(A1,A3,A5,A7,A9)

empty cells will not be included in the calculation,

You can let Microsoft know about this by selecting File > Feedback in Excel and clicking Send a Frown.

Answer 2

When you read the help for the function it says that if a reference argument contains text, logical values or empty cells, those values are ignored.....

Syntax

AVERAGE(number1, [number2], ...)

The AVERAGE function syntax has the following arguments:

• Number1    Required. The first number, cell reference, or range for which you want the average.
• Number2, ...    Optional. Additional numbers, cell references or ranges for which you want the average, up to a maximum of 255.

Remarks

• Arguments can either be numbers or names, ranges, or cell references that contain numbers.
• Logical values and text representations of numbers that you type directly into the list of arguments are not counted.
• If a range or cell reference argument contains text, logical values, or empty cells, those values are ignored; however, cells with the value zero are included.
• Arguments that are error values or text that cannot be translated into numbers cause errors.
• If you want to include logical values and text representations of numbers in a reference as part of the calculation, use the AVERAGEA function.
• If you want to calculate the average of only the values that meet certain criteria, use the AVERAGEIF function or the AVERAGEIFS function.

Answer 3

When the Formula generator opens, it does not tell you which number is being calculated either, but it calculates with Zero.....

Answer 4

Of course I am not supposed to do that. It was a mistake that my daughter made and asked me why the difference.

A mistake that anybody can do and the function does not control that error or maybe it should consider a NULL value instead of Zero in order to calculate properly.

Answer 5

Why would you want to omit arguments? Microsoft would probably argue that you're not supposed to do that...