Aggregate Function

[Tikey]

I am using Excel 2019, not 365

I have a list of dates which cover several years in the format 31/01/2020, some of the date cells contain errors ie #NUM!. I am trying to find the average of the dates, but excluding the year ( day & month only ) and I also do not wish to create any new cells when doing this.

I have been trying to do this using the aggregate function, but cannot find a way of excluding the year.

Maybe there is a better function, can anyone help.

Thanks

 

[jasonb75]

I'm not sure that averaging dates is ever going to work as expected but you could try array confirming this, adjust the range as necessary.

=AVERAGE(IFERROR(MOD(A1:A100,365),""))

For reference, aggregate doesn't accept arrays / criteria for average, see post 7 in the thread linked below

Use of AGGREGATE

Hi guys, sorry if my question is dumb but I'm not able to found a solution. I would like to use AGGREGATE function on rows to carry out separate averages data. on below example i would like to have: on D3 the average of Range G:O including only the cells where row (2) value is "h" on E3 the...

www.mrexcel.com

 

[Tikey]

Thanks for the suggestion, unfortunately this doesn't seem to work.

 

[jasonb75]

Doesn't work could mean that it still errors (it doesn't when I try it) or that it gives an incorrect result, the latter of which is open to interpretation.

As I already said, averaging dates is never going to work as expected. You could ask 100 people to average a list of dates and most likely each would come up with a different answer.

Without a handful of dates and the expected result of the average it is the best I can do.

 

[Tikey]

I take your point and thinking about it a bit more it is not easy particularly when different months are involved. I have showed a couple of examples below and what answer I would expect ( want )

28/04/1992

30/04/1993

29/04/1994

14/04/1995

29/04/1996

26/04

15/05/2020
15/06/2020

31/05

Is it possible to show what you have tried and the answers you achieved ?

 

[jasonb75]

Is it possible to show what you have tried and the answers you achieved ?

I didn't try anything as such, I just entered the formula that I suggested against a list of dates to see if it gave a result or an error.
Testing the formula with your listed dates and some others, I found a number of flaws in the formula and the theory that I used. Starting again with a different formula, this one appears to work with a quick test. Because the year is being omitted the formula will calculate every year as a leap year so you might get a 1 day variance between actual and expected results if your earliest date is on or before Feb 28 and your latest date is on or after Mar 01.

Book2
	A	B
1	28/04/1992	15/05/2020
2	30/04/1993	15/06/2020
3	29/04/1994
4	14/04/1995
5	29/04/1996
6
7
8
9
10
11
12	26/04/1900	31/05/1900
Sheet13

Cell Formulas
Range	Formula
A12:B12	A12	=ROUNDUP(AVERAGE(IF(A1:A10="","",DATE(1900,MONTH(A1:A10),DAY(A1:A10)))),0)
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Tikey]

Jason

Thanks very much, a one day variance possibly isn't too much of a problem. The formula doesn't appear to like error cells, fortunately they are always at the end of the range so I will just have to keep changing the range of cells, or is there a way of dealing with them ?

 

[jasonb75]

Sorry, that was my fault, when I changed to a different formula I forgot about the errors. This one will ignore errors, still array confirmed.

=ROUNDUP(AVERAGE(IF(ISNUMBER(A1:A10),DATE(1900,MONTH(A1:A10),DAY(A1:A10)))),0)

Regarding the one day difference, taking leap day into consideration, what results would you expect with each of the following pairs of dates?

Book2
	D
1	15/02/2019
2	15/03/2019
3
4	15/02/2020
5	15/03/2020
6
7	15/02/2019
8	15/03/2020
9
10	15/02/2020
11	15/03/2019
Sheet13

 

[Tikey]

Jason

Great, thanks very much for all your help.

 

[jasonb75]

You're welcome

In case you missed it, I edited my last reply to include some paired dates where the leap day calculation could give the incorrect result. The formula will return 01/03 for all of these pairs would you expect any of them to be different? Perhaps try moving the dates back by 1 day so that the result should be 28 / 29 Feb and compare the actual to the expected there as well.