ABS() treats blanks as 0; is there a workaround for this?

[TomCon]

If you put 5, blank, -10 in A1:A3 and then have this formula (blank is a clear cell empty of any content)
=AVERAGE(ABS(A1:A3))
you get answer 5, which is the average of ABS(5,-10,0)

Of course the "regular" AVERAGE function (without the ABS()) just averages the "present" values and does not treat the blank cell "as if it were value 0"

Is there a workaround for this so that i could have blank cells within a range for the formula AVERAGE(ABS()) and get a result of the Average of the "cells with values" and not treat empty cells as if they were value 0? I have tried IF statements that evaluate to "" or #N/A if the cell is empty but it also does not work. What a pernicious behavior!

If you can think of a workaround for this behavior of ABS() please let me know. Thank you.

 

[jtakw]

Yes, that does it. Thank you! I cannot exactly work out in my mind why this works, doesn't the output vector also still have some spaces with "" in it that correspond to blanks in the input? Oh well, it works!!!!

Yes, thanks, this works too. Possibly a bit more intuitive. I guess the system will not let me mark two responses as "correct solution". But, thanks!

Perhaps changing "Testing for Blanks" this way is a bit more intuitive? (and shorter)

=AVERAGE(IF(A1:A3<>"",ABS(A1:A3)))

 

[Peter_SSs]

Another way would have been be to remove the blanks altogether

=AVERAGE(ABS(FILTER(A1:A3,A1:A3<>"")))