Monthly average share price?

rcp

New Member
Joined
Dec 3, 2020
Messages
14
Office Version
  1. 2019
Platform
  1. MacOS
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.

Picture 2.png


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)

Picture 1.png


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
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
2,549
Office Version
  1. 2016
Platform
  1. Windows
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
Joined
Dec 3, 2020
Messages
14
Office Version
  1. 2019
Platform
  1. MacOS
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)

Picture 3.png
 

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
2,549
Office Version
  1. 2016
Platform
  1. Windows
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
Joined
Dec 3, 2020
Messages
14
Office Version
  1. 2019
Platform
  1. MacOS
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!
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,255
Messages
5,836,274
Members
430,414
Latest member
ayla

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top