Posts by NormanW

    Thank you Roy


    Yes, exactly as you say, that box is enabled.


    As this is a setting, I would have expected the feature to work across all spreadsheets.


    I have created a new spreadsheet this afternoon, and the "extend data range.." in options is still enabled, but the calculations do not auto advance.


    Is there something else which turns the feature on in an individual spreadsheet/worksheet please?


    Norman

    With the excessive heat in the northern hemisphere this summer, I had set up an additional spreadsheet to gather data from my weather station.


    The WMO standard for daily average temperatures is 4 measurements at 01:00, 07:00, 13:00 and 19:00 plus the daily max and min temps divided by 6 to give you an average. (In 2021 we are three to 6 degrees above the rolling average from previous years) To this I added the high and low relative humidity, the average and the difference between the two. All is on the attached spreadsheet, short version.

    Hot weather extra 2.xlsx

    Calculations are being done in columns H, I, J, M and N. This is all in an Excel 2007 spreadsheet.


    What I noticed was that I didn't have to copy the calculations down to the next blank cell in the column.


    Looking at Row 27, the first temperature is in cell B27. As soon as I populate C, D, E, F and G27, then the spreadsheet calculated H27, I27 and J27 with no input from me?


    I have looked at the cell formatting but I can't see what it is that I have enabled to get this to happen. It's a useful feature that I have somehow got working.


    Please can someone tell me what I did because I would like to use it on other spreadsheets.


    Thank for reading


    Norman

    [xpost][/xpost]

    also posted at: https://www.excelguru.ca/forum…dates-across-the-year-end

    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 Date: Type *14/03/2001


    I have a row for each species and in the date column I have four dates for one species: 27/12/2017: 06/01/2019: 14/12/2019: 20/11/2020

    What I would like to do is find an average date, consisting of a day and month, but NOT a year, for these flowers appearing.


    I have tried an array in the formula field {=AVERAGE(DATE(,MONTH(F14:I14),DAY(F14:I14)))} But this produces a date of 16/09/1900!


    My guess would be that the average date would be 21st December. Is there a different formula I can use to get this average figure.


    Thanks for reading

    Re: How to calculate difference between dates in successive years


    Thanks for the interesting reply, but I couldn't get it to work.


    I have attached a small portion of the spreadsheet as a demo. The columns where I am trying to calculate the figures are 'E' and 'G'.


    In the demo I have manually entered the figures. So Row 5, Column G, it is the difference in days between the 26th February and the 6th March. The year is irrelevant. In this case it is +8 days (8 days later than last year)


    A B C D E F G
    [TABLE="width: 583"]

    [tr]


    [TD="width: 208"]5 Daffodil by path in flower[/TD]
    [TD="class: xl63, width: 94, align: right"]07/03/2015[/TD]
    [TD="class: xl63, width: 34"][/TD]
    [TD="class: xl63, width: 76, align: right"]26/02/2016[/TD]
    [TD="class: xl65, width: 49"] -9[/TD]
    [TD="class: xl63, width: 76, align: right"]06/03/2017[/TD]
    [TD="class: xl64, width: 46"]+8[/TD]

    [/tr]


    [/TABLE]

    I am keeping a Nature "Springwatch" calendar using a standard Excel spreadsheet. I have a list of the indicator species in column A.


    There are then two columns for each year. As I note something e.g. 'First plum blossom', I then enter the date in the first column for that year. The Cell format is Category = Date; Type = *14/03/2001 Locale = English (UK). Excel version is 2007


    What I am trying to do in the second column for each year is to show the number of days variance, as either a +/- over this year, from last year. So if the date was 10 days earlier than last year, it would be '-10', if it was 10 days later, it would be '+10'.


    So far, I am unable to find a way to achive this.


    Thanks for reading.


    NW