Average date ( day & month only ) for the last number of years using a dynamic range

Tikey

Board Regular
Joined
Jan 30, 2014
Messages
148
Office Version
  1. 2021
Platform
  1. Windows
I use Excel 2019 from the Office Pro package.

I currently use the following formula to average a series of dates for just the day and month:

=ROUNDUP(AVERAGE(IF(ISNUMBER(C8:C38),DATE(1900,MONTH(C8:C38),DAY(C8:C38)))),0) - entered using Ctrl, Shift, Enter

This works fine, but I would like to take this a step further and adapt this to use in a dynamic range to cover the last 10 years.

I have tried editing this using the OFFSET function without any success, I am struggling with this, can anyone offer any suggestions.

Thanks in anticipation.
 
Edate is the same day, but x months earlier or later and i used -12*10 = 120 months earlier.
if you want 1 jan 2012, then you use ther formula
Excel Formula:
=DATE(YEAR(TODAY())-10,1,1)
 
Upvote 0

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
Problem sorted, thank you very much for all the time and effort you have put into this, much appreciated.
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,938
Members
448,534
Latest member
benefuexx

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
Back
Top