Averageif, error, formula not working with errors

[indyfox99]

Hi, I want to average a column (column v) based on a cell criteria of 'm' in column D. I want to exclude any cell in column V that contains N/A. I have this formula that works if all contain numbers but doesn't when N/A appears

=AVERAGEIF('TRACKER'!$D$4:$D$214,"m",'TRACKER'!$V$4:$V$214)

 

[Aladin Akyurek]

Try this:

=AVERAGEIFS(TRACKER!$V$4:$V$214,TRACKER!$V$4:$V$214,"<"&9.99E+307,TRACKER!$D$4:$D$214,"m")

 

[indyfox99]

spot on! your amazing

 

[Marcelo Branco]

Try this:

=AVERAGEIFS(TRACKER!$V$4:$V$214,TRACKER!$V$4:$V$214,"<"&9.99E+307,TRACKER!$D$4:$D$214,"m")

I like it. I could not imagine it could work.

M.

 

[Aladin Akyurek]

spot on! your amazing

You are welcome.

I like it. I could not imagine it could work.

M.

Indeed a better option than a long hand like...

=SUM(SUMIFS(TRACKER!$V$4:$V$214,TRACKER!$V$4:$V$214,{"<0",">0"},TRACKER!$D$4:$D$214,"m"))/SUM(COUNTIFS(TRACKER!$V$4:$V$214,{"<0",">0"},TRACKER!$D$4:$D$214,"m"))

 

[Marcelo Branco]

Aladin,

It's curious. This works
=COUNTIFS(range1,"m",range2,"<"&9.99E+307)

but this does not work (with some #N/A in range2)
=SUMPRODUCT (--(range1="m"), --(range2<9.99E+307))

M.

 

[Aladin Akyurek]

Aladin,

It's curious. This works
=COUNTIFS(range1,"m",range2,"<"&9.99E+307)

but this does not work (with some #N/A in range2)
=SUMPRODUCT (--(range1="m"), --(range2<9.99E+307))

M.

--(range2<9.99E+307)

will evaluate necessarily as something like...

--(TRUE;TRUE;#N/A;...)

{1;1;#N/A;...}

We can say that COUNTIFS filters using an operator like <, while a < test alone does not.

 

[Marcelo Branco]

--(range2<9.99E+307)

will evaluate necessarily as something like...

--(TRUE;TRUE;#N/A;...)

{1;1;#N/A;...}

We can say that COUNTIFS filters using an operator like <, while a < test alone does not.

Perfect! You used the proper verb about COUNTIFS: "filters" instead of "compares"

M.