Averageif Approximate Match

  • Thread starter Thread starter Pablo
  • Start date Start date
P

Pablo

I am using an averageifs function on an array and would like to
further refine the average by making it average approximate matches.
Let me explain further.

Column A is a list of product types. They are 100, 100r, 100m, S04,
S04r, S04m, etc., then sales data is in column B. I have been able to
average the exact match of each product, but I would like to average
the general product type so that I have a sales average of all the
variations of the 100s, the S04s, etc.
 
I am using an averageifs function [....]
Column A is a list of product types. They are 100, 100r,
100m, S04, S04r, S04m, etc., then sales data is in column
B. I have been able to average the exact match of each
product, but I would like to average the general product
type so that I have a sales average of all the variations
of the 100s, the S04s, etc.
Click to expand...

I do not have XL2007 or XL2010, but according to online sources,
AVERAGEIF and AVERAGEIFS supports wildcards. So the following should
do what you ask for:

=AVERAGEIFS(B1:B1000,A1:A1000,"100*")
=AVERAGEIFS(B1:B1000,A1:A1000,"S04*")

Alternatively:

=AVERAGEIF(A1:A1000,"100*",B1:B1000)
=AVERAGEIF(A1:A1000,"S04*",B1:B1000)
 
Back
Top