Average of the Product of Two Columns

[Allen]

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

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 outroduct 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)