Monthly average share price?

[Legacy 469449]

Hi,

I have a spreadsheet with daily share prices on (automatically pulled in). See pic below (there are obviously more data entires, I've just hidden rows to make the pic easier to read). Let's call this Table 1.

This is just a continually running entry, I just extend the table by 100 or so rows every time I need it to carry on (and hide rows via a filter based on length of time).

Now at the moment I have a separate table (Table 2) that I enter the average share price in for each month. Picture below (values removed)

I was wondering if someone could help me work out a formula that would pull in the average share price for each month, and that would run automatically?

I'm guessing it would need to use something along the lines of if Table1 A Month / Table 1 A Year = Table 2 A Month / Table 2 A Year, then some how pull the average share price for the 30 or so shares that had the same Month and Year…

Thanks in advance

 

[Toadstool]

Hi RCP,

I'll assume the monthly table should also show the year in case you go over 12 months. If the Month table just has 1st of each month/year as a date you can use that to average all price dates for a month.

RCP.xlsx
	A	B	C	D	E	F	G
1	Date	Share Price				Month	Average
2	10-Sep-21	490				August 21
3	20-Sep-21	490				September 21	490.000
4	30-Sep-21	490				October 21	72.000
5	10-Oct-21	66				November 21	133.333
6	20-Oct-21	50				December 21	6.000
7	30-Oct-21	100				January 22
8	09-Nov-21	100
9	19-Nov-21	100
10	29-Nov-21	200
11	09-Dec-21	5
12	19-Dec-21	7
Sheet1

Cell Formulas
Range	Formula
G2:G7	G2	=IFERROR(AVERAGEIFS(Share_Price,Date,">="&[@Month],Date,"<="&EOMONTH([@Month],0)),"")
F3:F7	F3	=EOMONTH(F2,0)+1

Named Ranges
Name	Refers To	Cells
Date	=Sheet1!$A$2:$A$12	G2:G7
Month	=Sheet1!$F$2:$F$7	G2, F3
Share_Price	=Sheet1!$B$2:$B$12	G2:G7

 

[Legacy 469449]

Hi RCP,

I'll assume the monthly table should also show the year in case you go over 12 months. If the Month table just has 1st of each month/year as a date you can use that to average all price dates for a month.

RCP.xlsx
	A	B	C	D	E	F	G
1	Date	Share Price				Month	Average
2	10-Sep-21	490				August 21
3	20-Sep-21	490				September 21	490.000
4	30-Sep-21	490				October 21	72.000
5	10-Oct-21	66				November 21	133.333
6	20-Oct-21	50				December 21	6.000
7	30-Oct-21	100				January 22
8	09-Nov-21	100
9	19-Nov-21	100
10	29-Nov-21	200
11	09-Dec-21	5
12	19-Dec-21	7
Sheet1

Cell Formulas
Range	Formula
G2:G7	G2	=IFERROR(AVERAGEIFS(Share_Price,Date,">="&[@Month],Date,"<="&EOMONTH([@Month],0)),"")
F3:F7	F3	=EOMONTH(F2,0)+1

Named Ranges
Name	Refers To	Cells
Date	=Sheet1!$A$2:$A$12	G2:G7
Month	=Sheet1!$F$2:$F$7	G2, F3
Share_Price	=Sheet1!$B$2:$B$12	G2:G7

That's wonderful – thanks so much. Maddeningly it doesn't seem to be working for me at the moment.

(Confession time, I'm actually using Apple Numbers, but this forum has been incredibly helpful before. I normally manage to make the necessary conversions but might be failing).

Numbers doesn't used named cells/ranges, but you can title tables to give the same effect. Fully understand if you can't help with this, but can you think of anything else that might need tweaking? (PS yes, also shows the year (just have it hidden). and am using EOMONTH formula)

 

[Toadstool]

I'm afraid I don't know anything about Apple Numbers.

The place I'd start is remove the IFERROR to see what actual error prevents it working. Here my data just give a couple of #DIV/0

RCP.xlsx
	A	B	C	D	E	F	G
1	Date	Share Price				Month	Average
2	10-Sep-21	490				August 21	#DIV/0!
3	20-Sep-21	490				September 21	490.000
4	30-Sep-21	490				October 21	72.000
5	10-Oct-21	66				November 21	133.333
6	20-Oct-21	50				December 21	6.000
7	30-Oct-21	100				January 22	#DIV/0!
8	09-Nov-21	100
9	19-Nov-21	100
10	29-Nov-21	200
11	09-Dec-21	5
12	19-Dec-21	7
2nd

Cell Formulas
Range	Formula
G2:G7	G2	=AVERAGEIFS(Share_Price,Date,">="&[@Month],Date,"<="&EOMONTH([@Month],0))
F3:F7	F3	=EOMONTH(F2,0)+1

Named Ranges
Name	Refers To	Cells
'2nd'!Date	='2nd'!$A$2:$A$12	G2:G7
'2nd'!Month	='2nd'!$F$2:$F$7	G2, F3
'2nd'!Share_Price	='2nd'!$B$2:$B$12	G2:G7

 

[Legacy 469449]

Think I figured it out – square brackets and ampersand what throwing it off (must not be compatible). It's now no longer showing up an error, but just returns a blank cell, which is odd. Oh well, thanks for your help, you've given me plenty to work with!