Time series data analysis

[lrotich]

I have time series data in excel where one column contains time of day (local Standard Time Meridian-LSMT) from 1st January to 31st December totaling up to 8760 rows. The next column has 360 rows with values of Equation of time (EOT) which are day dependent. My objective is to convert each LSMT to local Solar time(LST) based on the two columns. EOT value is to be used to calculate LSTs for its corresponding day. Kindly assist me get an excel function that will let me calculate 24 LSTs for day 1 using EOT value for the same day then proceed and carry out the same process for the next till the 365th day of the year. I have attached an excel file with the data. I would like to populate column F with the formula shown in cell F4 where the value H4 is used with range of values E4:E27. The Value H5 to be used with the range of values E28:E51 to populate F28:F51. The calculation should be executed to the end of Values in column H. I do hope that my case is clearer now. Thanks.

[Glenn Kennedy]

In mathematical terms what do you want...

You said ". I would like to populate column F with the formula shown in cell F4 where the value H4 is used with range of values E4:E27. "

EXACTLY, MATHEMATICALLY what does that mean?

[José Augusto]

Try in F4 and drag down

Please Login or Register  to view this content.

[lrotich]

Thank you Glenn for your time. Column F values are to be calculated using the Formula in cell F4 which basically gives the relationship between E column values and H column Values. However each Value in column H is limited to a specific range of values in column E through the formula. Jose has offered a solution below which has addressed my concern. Thanks again

[lrotich]

Thank you Jose Augusto for your help. My issue is now resolved, thanks to you.

[José Augusto]

Hi
You are welcome.

Please select Thread Tools from the menu link above and mark this thread as SOLVED.

Thanks.