Dynamic Trendline of Air Temperature in Jerusalem

Abstract

Linear trendline of air temperature in Jerusalem shows temperature increase between years 2017-2100 as "only" +4.66°C, while the "chain reaction" effect as determined using dynamic trendline, results in +7.00°C additional warming, total +11.66°C. The "chain reaction" of global warming is observed as constant change of air temperature trendline. The trendline was determined using yearly average temperatures of last 18 years. The "chain reaction" was determined as yearly change of the trendline in last 4 years. This work applies over 1 million air temperature records monitored at one meteorological station in Jerusalem. Total 13,898 (1.2%) missing records were estimated using linear interpolation between neighboring records, copying records from other Jerusalem stations and copying records from neighboring days.

Full text

Dynamic Trendline of Air Temperature in Jerusalem
by Joseph Nowarski, M.Sc., ME – Energy Conservation Expert
Abstract
Linear trendline of air temperature in Jerusalem shows temperature increase
between years 2017-2100 as "only" +4.66°C, while the "chain reaction" effect as
determined using dynamic trendline, results in +7.00°C additional warming, total
+11.66°C.
The "chain reaction" of global warming is observed as constant change of air
temperature trendline. The trendline was determined using yearly average
temperatures of last 18 years. The "chain reaction" was determined as yearly
change of the trendline in last 4 years.
This work applies over 1 million air temperature records monitored at one
meteorological station in Jerusalem. Total 13,898 (1.2%) missing records were
estimated using linear interpolation between neighboring records, copying records
from other Jerusalem stations and copying records from neighboring days.
Source of Data
The Israeli Meteorological Service publishes on Internet monitored records of
various meteorological parameters from many meteorological stations across the
country [1]. The Israeli Meteorological Service is governmental unit.
The data can be downloaded to CSV (Text and Excel) files. The records are for
every 10 minutes, which results in very big files. Number of records for one year is
52,560. There are limits of downloaded file's size, allowing download of one
parameter for one year only.
The parameter selected is "temperature", which is "dry temperature".
There are few meteorological station is Jerusalem. Among all Jerusalem stations,
Givat Ram station number 7020 has the longest records, reaching year 1993,

Dynamic Trendline of Air Temperature in Jerusalem - Joseph Nowarski
2 / 18
therefore it was selected for the purpose of this work. Givat Ram is central location
in Jerusalem, the site of Jerusalem University, government campus, the parliament
and Israel Museum. The station is located at the Institute of Earth Science at
Jerusalem University, N31.7704° E35.1973°, elevation 770 m. This station may be
regarded as "open air" without higher buildings around, surrounded by relatively
green area.
All data are in degrees Celsius according to winter time – LST.
Missing Records
Table 1 - Missing records
year missing missing, %
1993
530
1%
1994
3,809
7%
1995
1,928
4%
1996
730
1%
1997
541
1%
1998
54
0%
1999
237
0%
2000
83
0%
2001
38,138
73%
2002
ALL 100%
2003
8,726
17%
2004
606
1%
2005
11
0%
2006
1,121
2%
2007
4
0%
2008
4
0%
2009
3
0%
2010
4
0%
2011
16
0%
2012
11
0%
2013
4,134
8%
2014
15
0%
2015
6
0%
2016
13
0%
2017
38
0%
The biggest problem is the range 2001-2003. Following analysis shall determine the
applicability of other sources of data for these years.

