You can create a calc that uses the PREVIOUS_VALUE function. It would run along the line and look at the current value of AVG(Ferenheit). If it's null, set the calc to null. If it's not null, and if PREVIOUS_VALUE is null, then you have the first in the series. Set it to the AVG(ferenheit) value. If current value is not null, and if PREVIOUS_VALUE is also not null, then just grab PREVIOUS_VALUE (which would end up propagating the first value for the rest of the series.)
That's how it would work in theory. The specific implementation ... I would have to play with it to make sure it's coded properly.
If you can't get this to work, upload a sample workbook and I'll show you.
Thank you Joe. I've actually attempted to use Previous_Value function before making this post but was not able to make it work. I've attached the workbook. Thank you for all your help.
Looking at the data and at Sheet 5, we might be able to get away without PREVIOUS_VALUE.
I made a FIXED LOD calc that gives us the first date of any non-null data for each country. I added that value after the Country column on Sheet 5. If this approach is valid you can grab the AVG(Ferenheit) where the YEAR(DT) is the same as the YEAR(First Date...). Is it a safe assumption that the first recorded temp indicates the first year you have an AVG(Ferenheit)? On the surface that makes perfect sense to me, but you might know something more about the data than I am aware of.
But I also wrote up a calc that uses PREVIOUS_VALUE. See [First AVG F]. If you do WINDOW_MAX([First AVG F]), you'll have that first value, wherever it lands on the row, Throw my calc onto TEXT and you'll see what it does.
Thank you Joe. I will give this a try. Sorry for the late reply.
I've tried this formula. It does work for values that start at the beginning of the year range (1743) where it calculates to difference from base year. However it does not work for any other values where temperatures were not available in 1743. Was it the same way for you when you tried? Any suggestions?
Did you look at the example I uploaded?
I took what I wrote in that last reply and threw the [First AVG F] calc on TEXT. I also created [Calculation2] that just compares year(Dt) to year(First Date...) and grabs the avg. (It's a FIXED LOD that acts at the level of [Country].)
Both are displayed on Sheet 5 in the attached.
Thank you Joe. This worked great. I just need to subtract the First AVG F calc from the Avg temp to get my difference from base.
Thanks again for your help.