Time weighted number of shares

you used an average over the month to calculate the average

so assuming this is OK for your need

AVERAGEIFS() - if you have the correct version of excel
Otherwise as you have 2016 in your profile

SUMIFS()/COUNTIFS()
should work OK in 2007 + versions, i think

see attached

Not sure why you are showing the average in Jan date , when the month data is Feb, BUT we can adjust once i understand that

Originally Posted by kohno71

So lets just focus on this case where we have a) the list of transactions (aka buy/sell of shares) and b) the date at which I want to see the average weighted shares number at.

Originally Posted by joeu2004

Your attachment presents the data in several forms. It is not clear to me what form we should use for a solution.

Oh, I overlooked that comment in your second posting.

But still, #b is unclear. In particular, ``the date at which I want to see the average weighted shares`` measured from when?

Assuming that you want monthly weighted averages, I think the tables that I presented previously are close to the way that you should design the data in order to make it easy(er) to calculate the weighted averages.

@kohno71.... In general, a weighted average is calculated by SUM(d[i]*w[i], i=1,...,n) / SUM(w[i], i=1,...,n), where d[i] and w[i] are the data and corresponding weighting factor.

Your attachment presents the data in several forms. It is not clear to me what form we should use for a solution.

Suppose the data is in the following form. See the "Summary" table.

Then the monthly and total weighted averages can be calculated as follows. See the "Weighted Average" table.

Note that the total average is not a simple average of the monthly averages (C29).

Instead, it is a weighted average, either of the monthly averages (C27) or the entire summary data (C28).

(Sorry about the presentation. I am working-around limitations or defects in this forum.)

Formulas:
D11: =B11-A11+1
C12: =C11 + IFERROR(VLOOKUP(A12, $B$3:$C$7, 2, 0), 0)
C23: =SUMPRODUCT((B23<=$B$11:$B$19)*($B$11:$B$19<=EOMONTH(B23,0)), $D$11:$D$19, $C$11:$C$19)
      / SUMPRODUCT((B23<=$B$11:$B$19)*($B$11:$B$19<=EOMONTH(B23,0)), $D$11:$D$19)
D23: =DAY(EOMONTH(B23,0))
C27: =SUMPRODUCT(D23:D26, C23:C26) / SUM(D23:D26)
C28: =SUMPRODUCT(D11:D19, C11:C19) / SUM(D11:D19)
C29: =AVERAGE(C23:C26)

Note that B23:B26 contains first-of-the-month dates; e.g, 2/1/2020 for Feb'20.

	A	B	C	D	E
1		Transactions
2		Date	Shares
3		2/13/2020	100
4		2/23/2020	100
5		3/12/2020	20
6		4/4/2020	300
7		5/6/2020	-40
8
9			Summary
10			Shares	#Days
11	2/1/2020	2/12/2020	0	12
12	2/13/2020	2/22/2020	100	10
13	2/23/2020	2/29/2020	200	7
14	3/1/2020	3/11/2020	200	11
15	3/12/2020	3/31/2020	220	20
16	4/1/2020	4/3/2020	220	3
17	4/4/2020	4/30/2020	520	27
18	5/1/2020	5/5/2020	520	5
19	5/6/2020	5/31/2020	480	26
20
21			Weighted Average
22			Shares	#Days
23		Feb'20	82.7586	29
24		Mar'20	212.9032	31
25		Apr'20	490.0000	30
26		May'20	486.4516	31
27		TOTAL	320.4959	121	correct
28			320.4959		correct
29			318.0284		wrong!