Dynamic Trendline of Air Temperature in Jerusalem - Joseph Nowarski
3 / 18
Other Meteorological Station in Jerusalem
3 hours records from various stations were compared to the base, which is the
average yearly temperature at Givat Ram based on 10 minutes database. 2 years
were selected for the comparison: 2012 and 2015, those are the years with
minimum missing data for Givat Ram. For the purpose of the comparison, year
2012 includes 29 February (removed in further analysis).
Table 2 - Other stations in Jerusalem
3 hours Ave °C ∆ to Base Ave °C ∆ to Base ∆ Ave ∆ Max
Station 2012 °C 2015 °C °C °C
Base = Givat Ram 10 min 17.8385
N/A 17.8028
N/A
Givat Ram 3hr 17.8245
-0.0140
17.8066
0.0038
-0.0051
0.0140
Center 18.3339
0.4954
18.0223
0.2195
0.3574
0.4954
Givat Shaul 17.6569
-0.1816
17.4185
-0.3844
-0.2830
0.3844
Teva 18.9501
1.1116
18.0631
0.2603
0.6859
1.1116
The most similar station is Givat Shaul. The temperature in Givat Shaul is slightly
lower. However the number of missing records at Givat Shaul station is very large,
Alternative stations for estimation of missing data from Givat Ram station:
o 1
st
priority – Givat Shaul station
o If no records for specific date and time from Givat Shaul station, 2
nd
priority – Jerusalem Center station
Jerusalem Center station is located in the city center at Generali Building.
Table 3 - Difference between Givat Ram station and Jerusalem Center station
Jerusalem
Jerusalem
∆
Givat Ram
Center °C
Ave Temp 2005 17.1010
17.4144
0.3134
Ave Temp 2007 17.0293
17.6039
0.5746
Ave Temp 2012 17.8385
18.3339
0.4954
Ave Temp 2015 17.8028
18.0223
0.2195
Average 0.4007
In average, Jerusalem Center station is 0.4°C warmer than Givat Ram station.

Dynamic Trendline of Air Temperature in Jerusalem - Joseph Nowarski
4 / 18
Data for years 2001-2003
As mentioned before, there is very large number of missing records from the Givat
Ram station for years 2001-2003. The only Jerusalem station having 10 minutes
records for these years is Jerusalem Center.
Table 4 - Givat Ram and Jerusalem Center records comparison
Jerusalem Jerusalem ∆
Givat Ram Center °C
missing 2005 11
29
missing 2005 0.02%
0.06%
missing 2007 4
293
missing 2007 0.01%
0.56%
Ave Temp 2005 17.1010
17.4144
0.3134
Ave Temp 2007 17.0293
17.6039
0.5746
Unfortunately, the difference of average temperature between the 2 stations
disqualifies the Jerusalem Center station as the substitute for application for years
2001-2003, and as the result, this work will not include years 2001-2003.
Determination of Missing Records
Following procedure is applied for determination of missing records:
o Up to 11 missing records, the estimation is done as linear interpolation
between the neighboring records.
o If available, copy records for the relevant dates and times from Givat
Shaul station database.
o If no relevant records are available from Givat Shaul station, copy records
for the relevant dates and times from Jerusalem Center station database,
however not as is, but considering temperatures differences between the
stations for neighboring records (record before and record after the
missing period).

Dynamic Trendline of Air Temperature in Jerusalem - Joseph Nowarski
5 / 18
o If no data from any alternative station, copy records from neighboring
days as average between day before and day after the missing period.
o If no data from any alternative station for very long periods, more then
2,000 missing records for a single period, calculate average between 2
years before and 2 years after the missing period for the same days and
times and for the same station.
Examples of determination of missing data
Year 1993
There are 530 missing records for year 1993 (1%).
There are no data for any other station in Jerusalem for year 1993.
504 records (from 23 Sep to 26 Sep) were determined as average between day
before and day after the missing period for the same times.
All other missing records were determined using linear interpolation between
neighboring records.
Year 1994
There are 3,809 missing records for year 1994 (7%).
Some data are available from Jerusalem Center station, however not for the
whole missing period.
Out of 3,809 missing records, 1,658 were copied from Jerusalem Center station.
Jerusalem Center Station is wormer than the applied Givat Ram station, therefore
the data from Jerusalem Center station were corrected considering temperatures
differences between the stations for neighboring records (record before and
record after the missing period).
2,118 records were estimated as average between year 1993 and the next 2 years
for which the data is available (years 1995 and 1996), for the same dates and
times and for the same station, Givat Ram. For 67 above records, year 1995 was
not available and the average was between year 1993 and 1996.

