Volume weighted price, bottom/top percentile

[jcooooper]

I am trying to find the volume weighted average price of the bottom 25% of the overall quantity.

In this example, there are 240 share lots for sale, I need just the average price of the bottom 25% of the overall quantity.

Is there any easy way to do calculate this? I've included a picture for reference and the data in the table below

	Price	Quantity		bottom 25%	Quantity	Price	Total	(Cumulative Quantity)
	298.52​	5​		=first 60 shares	5​	298.52​	1492.6​	5​
	300​	30​			30​	300​	9000​	35​
	300​	20​			20​	300​	6000​	55​
	300​	16​			5​	300​	1500​	60​
	300​	5​		total	60​		17992.6​
	300​	5​
	302.91​	30​					Volume weighted price =
	302.91​	8​					Total / shares
	302.91​	7​					17992.6/60
	302.91​	7​				Result:	299.8766667
	302.91​	6​
	302.91​	5​
	302.91​	5​
	302.91​	3​
	305​	30​
	305​	12​
	305​	10​
	307​	36​
total		240​

 

[severynm]

I added your Price and Quantity values into a table, which is unceremonously called Table3. I added two simple columns: one for the Cumulative Quanity, and one for the Index (or row in the table - we'll need this later on). The last column First60Shares does most of the work here. The main body of that function is

=IF([@[Cumulative Quantity]]>SUM([Quantity])/4,SUM([Quantity])/4-INDEX([Cumulative Quantity],[@Index]-1),[@Quantity])

> If the Cumulative Quantity is > 25% of the total, return 25% of the total - the Cumulative Quantity of the previous row (essentially calculating the portion of the current row that is to be carried over to equal 25% of the total), otherwise just return the regular quantity).

Note that you can do this without the table, but in my opinion the formulas become much more easier to read and you do not have to worry about constantly extending the formulas if rows are added/removed - they always refer to every row in the table. You could also clean up a little if you want by adding some extra logic to First60Shares to show blank if its value is < 0, but I didn't want to complicate things further for now.

MrExcel.xlsm
	A	B	C	D	E
1	Price	Quantity	Cumulative Quantity	Index	First60Shares
2	298.52	5	5	1	5
3	300	20	25	2	20
4	300	30	55	3	30
5	300	16	71	4	5
6	300	5	76	5	-11
7	300	5	81	6	-16
8	302.91	30	111	7	-21
9	302.91	8	119	8	-51
10	302.91	7	126	9	-59
11	302.91	7	133	10	-66
12	302.91	6	139	11	-73
13	302.91	5	144	12	-79
14	302.91	5	149	13	-84
15	302.91	3	152	14	-89
16	305	30	182	15	-92
17	305	12	194	16	-122
18	305	10	204	17	-134
19	307	36	240	18	-144
SheetName

Cell Formulas
Range	Formula
C2:C19	C2	=SUM($B$2:B2)
D2:D19	D2	=ROW([@Quantity])-1
E2:E19	E2	=IF([@[Cumulative Quantity]]>SUM([Quantity])/4,SUM([Quantity])/4-INDEX([Cumulative Quantity],[@Index]-1),[@Quantity])

You can then finish everything out with one formula somewhere else in your worksheet: Sumproduct the Price and First60Shares columns, but only the rows where First60Share is > 0.

=SUMPRODUCT(IF(Table3[First60Shares]>0,Table3[Price],0),Table3[First60Shares])

This gives us 17992.6 as expected.

 

[jcooooper]

Exactly what I was after, thank you very much