Ignoring Text in a formula

[Anto111]

Hi guys,

I have the following formula:

=IFERROR(SUMPRODUCT(--(RIGHT(B6:B33,3)="DEF"),K6:K33)/SUMPRODUCT(--(RIGHT(B6:B33,3)="DEF"),--(K6:K33>0)),"-")

In the second part of the formula I have specified to ignore cells
containing zero values, however I also need to specify to the formula not to
count cells containing text.

Any help on what I need to add to the formula would be highly appreciated.

Kind regards,

Ant

 

[Bob Phillips]

=IFERROR(SUMPRODUCT(--(RIGHT(B6:B33,3)="DEF"),K6:K33)/SUMPRODUCT(--(RIGHT(B6:B33,3)="DEF"),--(K6:K33>0),--(ISNUMBER(K6:K33))),"-")

but you can use AVERAGEIFS

=IFERROR(AVERAGEIFS(K6:K33,B6:B33,"DEF",K6:K33,">0"),"-")

as AVERAGE ignores blanks

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Joel]

I would use ISNUMBER

=IFERROR(SUMPRODUCT(--(RIGHT(B6:B33,3)="DEF"),K6:K33)/SUMPRODUCT(--(RIGHT(B6:B33,3)="DEF"),--(K6:K33>0),--(ISNUMBER(K6:K33))),"-")