Dynamic Trendline of Air Temperature in Jerusalem - Joseph Nowarski
6 / 18
Table 5 - Determination of missing data 1994
Records OK 48,751
92.8%
Missing records 3,809
7.2%
Copied from Jerusalem Center 1,658
3.2%
Ave (1993, 1996) 67
0.1%
Ave (1993, 1995, 1996) 2,051
3.9%
Linear interpolation 33
0.1%
Year 1995
There are 1,938 missing records for year 1995 (4%).
All data are available from Jerusalem Center station.
Temperature correction is based on temperature before and after the missing
period, which means that the Jerusalem Center data were corrected to receive
exact value of the Givat Ram for the last and for the next available record.
Year 1996
There are 730 missing records for year 1996 (1%).
Unfortunately for 576 records (1-4 March) there are no records also from the
alternative station (Jerusalem Center).
As for year 1996 data from 29 February were available, they were applied "as are"
for 1 March. For 2-4 March, the averages from the neighboring days were applied.
For 127 records available from Jerusalem Center station, temperature correction is
based on record before and after the missing period, which means that the
Jerusalem Center data were corrected to receive exact value of the Givat Ram
for the last and for the next available record.
Year 2013
Out of 4,134 missing records in year 2013 (8%), 2,376 were copied from Givat Shaul
station and 1,748 from Jerusalem Center station after correction based on records
before an after the missing period.

Dynamic Trendline of Air Temperature in Jerusalem - Joseph Nowarski
7 / 18
29 February
Years having 366 days a year have colder average temperature than 365 days'
years, because of additional day. From the 365 days’ year point of view, the
additional day is 1 January of next year, which usually is much colder than the
year average.
Therefore 29 February was removed from the calculations of average
temperature.
Database applied
Table 6 - Database applied
years 22
years
existing 1,142,422
records
existing 98.8%
records
missing 13,898
records
missing 1.2%
records
full set 1,156,320
records
This work applies database of air temperature monitored at one station between
years 1993-2017. 3 years (2011-2013) were excluded from the analysis because of
large number of missing records. Finely the database applied includes 10 minutes
records for 22 years. The number of available records is 1,142,422 (98.8%). The
number of missing records in years applied is 13,898 (1.2%). After determination of
missing records the corrected database includes 1,156,320 records.

Dynamic Trendline of Air Temperature in Jerusalem - Joseph Nowarski
8 / 18
Yearly Average Temperatures
Table 7 - Yearly average temperatures
year Ave °C +/- ∆ to Ave °C
1993
16.76
-0.65
1994
16.78
+ -0.62
1995
16.96
+ -0.45
1996
17.28
+ -0.12
1997
16.40
- -1.00
1998
17.63
+ +0.22
1999
17.60
- +0.19
2000
16.76
- -0.65
2004
17.20
+ -0.20
2005
17.10
- -0.31
2006
16.81
- -0.60
2007
17.03
+ -0.38
2008
17.66
+ +0.26
2009
17.67
+ +0.26
2010
19.38
+ +1.97
2011
16.82
- -0.59
2012
17.84
+ +0.43
2013
17.69
- +0.29
2014
17.87
+ +0.46
2015
17.80
- +0.39
2016
18.17
+ +0.77
2017
17.74
- +0.33
Max 19.38
+1.97
Min 16.40
-1.00
Ave 17.41
Dynamic Trendline
The easiest way to explain the meaning of dynamic trendline is analysis of body
weight database, recorded every day. The purpose of recording such database is
to receive some expectations regarding the future body weight.
The body weight database has new record every day. This new record is in
addition to many records from the past, possibly tens of years. Such database
allows determination of trendline for any future period. The simplest way to find the
trendline is to use Excel graph with trendline for future months (or years). Let's say
that we would like to know what will be the body weight half a year from today,
based on data from last half year. To prepare such graph we have to select data
from last half year and to add days for the next half year. The graph and trendline

