Managing Errors in SUMPRODUCT Formulas

[Tikey]

I am using Excel 2019

I have a large sheet of data containing dates.

On another sheet I wish to find the earliest date for each year, which I do using the following formula:

=AGGREGATE(15,6,Records!$F$2:$F$40000/((YEAR(Records!$F$2:$F$40000)=2005)*(Records!$I$2:$K$40000>0)),1) This example for the year 2005

I then wish to use the dates returned to find the average of these dates, but only the day and month, I want the average for all the years and just the last 10 years, I use the following formulas:

=SUMPRODUCT(DATE(0,MONTH(Mydates),DAY(Mydates)))/COUNT(Mydates) This for all years
=SUMPRODUCT(DATE(0,MONTH(Mydates),DAY(Mydates))*(Mydates>DATE(YEAR(TODAY())-11,12,31))/SUMPRODUCT(--(Mydates>DATE(YEAR(TODAY())-11,12,31)))) This for the last 10 years, “Mydates” is a named range of cells.

All formulas work ok if there is a full set of data.

But, the first formula returns a #NUM! error if there is no data for one of the years. This error then causes a #NUM! error in the second two formulas. I have tried adding IFERROR to the first formula to return a blank cell, but this then causes a #VALUE! error in the second two formulas.

Can anyone offer a solution ?

 

[jasonb75]

See if this works. You will need to array confirm both formulas with Ctrl Shift Enter if you are still using excel 2019 (or an older version).

=SUMPRODUCT(IFERROR(DATE(0,MONTH(Mydates),DAY(Mydates)),0))/COUNT(Mydates)

=SUMPRODUCT(IFERROR(DATE(0,MONTH(Mydates),DAY(Mydates))*(Mydates>DATE(YEAR(TODAY())-11,12,31)),0)/SUMPRODUCT(IFERROR(--((Mydates+0)>DATE(YEAR(TODAY())-11,12,31)),0)))

 

[Tikey]

Thanks very much for your reply. They both work and ignore the error in the first formula. The answers though are making me question the original formula, I will have to do some further checks on what answers I would expect.

Thanks again for your help.

 

[jasonb75]

The way you had set up the second formula did seem a little unusual, see if this is closer to what you would expect.

=SUMPRODUCT(IFERROR(DATE(0,MONTH(Mydates),DAY(Mydates))*(Mydates>DATE(YEAR(TODAY())-11,12,31)),0))/SUMPRODUCT(IFERROR(--((Mydates+0)>DATE(YEAR(TODAY())-11,12,31)),0))

 

[Tikey]

That gives the same answer of 10th Aug as the original formula. But with the following dates, the majority being in May, off the top of my head I was expecting around a June date. I will investigate further.

10/08/2012

11/08/2012

08/06/2014

10/06/2015

15/08/2016

05/05/2017

23/05/2018

15/05/2019

08/05/2020

27/05/2021

 

[Tikey]

I've just quickly tried it again using those 10 dates and it gave an answer of 16th June, which seems where you would expect. I was using the following for the 'Mydates' range =OFFSET(Statistics!$C$8,,,COUNT(Statistics!$C$8:$C$38),) to establish the last 10 years of dates, but it looks like I've got that wrong.

 

[jasonb75]

Using the formula from post 4 with the list of dates that you provided, I get a result of 16 June.

Taking a wild guess on what the problem might be, one thing to note is that the offset formula that you have used for the named range will only work correctly if the dates are in consecutive cells. A blank in the middle will mess it up.

 

[Tikey]

Thanks, I will look further tomorrow. I've tried that many different options today that I am tying myself in knots. My last post was nonsense, as the 10 years of dates is defined in the formula in post 4.