# Monthly average share price?

#### rcp

##### New Member
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…

### Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

##### Well-known Member
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
ABCDEFG
1DateShare PriceMonthAverage
210-Sep-21490August 21
320-Sep-21490September 21490.000
430-Sep-21490October 2172.000
510-Oct-2166November 21133.333
620-Oct-2150December 216.000
730-Oct-21100January 22
809-Nov-21100
919-Nov-21100
1029-Nov-21200
1109-Dec-215
1219-Dec-217
Sheet1
Cell Formulas
RangeFormula
G2:G7G2=IFERROR(AVERAGEIFS(Share_Price,Date,">="&[@Month],Date,"<="&EOMONTH([@Month],0)),"")
F3:F7F3=EOMONTH(F2,0)+1
Named Ranges
NameRefers ToCells
Date=Sheet1!\$A\$2:\$A\$12G2:G7
Month=Sheet1!\$F\$2:\$F\$7G2, F3
Share_Price=Sheet1!\$B\$2:\$B\$12G2:G7

#### rcp

##### New Member
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
ABCDEFG
1DateShare PriceMonthAverage
210-Sep-21490August 21
320-Sep-21490September 21490.000
430-Sep-21490October 2172.000
510-Oct-2166November 21133.333
620-Oct-2150December 216.000
730-Oct-21100January 22
809-Nov-21100
919-Nov-21100
1029-Nov-21200
1109-Dec-215
1219-Dec-217
Sheet1
Cell Formulas
RangeFormula
G2:G7G2=IFERROR(AVERAGEIFS(Share_Price,Date,">="&[@Month],Date,"<="&EOMONTH([@Month],0)),"")
F3:F7F3=EOMONTH(F2,0)+1
Named Ranges
NameRefers ToCells
Date=Sheet1!\$A\$2:\$A\$12G2:G7
Month=Sheet1!\$F\$2:\$F\$7G2, F3
Share_Price=Sheet1!\$B\$2:\$B\$12G2: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)

##### Well-known Member
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
ABCDEFG
1DateShare PriceMonthAverage
210-Sep-21490August 21#DIV/0!
320-Sep-21490September 21490.000
430-Sep-21490October 2172.000
510-Oct-2166November 21133.333
620-Oct-2150December 216.000
730-Oct-21100January 22#DIV/0!
809-Nov-21100
919-Nov-21100
1029-Nov-21200
1109-Dec-215
1219-Dec-217
2nd
Cell Formulas
RangeFormula
G2:G7G2=AVERAGEIFS(Share_Price,Date,">="&[@Month],Date,"<="&EOMONTH([@Month],0))
F3:F7F3=EOMONTH(F2,0)+1
Named Ranges
NameRefers ToCells
'2nd'!Date='2nd'!\$A\$2:\$A\$12G2:G7
'2nd'!Month='2nd'!\$F\$2:\$F\$7G2, F3
'2nd'!Share_Price='2nd'!\$B\$2:\$B\$12G2:G7

#### rcp

##### New Member
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!

Replies
4
Views
276
Replies
1
Views
427
Replies
2
Views
53
Replies
10
Views
92
Replies
6
Views
197

1,191,171
Messages
5,985,067
Members
439,938
Latest member
MAlhash

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back