Dynamic Trendline of Air Temperature in Jerusalem - Joseph Nowarski
9 / 18
we shall receive is good as for the today's database. If we want to update the
trendline tomorrow, we have to build again the graph and the trendline.
Dynamic trendline changes automatically with each update of input data. It
creates automatically the trendline based on only the last 6 month of updated
data and automatically determines the next 6 months period from now.
Calculation of Linear Trendline
Any straight line has the following formula:
Formula 1 - Straight line
y = a * x + b
a slope, tangent of the line angle from horizon
b value of y when x = 0
To avoid use of parameter name "a" which easily may be misunderstood in text, it
will be replaced in this work by "dTL" and the formula for linear trendline will be:
Formula 2 - Linear trendline
y = dTL * x + b
Theoretically, it is possible to get the trendline formula on the graph.
The formula type is: y = dTL*x+b.
The problem is, what is regarded as "x" in the Excel trendline formula.
In our example of the body weight, we regard as "x" the date of the record. We
cannot use directly the "x" parameter from the Excel formula as the specific date
for which we want to find "y" and to multiple it by dTL. To calculate y, which is the
body weight, we have to build auxiliary program between the date and between
"x" as Excel trendline formula understands it. It may be easily done however it is not

Dynamic Trendline of Air Temperature in Jerusalem - Joseph Nowarski
10 / 18
convenient, and we would prefer a formula in which we can directly apply the
date.
Other serious problem of Excel trendline formula is the precision of "dTL" and "b"
values as displayed on the trendline graph. The values of "dTL" and "b" are not
precise enough, which results in serious deviation from the correct trendline value
("y").
Considering the above, mathematic formulas will be applied for trendline
calculations rather than formula from the trendline graph.
In XL file:
o x values (dates) are in column A
o y values (body weight) are in column B
Formula 3 - Set of formulas for determination of dTL and b
AveX = AVERAGE(A6:A185) //Dates
AveY = AVERAGE(B6:B185) //Body weight
∆x = x - AveX
∆y = y - AveY
dTL = SUM(∆x * ∆y) / SUM((∆x)^2)
b = AveY – dTL * AveX
The above procedure allows direct multiplication of x, also in case when x is a
date. In Excel date may be displayed as value, for instance date 20/01/2018 is
equivalent to number 43,120.0.

Dynamic Trendline of Air Temperature in Jerusalem - Joseph Nowarski
11 / 18
Formulas for Dynamic Trendline
Set of records is for specific period of time. Records are only from the past. Current
trendline is determined based on the most updated current records. This trendline
will be not necessary the same in the future, when new record will be added to
the database. The dynamic trendline is changing with any additional record.
The "Dynamic Trendline" system automatically selects the most updated records,
immediately after update.
This is done applying "Index" function in Excel.
For parameter x (date) the function is:
Formula 4 - Index function to select most updated records
x = INDEX(Input!A$3:A$65536, COUNTA(Input!A3:A65536)-counter,1)
Counter is "1,2 , 3 …".
To easily apply the counter, auxiliary column may be added with counter (1, 2, 3
…).
The above formula will result in reverse order of records. If increasing order of
records is requires (from latest to newest), there is a need to determine the first
starting record. If the task is to get the last 180 records in the same order as the
input, the formula will be as follows:
Formula 5 - Selection of 180 latest records
x = INDEX(Input!A$3:A$65536, COUNTA(Input!A3:A65536)-180+
+counter(from 1 to 180),1)

