AVERAGE IF ABS(Range)>x

[Gourav]

Hi all,

I've been struggling with the COUNTIFS and AVERAGEIFS functions when dealing with the ABS function.

What I want to do is:

=COUNTIFS(Criteria_range1,criteria1,Criteria_range2,criteria2, etc...)

Which is working fine until I want to use the ABS function within this function, for example count if column A contains 1 and column D contains any number greater than 1. (same for average)


=COUNTIFS(A:A,1,D:D,">1") and =AVERAGEIFS(D:D,A:A,1,D:D,">1")

How can I amend this so I can change that second criteria to look at the absolute values stored in column D...

=COUNTIFS(A:A,1,ABS(D:D),">1") and =AVERAGEIFS(D:D,A:A,1,ABS(D:D),">1")

Doesn't work and I've been struggling with other ways of writing this so excel knows what I want it to do.

Can I use INDEX(ABS(D:D),0,1) somehow? I have used this to find a average, with other criteria..

=AVERAGE(IF(Criteria_Range1=Criteria1,INDEX(ABS(D:D),0,1)))
to give me the average of the Absolute values in D that meet the criteria, could I somehow use this to solve my issue.

Appreciate the assistance.

Gourav

 

[Gourav]

Sorry bit of a mistake....

should read...

Which is working fine until I want to use the ABS function within this function, for example count if column A contains 1 and the absolute value (in the cells) of column D contains any number greater than 1. (same for average)

Thanks again

 

[Aladin Akyurek]

Hi all,

I've been struggling with the COUNTIFS and AVERAGEIFS functions when dealing with the ABS function.

What I want to do is:

=COUNTIFS(Criteria_range1,criteria1,Criteria_range2,criteria2, etc...)

Which is working fine until I want to use the ABS function within this function, for example count if column A contains 1 and column D contains any number greater than 1. (same for average)


=COUNTIFS(A:A,1,D:D,">1") and =AVERAGEIFS(D:D,A:A,1,D:D,">1")

How can I amend this so I can change that second criteria to look at the absolute values stored in column D...

=COUNTIFS(A:A,1,ABS(D:D),">1") and =AVERAGEIFS(D:D,A:A,1,ABS(D:D),">1")

Doesn't work and I've been struggling with other ways of writing this so excel knows what I want it to do.

Can I use INDEX(ABS(D:D),0,1) somehow? I have used this to find a average, with other criteria..

=AVERAGE(IF(Criteria_Range1=Criteria1,INDEX(ABS(D:D),0,1)))
to give me the average of the Absolute values in D that meet the criteria, could I somehow use this to solve my issue.

Appreciate the assistance.

Gourav

CountIf(s), SumIf(s), and AverageIf(s) do not process array objects, such as those that obtain from a transformation applied to range objects like:

MONTH(A2:A10)
LEFT(E2:E10)
ABS(D2:D10)

When a transformation is needed, one option is to switch to array formulas...

Control+shift+enter, not just enter:

=AVERAGE(IF(A2:A100=1,IF(ABS(D2:D100)>1,D2:D100)))

yields an average of D when A = 1 and ABS of D > 1.

=SUM(IF(A2:A100=1,IF(ABS(D2:D100)>1,1)))

yields a count.

 

[circledchicken]

Hi,

Perhaps try:

For the COUNT:
=SUMPRODUCT((A1:A100=1)*(ABS(D1:D100)>1))

For the AVERAGE (this is an array formula so use CTRL-SHIFT-ENTER rather than just ENTER):
=AVERAGE(IF(A1:A100=1,IF(ABS(D1:D100)>1,D1:D100)))

 

[Scott Huish]

For the COUNT:

=sumproduct(--(a:a=1),--(abs(d:d)>1))

 

[Gourav]

Thanks everyone! Sorted.

Aladin, that was perfect (and simple!)

 

[Gourav]

OK, not quite perfect...

I realised that when calculating the average, I need to use the ABS value of the values, I tried just doing this

{=AVERAGE(ABS(IF(A2:A100=1,IF(ABS(D2:D100)>1,D2:D100))))}

and

{=SUMPRODUCT(ABS(IF(A2:A100=1,IF(ABS(D2:D100)>1,D2:D100))))}

but that doesn't work. Any ideas?

 

[barry houdini]

Try this version....

=AVERAGE(IF(A2:A100=1,IF(ABS(D2:D100)>1,ABS(D2:D100))))

 

[Gourav]

Thanks! That's done the trick.