I am keeping Phenology records using an excel spreadsheet.
I have columns for successive northern hemisphere winters, 12018/2019, 2019/2020, 2020/21 etc Each cell is formatted as Custom Date: Type DD/MMM
I have two rows for each of 70+ species and columns for each year with the end column for calculating the average date of an event and the average difference in days
My formulae uses an array in the formula field {=AVERAGE(DATE(,MONTH(F14:I14),DAY(F14:I14)))} But this produces some strange results, for example 2nd May, for an average between 23rd December and 8th January
Because this is about the first time an effect is noticed, in any spring period, it can be between late November in one year and late January in the following year, so crossing the year boundary. I suspect that the average is looking within years rather than between year
Any suggestions for how I can correct this formulae would be much appreciated.
View attachment Spring watch Croatia average calc.xlsx
Thanks for reading
I have columns for successive northern hemisphere winters, 12018/2019, 2019/2020, 2020/21 etc Each cell is formatted as Custom Date: Type DD/MMM
I have two rows for each of 70+ species and columns for each year with the end column for calculating the average date of an event and the average difference in days
My formulae uses an array in the formula field {=AVERAGE(DATE(,MONTH(F14:I14),DAY(F14:I14)))} But this produces some strange results, for example 2nd May, for an average between 23rd December and 8th January
Because this is about the first time an effect is noticed, in any spring period, it can be between late November in one year and late January in the following year, so crossing the year boundary. I suspect that the average is looking within years rather than between year
Any suggestions for how I can correct this formulae would be much appreciated.
View attachment Spring watch Croatia average calc.xlsx
Thanks for reading