Dynamic Trendline of Air Temperature in Jerusalem - Joseph Nowarski
12 / 18
Air Temperature Trendline Based on 5 Years
Currently the average air temperature database includes 22 records, from year
1993 to year 2017, without years 2001-2003.
The first 5 years of the database are from year 1993 to 1997.
Table 8 - Input of 5 first years of the database
year Ave °C
1993
16.76
1994
16.78
1995
16.96
1996
17.28
1997
16.40
Table 9 - Trendline calculations
TL year (x) Ave °C (y) ∆x = x - AveX ∆y = y - AveY ∆x * ∆y (∆x)^2
1993
16.76
-2.0
-0.08
0.1589
4.0
1994
16.78
-1.0
-0.05
0.0535
1.0
1995
16.96
0.0
0.12
0.0000
0.0
1996
17.28
1.0
0.45
0.4454
1.0
1997
16.40
2.0
-0.43
-0.8655
4.0
Ave 1995.0
16.84
Σ -0.2076
10.0
dTL -0.0208
B 58.25
The trendline based on years 1993-1997 is negative, which means, that it may be
expected that the air temperature in Jerusalem will decrease with years

Dynamic Trendline of Air Temperature in Jerusalem - Joseph Nowarski
13 / 18
Table 10 - Air temperature trendline based on 5 first years of the database
year Ave °C TL 1997 ∆ to TL
1993
16.76
16.88
-0.12
1994
16.78
16.86
-0.07
1995
16.96
16.84
0.12
1996
17.28
16.82
0.47
1997
16.40
16.80
-0.39
1998
17.63
16.78
0.85
1999
17.60
16.75
0.85
2000
16.76
16.73
0.02
2001
16.71
2002
16.69
2003
16.67
2004
17.20
16.65
0.55
2005
17.10
16.63
0.47
2006
16.81
16.61
0.20
2007
17.03
16.59
0.44
2008
17.66
16.57
1.09
2009
17.67
16.55
1.12
2010
19.38
16.53
2.85
2011
16.82
16.51
0.31
2012
17.84
16.48
1.35
2013
17.69
16.46
1.23
2014
17.87
16.44
1.43
2015
17.80
16.42
1.37
2016
18.17
16.40
1.77
2017
17.74
16.38
1.36
2018
16.36
2019
16.34
2020
16.32
2021
16.30
2022
16.28
2023
16.26
2024
16.24
2025
16.21

Dynamic Trendline of Air Temperature in Jerusalem - Joseph Nowarski
14 / 18
Moving the 5 Years Input by One Year
Table 11 - Moving the 5 years input by one year, from 1993-1997 to 1994-1998
year (x) Ave °C (y) 2025
1993
16.76
1994
16.78
1995
16.96
1996
17.28
1997
16.40
16.21
1998
17.63
20.31
Moving the input for one more year, from 1993-1997 to 1994-1998, changes the 5
years trendline to positive, indicating the expected air temperature in year 2025 as
20.31°C, comparing to 16.21°C according to trendline based on years 1993-1997.
This result clearly indicates that the air temperature trendline cannot be
determined based on 5 years only, and the number of years which should be
applied for such analysis is much bigger.

Dynamic Trendline of Air Temperature in Jerusalem - Joseph Nowarski
15 / 18
Trendline Based on 10 Years Input
Table 12 - Trendline based on 10 years input
year (x) Ave °C (y) 2025
1993
16.76
1994
16.78
1995
16.96
1996
17.28
1997
16.40
1998
17.63
1999
17.60
2000
16.76
2004
17.20
2005
17.10
17.83
2006
16.81
17.18
2007
17.03
16.92
2008
17.66
17.34
2009
17.67
17.86
2010
19.38
18.90
2011
16.82
18.61
2012
17.84
19.37
2013
17.69
19.19
2014
17.87
19.12
2015
17.80
18.79
2016
18.17
18.46
2017
17.74
17.81
Also application of 10 years baseline results in too big differences of the expected
temperature in year 2025, between 16.92°C and 19.37°C. This means that air
temperature trendline cannot be determined based on 10 years only, and the
number of years which should be applied for such analysis is much bigger.

