Creating a macro to apply a formula to particular rows

[smurray444]

Dear all,

I have a large dataset of meterological stations, each containing data for an inconsistent duration of years in each case. Each year is on a separate row and whilst most stations have data from 1950-1990, this isn't always the case (i.e. some range from say, 1956-1972, 1982-1988, 1960-1978 etc etc... the years will always be continuous though - you won't find, say, 1956-1970 [DATA GAP] 1975-1990 for any given station). I'm interested in applying a linear projection to solar radiation values (based on the year they are associated with), in order to estimate future solar values in the year 2050.

Would you be able to design a means by which for each station (station number is in column A), the highest solar value (in column F) between 1980-1990 (year is in column E) has the following formula applied to it: ((2050-[year of highest solar value, e.g. 1988])*0.66)+[solar value corresponding to the year chosen]. For example, ((2050-1988)*0.66)+123 which should yield an answer of 163.92. Solar values are also in column Q (F = winter value whereas Q = summer value [for a particular year] - these are on the same row as the year they are associated with); so could you run the same process again, simply substituting the value in column F for that in Q when applied to the "solar value corresponding to the year chosen" term in the formula above.

With regard to where to place the output value, I figured that I'd increase my spreadsheet efficiency by avoiding the creation of additional columns. Instead, would you be able to place the newly generated values (there should be two for each row in which the formula has been applied), into a new *row* beneath the final row of existing data for that particular station (e.g. the solar value for the formula to be applied may have come from say, 1985, yet if the data for that station continues until say, 1990, then the new row should be created below the 1990 row and *not* the 1985 row. To complete the data for the newly created row, could you enable all the data for the row in which the highest solar value was selected to be copied and pasted down into this new row, except of course for the cells in columns F and Q which should contain the new solar values generated from the formula above, and for the year column (E) which instead of containing the year value for the row from which the data has been copied and pasted, should always be '2050'.

So just to be clear, due to the data inconsistancies, not all stations have data in the 1980-1990 range - these stations should be ignored and no new values/rows generated for them. Some stations may have data for, say, 1950-1983 - although the full 1980-1990 year range isn't available in this case, the highest solar value for application of the formula should still be chosen based on the greatest solar value between 1980-1983 in this case.

Sorry for the long-winded nature of this message, yet I hope the clarity is there. If not, please do not hesitate to get back to me.

Many thanks for your help,
Steve

[smurray444]

Has anyone had any luck with this? I'd be very grateful for help here.

Thanks, Steve