Search code examples
excelexcel-formulaformula

Excel formula help - I have 14/28 day readings averaged per day which need to be recorded as daily readings

Dates when the data was read are shown on the date column on the left, then worked out a value per day shown in ET/DAY column.

To analyze monthly data etc etc I want daily data so every day has the ET/DAY value between the dates it was read and inserted in the et/day column.

I have entered in manually what I want in blue but need a formula to do this as too long to do all manually

Solution

  • You can do this with MATCH. With an ascending list, if you give the magic number 1 as the third parameter, it will show the last row number with a smaller or equal number.

    Here it's matched 17 with 15 in column A and the result is 2, for row 2:

    A B C
    1 10 =MATCH(17,A:A,1) = 2
    2 15
    3 20

    So for your data, you can match the daily dates with the column on the left, and feed the row number into INDEX to look up from the ET/Day column.

    A B C D E
    1 Date ET/Day Daily ET/Day
    2 05/Jan/1995 05/Jan/1995 =Index(B:B, Match(@D:D,A:A,1)) = #N/A
    3 19/Jan/1995 3.00 06/Jan/1995 =Index(B:B, Match(@D:D,A:A,1)) = 3.00
    4 02/Feb/1995 5.41 07/Jan/1995 =Index(B:B, Match(@D:D,A:A,1)) = 3.00
    5 ... ...
    6 19/Jan/1995 =Index(B:B, Match(@D:D,A:A,1)) = 3.00
    7 20/Jan/1995 =Index(B:B, Match(@D:D,A:A,1)) = 5.41

    There's an inconsistency here with the 05 Jan rate but it should be good after that.