Hey guys,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com
fficeffice" /><o
></o>
<o> </o>
I have a quick problem regarding a VWAP.<o></o>
<o> </o>
An example of my spreadsheet consists of the A column, with the times, 7am-10pm, in fifteen min segments. Column B would consist of the price, corresponding to the time in column A and column C has the quantity available at that price.<o></o>
<o> </o>
But in addition to B and C, there are several more prices and (for each of those prices) sizes, expanding right to D-E, F-G, H-I, L-M etc.. pairs<o></o>
<o> </o>
What I’d like to do is get group sizes of 2,000,000 (in size terms).<o></o>
<o> </o>
So for example;<o></o>
<o> </o>
A B C D E<o></o>
At 7am -> 50 -> 750,000 -> 52 -> 5,000,000 etc…..<o></o>
<o> </o>
What I would like is a weighted average of only the first 2 million.<o></o>
<o> </o>
So to take all of the 750,000 at 50, and only 1,250,000 at 52. <o></o>
<o> </o>
It gets more complicated if C has 200,000, E has 100,000, G 3000, I 6,000,000<o></o>
<o> </o>
So you take all of C, E, G and only 1,607,000 of I.<o></o>
<o> </o>
And then its multiplying these numbers with the associated prices, and dividing by 2,000,000.<o></o>
<o> </o>
(There are more entries than just the one day (thousands), which I will use a pivot table, or otherwise I could analyse each individually, which would have been monotonous but possible, hence why I need your help)<o></o>
<o> </o>
Thanks Very very much,<o></o>
Kamr. <o></o>
fficeffice" /><o></o><o
> </o>I have a quick problem regarding a VWAP.<o
></o><o
> </o>An example of my spreadsheet consists of the A column, with the times, 7am-10pm, in fifteen min segments. Column B would consist of the price, corresponding to the time in column A and column C has the quantity available at that price.<o
></o><o
> </o>But in addition to B and C, there are several more prices and (for each of those prices) sizes, expanding right to D-E, F-G, H-I, L-M etc.. pairs<o
></o><o
> </o>What I’d like to do is get group sizes of 2,000,000 (in size terms).<o
></o><o
> </o>So for example;<o
></o><o
> </o>A B C D E<o
></o>At 7am -> 50 -> 750,000 -> 52 -> 5,000,000 etc…..<o
></o><o
> </o>What I would like is a weighted average of only the first 2 million.<o
></o><o
> </o>So to take all of the 750,000 at 50, and only 1,250,000 at 52. <o
></o><o
> </o>It gets more complicated if C has 200,000, E has 100,000, G 3000, I 6,000,000<o
></o><o
> </o>So you take all of C, E, G and only 1,607,000 of I.<o
></o><o
> </o>And then its multiplying these numbers with the associated prices, and dividing by 2,000,000.<o
></o><o
> </o>(There are more entries than just the one day (thousands), which I will use a pivot table, or otherwise I could analyse each individually, which would have been monotonous but possible, hence why I need your help)<o
></o><o
> </o>Thanks Very very much,<o
></o>Kamr. <o
></o>
