average of absolute values if some cells are left blank.

[GertG]

Hello,


I have a small problem,
An array of cells contains number, negative or positive. Although some cells are left blank in the array. Which cells are blank and which ones aren't vary depending on previous input.
I would like to take the average of the absolute values of the number in the cells. The cells that are blank in the array should be completely ignored.

I was thinking of something like this:
=SUMPRODUCT(ABS(A6:A161))/COUNT(A6:A161)

Although the ABS function doesn't work properly if some of the cells are blank. So there should be something within ABS that checks if the cells contain a number, although i don't know how to do this over an array and still get this to work.


I hope you can help,
Gert

 

[edvwvw]

Rather than use COUNT try COUNTA

edvwvw

 

[mikerickson]

This CSE formula worked for me, but I suspect there is a slicker way of doing it.
=SUM(IF(A1:A9="","x",ABS(A1:A9)))/COUNT(A1:A9)

 

[GertG]

This CSE formula worked for me, but I suspect there is a slicker way of doing it.
=SUM(IF(A1:A9="","x",ABS(A1:A9)))/COUNT(A1:A9)

Doesn't seem to be working for me. It appear that the IF clause only takes the first cell in the array and then there is just a sum of the first cell. The COUNT part does its job off course. At least that's the cast down at my end.


Sincerely,
Gert

 

[pgc01]

Hi Gert
Welcome to the board

Try:

=AVERAGE(IF(ISNUMBER(A6:A161),ABS(A6:A161)))

This is an array formula, you have to confirm it with CTRL-SHIFT-ENTER and not just ENTER.

 

[mikerickson]

PGC's formula is the slicker version that I was talking about.