Average of the Product of Two Columns

  • Thread starter Thread starter DJo
  • Start date Start date
D

DJo

Hi,

I have a spreadsheet showing the name of a product followed by how much it will sell for in each location. In another column I have a flag, either 0 or 1 to indicate that the product can be purchased from that location. The buy and sell prices are equal (no profit).

I want to find out what the average sell price for each product is. Here is how my spreadsheet is layed out:


-----------
Product A Location Sells Product B Location Sells
Location 1 200 0 150 1
Location 2 100 1 300 0
Location 3 300 1 150 0

0 = No
1 = Yes
-------------------


This is how I propose to find the average sell price:

Product Price multiplied by Sell Flag (for location 1, 2 and 3)
Add them all up
Divide by the sum of Location Sells column

200 * 0 = 0
100 * 1 = 100
300 * 1 = 300
--------------------
Sum of = 400

400 / (0 + 1 + 1) = 200
So the average sell price is 200.


Now is there a way to automate this? I don't want to create ANOTHER set of columns (there are a lot of products)

Thanking you in advance.

Iain

P.S. I have no idea how to use Access :(
 
DJo

Try this:
=(SUMPRODUCT((E11:E13*F11:F13)))/SUM(F11:F13)

Andy.
Hi,

I have a spreadsheet showing the name of a product followed by how much it will sell for in each location. In another column I have a flag, either 0 or 1 to indicate that the product can be purchased from that location. The buy and sell prices are equal (no profit).

I want to find out what the average sell price for each product is. Here is how my spreadsheet is layed out:


-----------
Product A Location Sells Product B Location Sells
Location 1 200 0 150 1
Location 2 100 1 300 0
Location 3 300 1 150 0

0 = No
1 = Yes
-------------------


This is how I propose to find the average sell price:

Product Price multiplied by Sell Flag (for location 1, 2 and 3)
Add them all up
Divide by the sum of Location Sells column

200 * 0 = 0
100 * 1 = 100
300 * 1 = 300
--------------------
Sum of = 400

400 / (0 + 1 + 1) = 200
So the average sell price is 200.


Now is there a way to automate this? I don't want to create ANOTHER set of columns (there are a lot of products)

Thanking you in advance.

Iain

P.S. I have no idea how to use Access :(
 
To get an answer in one cell, use an array formula:
=AVERAGE(IF($D$1:$D$3=1,$C$1:$C$3))

This works fine. Press CTRL+SHIFT+ENTER when you enter
the formula to make sure it reads it as an array.

In the formula, column D is referencing the cells which
say if the location sells it or not, Column C the selling
price. You would need to make sure that the number of
rows you have is reflected (not just 3) in both ranges.

Allen



-----Original Message-----
Hi,

I have a spreadsheet showing the name of a product
Click to expand...
followed by how much it will sell for in each location. In
another column I have a flag, either 0 or 1 to indicate
that the product can be purchased from that location. The
buy and sell prices are equal (no profit).
I want to find out what the average sell price for each
Click to expand...
product is. Here is how my spreadsheet is layed out:Product B Location Sells
Location 1 200 0 150 1
Location 2 100 1 300 0
Location 3 300 1 150 0

0 = No
1 = Yes
-------------------


This is how I propose to find the average sell price:

Product Price multiplied by Sell Flag (for location 1, 2 and 3)
Add them all up
Divide by the sum of Location Sells column

200 * 0 = 0
100 * 1 = 100
300 * 1 = 300
--------------------
Sum of = 400

400 / (0 + 1 + 1) = 200
So the average sell price is 200.


Now is there a way to automate this? I don't want to
Click to expand...
create ANOTHER set of columns (there are a lot of products)
 
Try something like this:

=SUMPRODUCT((C2:C4=1)*B2:B4)

-Dave
Hi,

I have a spreadsheet showing the name of a product followed by how much it
Click to expand...
will sell for in each location. In another column I have a flag, either 0 or
1 to indicate >that the product can be purchased from that location. The buy
and sell prices are equal (no profit).
I want to find out what the average sell price for each product is. Here is
Click to expand...
how my spreadsheet is layed out:
-----------
Product A Location Sells Product B Location Sells
Location 1 200 0 150 1
Location 2 100 1 300 0
Location 3 300 1 150 0

0 = No
1 = Yes
-------------------


This is how I propose to find the average sell price:

Product Price multiplied by Sell Flag (for location 1, 2 and 3)
Add them all up
Divide by the sum of Location Sells column

200 * 0 = 0
100 * 1 = 100
300 * 1 = 300
--------------------
Sum of = 400

400 / (0 + 1 + 1) = 200
So the average sell price is 200.


Now is there a way to automate this? I don't want to create ANOTHER set of
Click to expand...
columns (there are a lot of products)
 
Back
Top