=SUMIF(C9:C13,"0")/SUMIF(A9:A13,"0")
--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===

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