Conditional sumif?

[ewan7279]

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

 

[JLatham]

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:C13>0),(C9:C13))/SUMPRODUCT(--(A9:A13>0),(A9:A13))

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

 

[RagDyeR]

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

HTH,

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

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

 

[Shane Devenshire]

Hi,

The basic weighted average formula would be

=SUMPRODUCT(A2:A9*B2:B9)/SUM(B2:B9)

You can make this condtional with:

=SUMPRODUCT(--(A2:A9<0),A2:A9*B2:B9)/SUMPRODUCT(B2:B9*(A2:A9<0))

 

[ewan7279]

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

 

[ewan7279]

Great! So simple...thanks. Final formula is:

=IF(L1>0,SUMIF(J3:J320,">0")/SUMIF(H3:H320,">0"),SUMIF(J3:J320,"<0")/SUMIF(H3:H320,"<0"))