S

#### smurray444

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