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
------------------------------------------------------------------------
smurray444's Profile:
http://www.excelforum.com/member.php...o&userid=28956
View this thread:
http://www.excelforum.com/showthread...hreadid=508562