Assignment$$

Using the provided Excel file $($S&P $500$ Data$),$ complete the following actions.$$

$1.$ Create four new worksheets named Q$1,$ Q$2,$ Q$3,$ and Q$4.$ Copy the original block of data $($A$1$:F$252)$ to each of the four new worksheets.

$2.$ In worksheet Q$1,$ leave the data just as you copied it$.$ In column G$,$ use an IF function to show the text "gain streak" if the day's closing price was higher than the previous day's, and the previous day's close was higher than the day before. Ignore days where the sheet lacks data about those previous days.

$3.$ In worksheet Q$2,$ sort the data by date, in ascending order $($i$.$e$.,$ the top row should have the earliest date and the bottom row should have the last date$).$ Starting in cell I$2,$ manually make a table for each month $($November $2023$ through October $2024).$ Each row should have the month $($in column I$),$ then the average closing price for that month $($in column J$),$ then the average volume for that month $($in column K$).$ Place appropriate labels $($like Month, Avg. Close, and Avg. Volume$)$ above the three columns.

$4.$ In worksheet Q$3,$ again sort the data by date in ascending order. In column G$,$ calculate each day's price change $($the closing price minus the opening price$).$ In column H$,$ calculate the day's price change as a percentage $($the price change divided by the opening price$).$ In column I, use an IF function to show the text "gain" if the price went up for the day and "loss" if the closing price was below the opening price.

$5.$ In worksheet Q$4,$ sort the data by volume, in descending order $($i$.$e$.,$ the top row should have the highest volume$).$ In cell I$2,$ write a formula to display the average volume from the ten highest$-$volume days. In cell K$2,$ write a formula to display the average volume from the ten lowest$-$volume days. In cell M$2,$ write a formula to display the average volume from all the other days $($i$.$e$.,$ excluding the ten highest and ten lowest volume days$).$ Place appropriate labels $($like High $10,$ Low $10,$ and Middle$)$ above the three formulas.