why is the formula below not working...It returns the #VALUE!

=AVERAGEIF((C5:C12,C15:C18,C21:C30,C33),"<0")

Thanks

Curtis wrote:
why is the formula below not working...It returns the #VALUE!

=AVERAGEIF((C5:C12,C15:C18,C21:C30,C33),"<0")

Thanks


#VALUE! error

Occurs when the wrong type of argument or operand is used.

Possible causes and solutions:


1. Entering text when the formula requires a number or a logical value, such as
TRUE or FALSE.

Microsoft Excel cannot translate the text into the correct data type. Make sure
the formula or function is correct for the required operand or argument, and
that the cells that are referenced by the formula contain valid values. For
example, if cell A5 contains a number and cell A6 contains the text "Not
available", the formula =A5+A6 will return the error #VALUE!.



2. Entering or editing an array formula, and then pressing ENTER.

Select the cell or range of cells that contains the array formula, press F2 to
edit the formula, and then press CTRL+SHIFT+ENTER.



3. Entering a cell reference, a formula, or a function as an array constant.

Make sure the array constant is not a cell reference, formula, or function.



4. Supplying a range to an operator or a function that requires a single value,
not a range.

Change the range to a single value. Change the range to include either the same
row or the same column that contains the formula.



5. Using a matrix that is not valid in one of the matrix worksheet functions.

Make sure the dimensions of the matrix are correct for the matrix arguments.



6. Running a macro that enters a function that returns #VALUE!.

Hi Curtis,

You can do it if you take each sum average separately, then average the
whole thing.

=AVERAGE(AVERAGEIF((C5:C12),"<0"),AVERAGEIF((C15: C18),"<0"),AVERAGEIF((C21:C30),"<0"),AVERAGEIF(( C33),"<0"))

Squeaky

"Curtis" wrote:

why is the formula below not working...It returns the #VALUE!

=AVERAGEIF((C5:C12,C15:C18,C21:C30,C33),"<0")

Thanks