Posts by NormanW

    There is an active workbook for each year. Every December I copy the blank year, rename to the new year then the blank remain untouched. I have a second blank called leapyear blank. New century years are not generally leap years. I called it 2000 just to easily show it is the master worksheet.

    Data is only entered once in a worksheet to be efficient, then is used across a range of statistical graphs and charts to show climate breakdown effects. Every worksheet is an exact replica of blank year.


    In blank worksheet, the 12 rows highlighted in yellow, 8, 28, 48, 68 etc to 228 have had thee formulae corrected. Because cells B28, C28 to K28 add a value from the corresponding row for the previous month above, I need to copy cells B*8 to AF*8, so all 12 rows of 31 cells in the blank, correct worksheet, to corresponding rows in all the annual sheets, like 2022 where there is an error on all 12 rows for "11 Day Avge".


    I can copy one row at a time, but it is hardly efficient. So I am asking if there is a way to select the 31 cells, in the 12 separate rows of the blank, so 372 cells in total, in blocks of 31 and then copy them all in one go to each active worksheet?

    Can you attach a workbook with one of your 12 worksheets and indicate what formula needs to be changed (and what you want it change to).


    It may be possible to write a macro, that would need to be run just once, to update the formulae in all the worksheets.

    Thank you for replying, I have attached a slimmed down sheet.

    Weather statistics 2022 ERROR.xlsx

    The "Blank Year" workbook has the 12 now corrected rows highlighted in yellow. The 2022 workbook is uncorrected so all 12 rows have errors at the start of the month based on how many days there are at the end of the previous month.

    In addition to 12 individual "Year" workbooks there are three additional books in the worksheet which aggregate all the individual year's data in one place and do more analysis and chart the results. These DO NOT need any amendments.

    I have made a mistake creating an Excel workbook containing 12 worksheets. I am using a legal copy of Excel 2007.


    I have a new Excel workbook to record and analyse weather statistics; temperature, humidity and similar data.


    There are 12 worksheets, each one for a single year.


    Each worksheet has 12 months, each month consisting of 16 rows and 31 columns per month, with blank cells at the end of rows for months with 28 and 30 days. The error is in one single line in each month.


    While doing some back record conversion I have realised that in my master blank worksheet, and consequentially every copy there is an error for February.


    A key Cardinal temperature for farmers and growers is the 11 day rolling average temperature. It is easy to create cell formulae to add 11 cell's data then divide by 11 to correct the error. The problem is then how to copy the amended row with 30 or 31 formulae to all the other sheets.


    I have realised that when I created the master sheet, I introduced an error in one line of formulae because I used 30 days for February not 28/29. This means that for March to December, in one row for each of the 10 months there is an error.


    Is there a way to copy 10 separate rows of amended formulae (1 in March, April etc) in one copy and paste operation to each worksheet? Or do I have to copy the 10 amended rows individually into each of the 12 year's worksheet? ie 120 separate copy and paste operations. So is it 12 copy and pastes or `120?


    Thank you for reading

    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