| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Choosing the right statistical function
I was wondering which statistical function would be the best to use when
trying to see if their is a relationship between temperature and glacier length change. I have used the CORREL function to try and assess there correlation but will this give me bad answers as there isn't a straight line relationship between the two!?!? |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Choosing the right statistical function
Hi,
Presumably you have tried plotting the data on an x-y chart and tried adding any of the non-linear trendlines? Dave "Stibbz" wrote: I was wondering which statistical function would be the best to use when trying to see if their is a relationship between temperature and glacier length change. I have used the CORREL function to try and assess there correlation but will this give me bad answers as there isn't a straight line relationship between the two!?!? |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Choosing the right statistical function
I'm trying to picture the relationship around the 31-33 degree F range :)
Just kidding... Dana DeLouis Stibbz wrote: I was wondering which statistical function would be the best to use when trying to see if their is a relationship between temperature and glacier length change. I have used the CORREL function to try and assess there correlation but will this give me bad answers as there isn't a straight line relationship between the two!?!? |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Choosing the right statistical function
No I haven't used any of the non-linear trendlines as I am unsure on which
one is appropriate to use "Dave Curtis" wrote: Hi, Presumably you have tried plotting the data on an x-y chart and tried adding any of the non-linear trendlines? Dave "Stibbz" wrote: I was wondering which statistical function would be the best to use when trying to see if their is a relationship between temperature and glacier length change. I have used the CORREL function to try and assess there correlation but will this give me bad answers as there isn't a straight line relationship between the two!?!? |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Choosing the right statistical function
I agree there's a danger of drawing erroneous conclusions, just because your
data happens to fit part of, say a fourth order polynomial. Is therebno published data on a theoretical relationship between the two variables? Why not post a sample your data? Dave "Stibbz" wrote: No I haven't used any of the non-linear trendlines as I am unsure on which one is appropriate to use "Dave Curtis" wrote: Hi, Presumably you have tried plotting the data on an x-y chart and tried adding any of the non-linear trendlines? Dave "Stibbz" wrote: I was wondering which statistical function would be the best to use when trying to see if their is a relationship between temperature and glacier length change. I have used the CORREL function to try and assess there correlation but will this give me bad answers as there isn't a straight line relationship between the two!?!? |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Choosing the right statistical function
Ok here's some sample data for you to look at
Glacier length change data Year Aquila 1896 -2 1897 1898 10 1899 1900 -12 1901 1902 1903 -16 1904 -9 1905 -8 1906 1907 -14 1908 1909 1910 1911 4 1912 1913 1914 -4 1915 1916 1917 -3 1918 1919 -13 1920 1921 -34 1922 1923 4 1924 -6 1925 50 1926 -34 1927 1928 -9 1929 1930 -16 1931 14 1932 -13 1933 1934 1935 1936 1937 -6 1938 2 1939 0 1940 11 1941 -3 1942 -7 1943 -2 1944 -6 1945 -105 1946 1947 -18 1948 1949 -22 1950 1951 -6 1952 -8 1953 -6 1954 -4 1955 -14 1956 -18 1957 -22 1958 22 1959 -21 1960 7 1961 -23 1962 -8 1963 -7 1964 -14 1965 -15 1966 6 1967 1 1968 -1 1969 -33 1970 -16 1971 1972 1973 1974 1975 1976 0 1977 0 1978 -15 1979 0 1980 4 1981 -9 1982 21 1983 18 1984 1985 -5 1986 -19 1987 1988 -3 1989 -6 1990 -12 1991 1992 -17 1993 -8 1994 -14 1995 -11 1996 -17 1997 -29 1998 -71 1999 -23.4 2000 -14 2001 -18.8 2002 -77.1 2003 -76.8 2004 -1.8 2005 -13.8 2006 -26.1 Temperature Data 1896 8.33 1897 9.34 1898 9.56 1899 9.76 1900 9.78 1901 8.62 1902 8.84 1903 8.98 1904 9.74 1905 9.03 1906 9.32 1907 9.00 1908 8.75 1909 8.46 1910 9.13 1911 10.06 1912 8.93 1913 9.52 1914 9.01 1915 9.32 1916 9.21 1917 8.41 1918 9.32 1919 9.05 1920 10.05 1921 10.24 1922 9.01 1923 9.88 1924 9.13 1925 9.39 1926 9.84 1927 9.63 1928 10.38 1929 9.28 1930 10.18 1931 9.17 1932 9.33 1933 9.19 1934 10.16 1935 9.78 1936 9.82 1937 10.16 1938 9.63 1939 9.33 1940 8.85 1941 8.94 1942 9.53 1943 10.55 1944 9.77 1945 10.52 1946 10.15 1947 11.08 1948 10.32 1949 10.82 1950 10.64 1951 9.83 1952 10.14 1953 9.92 1954 9.62 1955 9.94 1956 8.72 1957 9.68 1958 9.80 1959 10.40 1960 9.93 1961 10.77 1962 9.27 1963 8.70 1964 10.02 1965 9.03 1966 10.22 1967 9.88 1968 9.56 1969 9.33 1970 9.54 1971 9.49 1972 9.41 1973 9.34 1974 10.02 1975 9.89 1976 9.99 1977 10.07 1978 9.23 1979 9.92 1980 9.05 1981 9.78 1982 10.60 1983 10.47 1984 9.74 1985 9.36 1986 10.03 1987 10.07 1988 10.71 1989 10.90 1990 10.98 1991 10.36 1992 10.79 1993 10.28 1994 11.84 1995 10.85 1996 9.99 1997 10.94 1998 10.83 1999 10.71 2000 11.37 2001 11.07 2002 11.54 2003 11.77 2004 11.18 2005 10.69 2006 11.18 "Dave Curtis" wrote: I agree there's a danger of drawing erroneous conclusions, just because your data happens to fit part of, say a fourth order polynomial. Is therebno published data on a theoretical relationship between the two variables? Why not post a sample your data? Dave "Stibbz" wrote: No I haven't used any of the non-linear trendlines as I am unsure on which one is appropriate to use "Dave Curtis" wrote: Hi, Presumably you have tried plotting the data on an x-y chart and tried adding any of the non-linear trendlines? Dave "Stibbz" wrote: I was wondering which statistical function would be the best to use when trying to see if their is a relationship between temperature and glacier length change. I have used the CORREL function to try and assess there correlation but will this give me bad answers as there isn't a straight line relationship between the two!?!? |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Choosing the right statistical function
Hi,
OK, here's my first attempt. First I deleted all the rows for which you have no length change data. Then, with years in column A starting in A2 with length change in column B starting in B2 with temperature in Column C starting in C2. I assumed an original arbitrary length of 1000 so the length of the glacier over the years is given in D2 by =1000+SUM($B$2:B2) and copied down. A x y plot of temp against this length gives a big group of points, and a linear trendline slopes downwards with an R-squared of about 0.4. Are we getting anywhere? Dave "Stibbz" wrote: Ok here's some sample data for you to look at Glacier length change data Year Aquila 1896 -2 1897 1898 10 1899 1900 -12 1901 1902 1903 -16 1904 -9 1905 -8 1906 1907 -14 1908 1909 1910 1911 4 1912 1913 1914 -4 1915 1916 1917 -3 1918 1919 -13 1920 1921 -34 1922 1923 4 1924 -6 1925 50 1926 -34 1927 1928 -9 1929 1930 -16 1931 14 1932 -13 1933 1934 1935 1936 1937 -6 1938 2 1939 0 1940 11 1941 -3 1942 -7 1943 -2 1944 -6 1945 -105 1946 1947 -18 1948 1949 -22 1950 1951 -6 1952 -8 1953 -6 1954 -4 1955 -14 1956 -18 1957 -22 1958 22 1959 -21 1960 7 1961 -23 1962 -8 1963 -7 1964 -14 1965 -15 1966 6 1967 1 1968 -1 1969 -33 1970 -16 1971 1972 1973 1974 1975 1976 0 1977 0 1978 -15 1979 0 1980 4 1981 -9 1982 21 1983 18 1984 1985 -5 1986 -19 1987 1988 -3 1989 -6 1990 -12 1991 1992 -17 1993 -8 1994 -14 1995 -11 1996 -17 1997 -29 1998 -71 1999 -23.4 2000 -14 2001 -18.8 2002 -77.1 2003 -76.8 2004 -1.8 2005 -13.8 2006 -26.1 Temperature Data 1896 8.33 1897 9.34 1898 9.56 1899 9.76 1900 9.78 1901 8.62 1902 8.84 1903 8.98 1904 9.74 1905 9.03 1906 9.32 1907 9.00 1908 8.75 1909 8.46 1910 9.13 1911 10.06 1912 8.93 1913 9.52 1914 9.01 1915 9.32 1916 9.21 1917 8.41 1918 9.32 1919 9.05 1920 10.05 1921 10.24 1922 9.01 1923 9.88 1924 9.13 1925 9.39 1926 9.84 1927 9.63 1928 10.38 1929 9.28 1930 10.18 1931 9.17 1932 9.33 1933 9.19 1934 10.16 1935 9.78 1936 9.82 1937 10.16 1938 9.63 1939 9.33 1940 8.85 1941 8.94 1942 9.53 1943 10.55 1944 9.77 1945 10.52 1946 10.15 1947 11.08 1948 10.32 1949 10.82 1950 10.64 1951 9.83 1952 10.14 1953 9.92 1954 9.62 1955 9.94 1956 8.72 1957 9.68 1958 9.80 1959 10.40 1960 9.93 1961 10.77 1962 9.27 1963 8.70 1964 10.02 1965 9.03 1966 10.22 1967 9.88 1968 9.56 1969 9.33 1970 9.54 1971 9.49 1972 9.41 1973 9.34 1974 10.02 1975 9.89 1976 9.99 1977 10.07 1978 9.23 1979 9.92 1980 9.05 1981 9.78 1982 10.60 1983 10.47 1984 9.74 1985 9.36 1986 10.03 1987 10.07 1988 10.71 1989 10.90 1990 10.98 1991 10.36 1992 10.79 1993 10.28 1994 11.84 1995 10.85 1996 9.99 1997 10.94 1998 10.83 1999 10.71 2000 11.37 2001 11.07 2002 11.54 2003 11.77 2004 11.18 2005 10.69 2006 11.18 "Dave Curtis" wrote: I agree there's a danger of drawing erroneous conclusions, just because your data happens to fit part of, say a fourth order polynomial. Is therebno published data on a theoretical relationship between the two variables? Why not post a sample your data? Dave "Stibbz" wrote: No I haven't used any of the non-linear trendlines as I am unsure on which one is appropriate to use "Dave Curtis" wrote: Hi, Presumably you have tried plotting the data on an x-y chart and tried adding any of the non-linear trendlines? Dave "Stibbz" wrote: I was wondering which statistical function would be the best to use when trying to see if their is a relationship between temperature and glacier length change. I have used the CORREL function to try and assess there correlation but will this give me bad answers as there isn't a straight line relationship between the two!?!? |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Choosing the right statistical function
Excel 2007
Correlation Chart I believe, I believe! http://www.mediafire.com/file/nyljg4oydzi/03_11_09.xlsx |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Choosing the right statistical function
Yeah that looks pretty good to me thanks!
"Dave Curtis" wrote: Hi, OK, here's my first attempt. First I deleted all the rows for which you have no length change data. Then, with years in column A starting in A2 with length change in column B starting in B2 with temperature in Column C starting in C2. I assumed an original arbitrary length of 1000 so the length of the glacier over the years is given in D2 by =1000+SUM($B$2:B2) and copied down. A x y plot of temp against this length gives a big group of points, and a linear trendline slopes downwards with an R-squared of about 0.4. Are we getting anywhere? Dave "Stibbz" wrote: Ok here's some sample data for you to look at Glacier length change data Year Aquila 1896 -2 1897 1898 10 1899 1900 -12 1901 1902 1903 -16 1904 -9 1905 -8 1906 1907 -14 1908 1909 1910 1911 4 1912 1913 1914 -4 1915 1916 1917 -3 1918 1919 -13 1920 1921 -34 1922 1923 4 1924 -6 1925 50 1926 -34 1927 1928 -9 1929 1930 -16 1931 14 1932 -13 1933 1934 1935 1936 1937 -6 1938 2 1939 0 1940 11 1941 -3 1942 -7 1943 -2 1944 -6 1945 -105 1946 1947 -18 1948 1949 -22 1950 1951 -6 1952 -8 1953 -6 1954 -4 1955 -14 1956 -18 1957 -22 1958 22 1959 -21 1960 7 1961 -23 1962 -8 1963 -7 1964 -14 1965 -15 1966 6 1967 1 1968 -1 1969 -33 1970 -16 1971 1972 1973 1974 1975 1976 0 1977 0 1978 -15 1979 0 1980 4 1981 -9 1982 21 1983 18 1984 1985 -5 1986 -19 1987 1988 -3 1989 -6 1990 -12 1991 1992 -17 1993 -8 1994 -14 1995 -11 1996 -17 1997 -29 1998 -71 1999 -23.4 2000 -14 2001 -18.8 2002 -77.1 2003 -76.8 2004 -1.8 2005 -13.8 2006 -26.1 Temperature Data 1896 8.33 1897 9.34 1898 9.56 1899 9.76 1900 9.78 1901 8.62 1902 8.84 1903 8.98 1904 9.74 1905 9.03 1906 9.32 1907 9.00 1908 8.75 1909 8.46 1910 9.13 1911 10.06 1912 8.93 1913 9.52 1914 9.01 1915 9.32 1916 9.21 1917 8.41 1918 9.32 1919 9.05 1920 10.05 1921 10.24 1922 9.01 1923 9.88 1924 9.13 1925 9.39 1926 9.84 1927 9.63 1928 10.38 1929 9.28 1930 10.18 1931 9.17 1932 9.33 1933 9.19 1934 10.16 1935 9.78 1936 9.82 1937 10.16 1938 9.63 1939 9.33 1940 8.85 1941 8.94 1942 9.53 1943 10.55 1944 9.77 1945 10.52 1946 10.15 1947 11.08 1948 10.32 1949 10.82 1950 10.64 1951 9.83 1952 10.14 1953 9.92 1954 9.62 1955 9.94 1956 8.72 1957 9.68 1958 9.80 1959 10.40 1960 9.93 1961 10.77 1962 9.27 1963 8.70 1964 10.02 1965 9.03 1966 10.22 1967 9.88 1968 9.56 1969 9.33 1970 9.54 1971 9.49 1972 9.41 1973 9.34 1974 10.02 1975 9.89 1976 9.99 1977 10.07 1978 9.23 1979 9.92 1980 9.05 1981 9.78 1982 10.60 1983 10.47 1984 9.74 1985 9.36 1986 10.03 1987 10.07 1988 10.71 1989 10.90 1990 10.98 1991 10.36 1992 10.79 1993 10.28 1994 11.84 1995 10.85 1996 9.99 1997 10.94 1998 10.83 1999 10.71 2000 11.37 2001 11.07 2002 11.54 2003 11.77 2004 11.18 2005 10.69 2006 11.18 "Dave Curtis" wrote: I agree there's a danger of drawing erroneous conclusions, just because your data happens to fit part of, say a fourth order polynomial. Is therebno published data on a theoretical relationship between the two variables? Why not post a sample your data? Dave "Stibbz" wrote: No I haven't used any of the non-linear trendlines as I am unsure on which one is appropriate to use "Dave Curtis" wrote: Hi, Presumably you have tried plotting the data on an x-y chart and tried adding any of the non-linear trendlines? Dave "Stibbz" wrote: I was wondering which statistical function would be the best to use when trying to see if their is a relationship between temperature and glacier length change. I have used the CORREL function to try and assess there correlation but will this give me bad answers as there isn't a straight line relationship between the two!?!? |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Choosing the right statistical function
Ugh!
Surely there are lags in glacial-length change with respect to temperatures?!? Tossing out the missing temps sounds like a bad approach to me; getting the data, or working out a model to fill in the missing points, would make a lot more sense to me. PLUS, "Temperature" of what?!? Average ambient air at a predetermined height above the glacier over it's length? What about the other temperatures that come into play, as well as the precipitation over the course of the year, as well as the amount of sunlight hitting the glacier (amount sunlight reaching the Earth's surface has decreased markedly over this time period due to increased particulate matter)? Seems to me you need to explore for more explanatory variables, and try a different approach to fill in for missing data. Dave Stibbz wrote: Yeah that looks pretty good to me thanks! "Dave Curtis" wrote: Hi, OK, here's my first attempt. First I deleted all the rows for which you have no length change data. Then, with years in column A starting in A2 with length change in column B starting in B2 with temperature in Column C starting in C2. I assumed an original arbitrary length of 1000 so the length of the glacier over the years is given in D2 by =1000+SUM($B$2:B2) and copied down. A x y plot of temp against this length gives a big group of points, and a linear trendline slopes downwards with an R-squared of about 0.4. Are we getting anywhere? Dave "Stibbz" wrote: Ok here's some sample data for you to look at Glacier length change data Year Aquila 1896 -2 1897 1898 10 1899 1900 -12 1901 1902 1903 -16 1904 -9 1905 -8 1906 1907 -14 1908 1909 1910 1911 4 1912 1913 1914 -4 1915 1916 1917 -3 1918 1919 -13 1920 1921 -34 1922 1923 4 1924 -6 1925 50 1926 -34 1927 1928 -9 1929 1930 -16 1931 14 1932 -13 1933 1934 1935 1936 1937 -6 1938 2 1939 0 1940 11 1941 -3 1942 -7 1943 -2 1944 -6 1945 -105 1946 1947 -18 1948 1949 -22 1950 1951 -6 1952 -8 1953 -6 1954 -4 1955 -14 1956 -18 1957 -22 1958 22 1959 -21 1960 7 1961 -23 1962 -8 1963 -7 1964 -14 1965 -15 1966 6 1967 1 1968 -1 1969 -33 1970 -16 1971 1972 1973 1974 1975 1976 0 1977 0 1978 -15 1979 0 1980 4 1981 -9 1982 21 1983 18 1984 1985 -5 1986 -19 1987 1988 -3 1989 -6 1990 -12 1991 1992 -17 1993 -8 1994 -14 1995 -11 1996 -17 1997 -29 1998 -71 1999 -23.4 2000 -14 2001 -18.8 2002 -77.1 2003 -76.8 2004 -1.8 2005 -13.8 2006 -26.1 Temperature Data 1896 8.33 1897 9.34 1898 9.56 1899 9.76 1900 9.78 1901 8.62 1902 8.84 1903 8.98 1904 9.74 1905 9.03 1906 9.32 1907 9.00 1908 8.75 1909 8.46 1910 9.13 1911 10.06 1912 8.93 1913 9.52 1914 9.01 1915 9.32 1916 9.21 1917 8.41 1918 9.32 1919 9.05 1920 10.05 1921 10.24 1922 9.01 1923 9.88 1924 9.13 1925 9.39 1926 9.84 1927 9.63 1928 10.38 1929 9.28 1930 10.18 1931 9.17 1932 9.33 1933 9.19 1934 10.16 1935 9.78 1936 9.82 1937 10.16 1938 9.63 1939 9.33 1940 8.85 1941 8.94 1942 9.53 1943 10.55 1944 9.77 1945 10.52 1946 10.15 1947 11.08 1948 10.32 1949 10.82 1950 10.64 1951 9.83 1952 10.14 1953 9.92 1954 9.62 1955 9.94 1956 8.72 1957 9.68 1958 9.80 1959 10.40 1960 9.93 1961 10.77 1962 9.27 1963 8.70 1964 10.02 1965 9.03 1966 10.22 1967 9.88 1968 9.56 1969 9.33 1970 9.54 1971 9.49 1972 9.41 1973 9.34 1974 10.02 1975 9.89 1976 9.99 1977 10.07 1978 9.23 1979 9.92 1980 9.05 1981 9.78 1982 10.60 1983 10.47 1984 9.74 1985 9.36 1986 10.03 1987 10.07 1988 10.71 1989 10.90 1990 10.98 1991 10.36 1992 10.79 1993 10.28 1994 11.84 1995 10.85 1996 9.99 1997 10.94 1998 10.83 1999 10.71 2000 11.37 2001 11.07 2002 11.54 2003 11.77 2004 11.18 2005 10.69 2006 11.18 "Dave Curtis" wrote: I agree there's a danger of drawing erroneous conclusions, just because your data happens to fit part of, say a fourth order polynomial. Is therebno published data on a theoretical relationship between the two variables? Why not post a sample your data? Dave "Stibbz" wrote: No I haven't used any of the non-linear trendlines as I am unsure on which one is appropriate to use "Dave Curtis" wrote: Hi, Presumably you have tried plotting the data on an x-y chart and tried adding any of the non-linear trendlines? Dave "Stibbz" wrote: I was wondering which statistical function would be the best to use when trying to see if their is a relationship between temperature and glacier length change. I have used the CORREL function to try and assess there correlation but will this give me bad answers as there isn't a straight line relationship between the two!?!? -- Please keep response(s) solely within this thread. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Statistical - text function (counting) | Excel Worksheet Functions | |||
| statistical function with range referencs | Excel Worksheet Functions | |||
| Does excel have a function for statistical hypotosis testing? | Excel Worksheet Functions | |||
| Definition of a statistical function (CQC) in Excel | Excel Worksheet Functions | |||
| Statistical Function | Excel Worksheet Functions | |||
Linear Mode
