Thanks for your reply.

Your answer works in the example I gave you, but I might not have expanded
my question far enough - can the formula be written so that if the total vol
is negative, all positive figures are excluded? I have tried the formula on
the data below, and I get a #div/0! error (would expect to have 27.82 as an
answer):

Vol Price Price * Vol
-35 28 -980
-97 27 -2619
-40 27 -1080
-89 29 -2581

Total vol: -261


"JLatham" wrote:

I wrote the formula a bit oddly, assumes that your first vol/price pair is on
row 9 and the -50 volume entry is in row 13, adjust accordingly:

=SUMPRODUCT(--(C9:C130),(C9:C13))/SUMPRODUCT(--(A9:A130),(A9:A13))

I think that'll do the trick for you without resorting to array formulas.


"ewan7279" wrote:

Hi,

I am looking for a formula that will allow me to calculate a weighted
average price. The data is as follows:

Vol Price Price x Vol
27 50 1350
30 45 1350
15 60 900
20 40 800

Total Vol: 92
Weighted average: 47.83 (sum(vol * price) / total vol)

However, occasionally I have negative volumes (i.e. buy or sell) and want
the formula to calculate only the positive/negative weighted prices dependent
on the total vol (the example above is positive with a total vol of 92).
Using the data above, if there was a negative volume included in the data, it
would look like:

Vol Price Price x Vol
27 50 1350
30 45 1350
15 60 900
20 40 800
-50 20 -1000

Total Vol: 42
Weighted average: 80.95 (sum(vol * price) / total vol)

...but I want the formula to ignore the -50 volume. I want to avoid using
helper columns etc, and was thinking there may be a way of doing something
along the lines of: if(total vol < 0 , (sum -ve prices * vols)/total -ve
vols, (sum +ve prices * vols)/total +ve vols), but can't work out how to do
it. Any help appreciated...