Conditionals containing variables
 

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.

Conditionals containing variables
 

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.

"ChevyChem" wrote:

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.

Conditionals containing variables
 

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



"ChevyChem" wrote:

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.

Conditionals containing variables
 

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...

"ChevyChem" wrote:

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.

Conditionals containing variables
 

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
--
Always provide your feedback so that others know whether the solution worked
or problem still persists ...


"ChevyChem" wrote:

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.

Conditionals containing variables
 

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

"ChevyChem" wrote:

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.

Conditionals containing variables
 

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.
--
David Biddulph

"Mike H" wrote in message
...
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



"ChevyChem" wrote:

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.

Conditionals containing variables
 

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

Mike

"David Biddulph" wrote:

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.
--
David Biddulph

"Mike H" wrote in message
...
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



"ChevyChem" wrote:

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.

Conditionals containing variables
 

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


"ChevyChem" wrote:

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.