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]

Hi,

Not sure if I understand 100%, but how about putting ABS Outside AVERAGE:

=ABS(AVERAGE(A1:A3))

 

[Fluff]

How about

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

 

[kweaver]

I suppose you could do this:
[ didn't pass all my tests ]

 

[kweaver]

With a (spill) helper.

Book1
	A	B	C	D
1	5		7.5	5
2				FALSE
3	-10			10
Sheet1

Cell Formulas
Range	Formula
C1	C1	=AGGREGATE(1,2,D1:D3)
D1:D3	D1	=IF(NOT(ISBLANK(A1:A3)),ABS(A1:A3))
Dynamic array formulas.

 

[jtakw]

Yeah, I probably mis-understood OP.

@kweaver , briefly saw your answer in Post #4, think it would have worked, you just didn't include ABS:

=AVERAGE(IF(ISNUMBER(A1:A3),ABS(A1:A3)))

and for your Post #5, why not just this without the "helper":

=AVERAGE(IF(NOT(ISBLANK(A1:A3)),ABS(A1:A3)))

With these formulas including Fluff's in Post #3, for me, using 2016, I have to enter as CSE, but I think, since all of you have 365, including OP, it is entered normally (Not sure).

 

[TomCon]

How about

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

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!!!!

 

[Fluff]

It's the Abs function that is turning the blanks into 0, so that formula only runs the Abs on those cells that are not ""
The Average function simply ignores the "" blank values.

I think, since all of you have 365, including OP, it is entered normally

That's right, no more CSE for us.

 

[TomCon]

Yeah, I probably mis-understood OP.

@kweaver , briefly saw your answer in Post #4, think it would have worked, you just didn't include ABS:

=AVERAGE(IF(ISNUMBER(A1:A3),ABS(A1:A3)))

and for your Post #5, why not just this without the "helper":

=AVERAGE(IF(NOT(ISBLANK(A1:A3)),ABS(A1:A3)))

With these formulas including Fluff's in Post #3, for me, using 2016, I have to enter as CSE, but I think, since all of you have 365, including OP, it is entered normally (Not sure).

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!

 

[jtakw]

You're welcome, thanks for the feedback.