Need formulas for ones circled in red
Table is titled S&P 500
column should based on NBER look like Business Cycle 1920s Business Cycle Decade Day of WkEnd of Month based on Previous Adj Close End of Quarter %Chg Price %Chg Volume 0 0 0 0 0 Adjusted for stock splits & dividends Date Open High Low Close Adj Close Volume 12/30/1927 17.66 17.66 17.66 17.66 17.66 1/3/1928 17.76 17.76 17.76 17.76 17.76 1/4/1928 17.719999 17.719999 17.719999 17.719999 17.719999 1/5/1928 17.549999 17.549999 17.549999 17.549999 17.549999 1/6/1928 17.66 17.66 17.66 17.66 17.66 1/9/1928 17.5 17.5 17.5 17.5 17.5 1/10/1928 17.370001 17.370001 17.370001 17.370001 17.370001 1/11/1928 17.35 17.35 17.35 17.35 17.35 1/12/1928 17.469999 17.469999 17.469999 17.469999 17.469999 1/13/1928 17.58 17.58 17.58 17.58 17.58 1/16/1928 17.290001 17.290001 17.290001 17.290001 17.290001 1/17/1928 17.299999 17.299999 17.299999 17.299999 17.299999 1/18/1928 17.26 17.26 17.26 17.26 17.26 1/19/1928 17.379999 17.379999 17.379999 17.379999 17.379999 1/20/1928 17.48 17.48 17.48 17.48 17.48 1/23/1928 17.639999 17.639999 17.639999 17.639999 17.639999 1/24/1928 17.709999 17.709999 17.709999 17.709999 17.709999 0 0 0 0 0 0 0 0 0 0 0 Note: Month numbers start in January 1800 Peak Trough Duration, Duration, Duration, Duration, Peak Trough Peak month Trough month month month peak to trough to peak to trough to end-of-month end-of-month number number trough peak peak trough Date Date December 1854 660 June 1857 December 1858 690 708 18 30 48 October 1860 June 1861 730 738 8 22 40 30 April 1865 December 1867 784 816 32 46 54 78 June 1869 December 1870 834 852 18 18 50 36 October 1873 March 1879 886 951 65 34 52 99 March 1882 May 1885 987 1025 38 36 101 74 March 1887 April 1888 1047 1060 13 22 60 35 July 1890 May 1891 1087 1097 10 27 40 37 January 1893 June 1894 1117 1134 17 20 30 37 December 1895 June 1897 1152 1170 18 18 35 36 June 1899 December 1900 1194 1212 18 24 42 42 September 1902 August 1904 1233 1256 23 21 39 44 May 1907 June 1908 1289 1302 13 33 56 46 January 1910 January 1912 1321 1345 24 19 32 43 January 1913 December 1914 1357 1380 23 12 36 35 August 1918 March 1919 1424 1431 7 44 67 51 January 1920 July 1921 1441 1459 18 10 17 28 1495 14 22 40 36 1481 1522 1556 1535 13 27 41 40 64 1599 43 21 34 1662 13 50 93 63 1750 8 80 93 88 1798 11 37 45 48 1853 10 45 56 55 1900 1649 1742 1787 1843 1892 1924 2040 2087 2161 8 39 49 May 1923 October 1926 August 1929 May 1937 February 1945 November 1948 July 1953 August 1957 April 1960 December 1969 November 1973 January 1980 July 1981 July 1990 March 2001 December 2007 February 2020 47 July 1924 November 1927 March 1933 June 1938 October 1945 October 1949 May 1954 April 1958 February 1961 November 1970 March 1975 July 1980 November 1982 March 1991 November 2001 June 2009 10 24 32 34 11 106 116 117 1934 2051 2103 2167 16 36 47 52 6 58 74 64 2179 2195 16 12 18 28 2295 8 92 108 100 2287 2415 2423 8 120 128 128 2496 2514 18 73 81 91 2642 128 146 17.5 41.4 59.2 56.2 21.6 26.6 48.9 48.2 1854-2020 (34 cycles) 1854-1919 (16 cycles) 1919-1945 (6 cycles) 1945-2020 (12 cycles) 18.2 53.0 53.2 35.0 64.2 11.1 75.0 69.5 After reading the directions below, click the "Begin Work" button and answer the questions to create the template with the multiple worksheets needed. Step #1: WRITE FORMULAS in the NBER Business Cycle worksheet to display: The Peak End-of-Month Date (Column K) for the given row based on the Peak Month (Column C or E). The Trough End-of-Month Date (Column L) for the given row based on the Trough Month (Column C or F). Note: Month numbers start in January 1800 (e.g. February 1800 is month 2) Grey Cells need formulas. Since Excel dates cannot be in date format before 1900, the first several cells have been blacked-out (no formulas required here). Hint: Write the first formula in L15, and copy it down through Column L and then over to Column K. DATEVALUE can make it easy. Step #2: Begin this only after completing Step #1 ... WRITE FORMULAS in the S&P 500 table to display: "Expansion" or "Contraction" in the "Business Cycle" column (i.e., column H in SP500Table) based on the data in the NBER Business Cycle worksheet NOTE: Use the Month End as the exact date for the Peak & Trough when determining Expansion & Contraction Expansion month meets following condition: Trough Date
