Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional sumif?
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... |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional sumif?
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... |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional sumif?
=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... |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional sumif?
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)) -- If this helps, please click the Yes button. Cheers, Shane Devenshire "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... |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional sumif?
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... |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional sumif?
Great! So simple...thanks. Final formula is:
=IF(L10,SUMIF(J3:J320,"0")/SUMIF(H3:H320,"0"),SUMIF(J3:J320,"<0")/SUMIF(H3:H320,"<0")) "RagDyeR" wrote: =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... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional formatting w/ a sumif | Excel Discussion (Misc queries) | |||
Conditional sumif | Excel Discussion (Misc queries) | |||
conditional fraction - sumif? | Excel Worksheet Functions | |||
Conditional SUMIF??? | Excel Worksheet Functions | |||
Conditional SUMIF | Excel Worksheet Functions |