Conditionals containing variables

[ChevyChem]

I have a dataset with over 1000 records contained in two columns. First
column is product name...second column is a measured quantitiy for that
product. Originally the data was accumulated over time, but they have been
sorted to group the products. I need to average the measured quantity for
each product. I can do it by hand but short of VBA is there a way to go
through and have an average for each measured quantity for each product done
automatically.

ex. ProdA 1
ProdA 3
ProdA 2
ProdB 4
ProdB 6
I can see comparing the product names in an IF statement but it seems it
will require a variable in the IF to get it to average each product
separately.

 

[Sean Timmons]

Sounds easiest using a pivot table

Click anywhere within your table

Data - Pivot Table

Drop your products into row fiels and your amounts into data fields.

Right-click within one of the cells with an amount and select Field Settings

Click Average.

click the Number btton to format as needed.

 

[Mike H]

If I understand correctly then maybe

=AVERAGE(IF(A1:A20="ProdA",B1:B20,0))

This is an array formula which must be entered with CTRL+Shift+Enter and NOT
just enter. If you do it correct then Excel will put curly brackets around
the formula{}. You can't type these yourself. If you Edit the ranges
then you must re-enter as An array.

Mike

 

[Sean Timmons]

The other option, if you have a limited number of products is to have a table
with your product names in, say, column C, then in D2:

=Sumif(A:A,C2,B:B)/countif(A:A,C2)

would give the average per product...

 

[Sheeloo]

Assuming Col C has the list of unique product names
(You can get that by Data|Advance Filter|Uniqe Records only pasted to C1)
Copy or type this in Col D
=AVERAGE(IF(A1:A1000=C1,B1:B1000,""))
and press CTL-SHIFT-ENTER
This will give you the average for the product in C1
You can copy this formula down to the end of your dataset in Col C...

Adjust 100 to the end of your dataset in Col A-B

 

[FSt1]

hi
assuming your product names are in column A and your measures are in column
B....
=SUMIF(A2:A1000,"ProdA",B2:B1000/COUNTIF(A2:A1000,"ProdA")

you would need a formula for each product.

Regards
FSt1

 

[David Biddulph]

Did you test that, Mike?

Your formula will throw in a zero for each row that *doesn't* have "ProdA"
in column A, and include all those zeroes in the average.
Just leave out the ,0 from your formula, and make it
=AVERAGE(IF(A1:A20="ProdA",B1:B20))
still array-entered.

 

[Mike H]

No i didn't test and now wished I had, thanks for the correction

Mike

 

[ShaneDevenshire]

Hi,

The following formula does the trick:

=AVERAGEIF(A$1:A$1000,J1,C$1:C$1000)

Where A1:A1000 contains the product names, C1:C1000 contains the values you
want averaged, J1 contains the product name you want looked up in A1:A1000.

The above formula is probably the best way to do this but you need to be
using 2007, however you didn't say what version you were using, so....

Thanks,
Shane Devenshire