AverageIF and ABS function

[pyrrhus272bc]

All,

Can someone advise me on this formula:


=IFERROR(AVERAGEIF(AE4:AZ4,1,E4:Z4),"")


How can you embed an ABS function in there? You cannot put it in here abs(E4:Z4) you will get error on the formula.


appreciate any inputs.

[Jonmo1]

Can you post your actual formula?
I'm guessing that is an example because you're doing an average of all 1's.
The average of all 1's is just 1, no matter how many there are.

My bad, you're averaging E to Z, but counting 1's in AE to AZ..
So nevermind that..

Hang on, there's a way.

[Jonmo1]

Try an array formula entered with CTRL + SHIFT + ENTER

=IFERROR(AVERAGE(IF(AE4:AZ4=1,ABS(E4:Z4))),"")

[shg]

AVERAGEIF (like other xxIF functions) requires ranges, not arrays, for both the criteria and the data to be averaged.

=IFERROR(SUMPRODUCT((AE4:AZ4=1)*ABS(E4:Z4)) / COUNTIF(AE4:AZ4, 1), "")

=IFERROR(AVERAGE(IF(AE4:AZ4=1,ABS(E4:Z4))),"")

Well, that's better!

[pyrrhus272bc]

Jonmo,

Completely forgot about that function.

Thanks a mill.

[Jonmo1]

Well, that's better!

Thanks alot!

[martindwilson]

you can do it like this
=SUMPRODUCT(--(AE4:AZ4=1),ABS(E4:Z4))/COUNTIF(AE4:AZ4,1)

[shg]

Just realized that. After you posted, and before I looked closely, I went to VBA to see what Intellisense said:

WorksheetFunction.AverageIf(Arg1 as Range, Arg2, [Arg3])

That, combined with the example I (thought I) saw, made me think I was wrong about the arguments. Intellisense shows the same for the other xxIf and xxIFS functions; only the first shows As Range.

I feel ... betrayed.

[Jonmo1]

Jonmo,

Completely forgot about that function.

Thanks a mill.

You're welcome.

[shg]

The post editing has taken its toll, here, Jonmo