Dynamic Trendline of Air Temperature in Jerusalem - Joseph Nowarski
16 / 18
Trendline based on 18 years input
Table 13 - Trendline based on 18 years input
year (x) Ave °C (y) 2025
1993
16.76
1994
16.78
1995
16.96
1996
17.28
1997
16.40
1998
17.63
1999
17.60
2000
16.76
2004
17.20
2005
17.10
2006
16.81
2007
17.03
2008
17.66
2009
17.67
2010
19.38
2011
16.82
2012
17.84
2013
17.69
18.37
2014
17.87
18.37
2015
17.80
18.33
2016
18.17
18.42
2017
17.74
18.42
According to database for years 1993-2013, the trendline temperature in year 2025
will be 18.37°C.
According to database for years 1997-2017, the trendline temperature in year 2025
will be 18.42°C.
Application of 18 years input database gives good results. The trendline value in
year 2025 will be between 18.33°C and 18.42°C. The trendline temperature
differences are reasonable.

Dynamic Trendline of Air Temperature in Jerusalem - Joseph Nowarski
17 / 18
Trendline and Actual Temperature in Last 4 Years
Table 14 - Trendline and actual temperature in last 4 years
year Ave °C TL +/- ∆
2013
17.69
17.78
-0.09
2014
17.87
17.83
+ +0.04
2015
17.80
17.88
- -0.09
2016
18.17
17.93
+ +0.24
2017
17.74
17.98
- -0.24
Dynamic Trendline for Last Year
The average temperature in year 2017 was 17.74°C.
Table 15 - Dynamic trendline of temperature for year 2017
TL °C ∆ °C
TL 1993-2013 17.98
-0.24
TL 1994-2014 17.98
-0.24
TL 1995-2015 17.96
-0.22
TL 1996-2016 18.01
-0.27
Dynamic Trendline and Future Air Temperature in Jerusalem
Table 16 - Dynamic trendline and future air temperature in Jerusalem
dTL b TL °C TL °C TL °C TL °C ∆°C to 2017
2025 2050 2075 2100 2100
TL 1993-2013 0.0492
-81.2
18.373
19.60
20.83
22.06
+4.32
TL 1994-2014 0.0490
-80.9
18.374
19.60
20.82
22.05
+4.31
TL 1995-2015 0.0468
-76.4
18.329
19.50
20.67
21.84
+4.10
TL 1996-2016 0.0508
-84.4
18.415
19.68
20.95
22.22
+4.48
TL 1997-2017 0.0530
-89.0
18.420
19.75
21.07
22.40
+4.66

Dynamic Trendline of Air Temperature in Jerusalem - Joseph Nowarski
18 / 18
What-If the Trendline Will Change as in Last 4 Years
Table 17 - What can be expected in year 2100 if the trendline will change as in
last 4 years
Trendline change in 4 years +0.34
Trendline change per year +0.08
years till 2100 83
years
TL change till 2100 +7.00
∆°C till 2100
∆°C from 2017@2017 +4.66
∆°C till 2100
∆°C from 2017@2100 +11.66
∆°C till 2100
Global Warming Chain Reaction
Linear trendline of air temperature in Jerusalem shows temperature increase
between years 2017-2100 as "only" +4.66°C.
The continuous change of air temperature trendline indicates the global warming
chain reaction.
The "chain reaction" was determined as yearly change of the trendline in last 4
years.
The "chain reaction" effect between year 2017 and 2100, as determined using
dynamic trendline, results in +7.00°C additional warming in year 2100, total
+11.66°C.
This indicates that the "chain reaction" of the global warming is much more
dangerous than the "business as usual" global warming.
Reference
1. The Government Databases / Meteorological Service
https://ims.data.gov.il/ims/7
* * *