Week 5 Final Assignment >> Excel Skills for Business: Advanced
1. This week’s assignment will test your ability to use the INDIRECT, ADDRESS, OFFSET and INDEX functions in ways focussed on extracting values from tables of data. Follow the instructions carefully and apply what you have seen in the videos and you should do fine. Write your formulas in the yellow and green cells and answer the questions as you go. Do not insert or delete any rows or columns because then the references in the instructions may not align with your worksheet, and the Check Sum calculations may not give the intended results.
Part 1Download and open the assignment workbook.
Go to the Part1 worksheet. This section will focus on extracting exchange rates from a provided year, month and currency. Look at the sheets 2012 through 2016 to see where the data is and the layout of the data. Note that the sheets are designed identically and that cells B2:K13 of each sheet has been given a defined name called Rates_XXXX where XXXX is the year. When you are done, go back to the sheet Part1.
The blue cells J5, J7 and J9 contain dropdown lists where we can select which value we want to look up. The existing setting should be 2016, December, SGD. If it isn’t, change those cells to those values. Now we are going to write formulas in the green cells, ultimately arriving at 3 different ways of using INDIRECT to extract the exchange rate. Our formulas need to be flexible so that if the blue cell input values change, our answer will change too.
We will start with a few helper cells – intermediate calculations that will be used by other calculations. In cells L7 and L9, write a formula (using the MATCH function is recommended) to return the position / number of the chosen Month (J7) and Currency (J9) from the list of Months and Currencies. You should get 12 for December and 10 for SGD.
In cell L11, use the ADDRESS function and references to the helper values in cells L7 and L9 to return the cell address (don’t worry about the sheet name, that will come later) of the chosen month/currency combination from one of the yearly data sheets. Because our data starts at cell B2, meaning the first month in the list is in the 2nd row, and the first currency in the list is in the second column, we will need to add 1 to each of the first two arguments in our ADDRESS function. When you are done, you should have the value $K$13 in cell L11. We will use this in the next formula we write.
In cell J14, write a formula using the INDIRECT function, plus references to
the year in cell J5 to specify the worksheet andthe address in cell L11 to specify the cell addressthat will return the chosen exchange rate. Remember to put an exclamation point in between the two references. (HINT: you will need to use the string &”!”& somewhere in the formula)
Next, in cell J15, let’s achieve the same thing but using an INDEX function instead of our ADDRESS function helper cell. Here we are going to take advantage of the name Rates_2016 that has been applied to cells B2:K13 of the 2016 sheet (and similarly for the other years). If the first argument of our INDEX function is INDIRECT(“Rates_”&J5) that will reference cells B2:K13 of our chosen year as the array to index in to. We can then refer to the helper cells at L7 and L9 to specify the row and column we want within that array. With this knowledge, write a formula beginning with =INDEX(INDIRECT( to return the exchange rate for the chosen dropdown values.
For the last method in cell J16, we will use OFFSET and INDIRECT. If the first argument of our OFFSET function is INDIRECT(J5&”!A1″) that will reference cell A1 of our chosen year sheet, and we can then offset that cell by the desired number of rows and columns specified in cells L7 and L9. Write a formula beginning with =OFFSET that will return the exchange rate.
When you are done, you should have the same value in all of the cells J14:J16, and they should be dynamic. Change the blue cell inputs to be 2015, March, CAD and check that your calculated exchange rates update to new values (they should still all be the same). Submit the answer from the Check Sum at cell J17 when 2015, March, CAD are the inputs.
36757
2. Our next challenge is to write a formula in cell J23 that can be dragged across and down J23:M25 to return the exchange rate for the desired currency (J20), year (column I) and month (row 22), and that will update when a new currency is chosen at J20. Be careful though – the years and months needed will change depending on what currency is selected!
Start by filling in the helper cell at L20, and then use whichever INDIRECT method you most prefer in cells J23:M25 to fill in the table. Make sure it updates when the currency at cell J20 changes. Use this table to answer the next four questions.
Set cell J20 to AUD. What is the value of the check sum in cell J27?
3235
3. Set cell J20 to EUR. What is the value of the check sum in cell J27?
2455
4. Set cell J20 to GBP. What is the value of the check sum in cell J27?
3578
5. Set cell J20 to JPY. What is the value of the check sum in cell J27?
2453
6. Part 2You’re done with Part 1! Now navigate to the worksheet Part2. In columns B:D you will see NASDAQ data for Microsoft from the years 2013 to 2016, with the date, price and volume traded.
This data is presented with the most recent dates at the top. The first thing we want to do is flip this around, so the oldest date is at the top and the most recent at the bottom. This is a pretty common problem when working with dated data sets in Excel. Because we already know that it is sorted newest to oldest and not completely random, we can use a simpler set of formulas to sort this oldest to newest than if it was completely random. We will start by filling in some helper cells in column F, and the rest should easily fall into place.
In cell F5, write =COUNT(B5:B1012). This will tell us how many entries there are. Then in F6, write =F5-1 and apply that formula down to cell F1012. We should have a list of numbers from 1008 to 1 in decreasing order.
In columns G, H and I, use either the INDEX function or OFFSET function to write a formula that references columns B, C and D respectively, plus the helper value in column F, that will return the data sorted in an oldest to newest format. When you are done, submit the value of the Check Sum at cell H3.
20430
7. For the next 5 questions, we are interested in monthly summaries of the daily data, organised by calendar month. We will make reference to our newly sorted oldest to newest data. Beginning at column K, rows 5:52 contain 48 rows (1 for each calendar month) that we will fill in with aggregate data from that particular month. There are some clever ways we can find this using INDEX or OFFSET. In both cases, we are going to benefit from helper cells (columns M and N) that tell us the position (from 1st spot to 1008th spot) of the start and end of each month in the daily data. To begin, fill in the helper cells as follows: In cell M5, type 1. In cell N5, type =MATCH(L5,$G$5:$G$1012,1) and apply this down the column. In cell M6, type =N5+1 and apply this down the column. Can you see what the MATCH formula is doing and why this works? Cells M7:N7 should contain 41 and 60. This means that the first March 2013 data point is the 41st in the list of G5:G1012, and the final March 2013 data point is the 60th in the list of G5:G1012. We will need these position numbers for the next step to make our formulas easier.
In column O, write a formula using OFFSET that returns the sum of prices for the designated month. Remember that OFFSET can return a range of cells. If that OFFSET function is then wrapped by a SUM function, we will get the sum of the entire range that was returned by OFFSET. Start by writing in cell O5 “=SUM(OFFSET(H$5,” and fill in the rest of the arguments and drag the formula down. Use the helper columns and M and N for the second and fourth arguments. The third argument will be zero. When you are done, submit the value of the check sum at cell O3.
4382
8. Before we move on, consider this. We could have performed our sum-by-month calculation in column O using two SUMIFS functions, with one subtracted from the other (e.g. the sum of prices from March 2013 is the sum of all prices <= 31 March 2013 minus the sum of all prices < 1 March 2013. However, the advantage of doing it the way we did with OFFSET, or the way we will do next with INDEX, is that it is much faster to calculate than SUMIFS over large data sets, and gives us the flexibility to calculate other items like averages and not just sums. Finding an average value over a month, especially when we don’t know how many items are in each month, would be much more complex without using our OFFSET or INDEX approach. Now let’s move on…
In column P, we are going to do a similar thing, but this time finding the AVERAGE. We will also use INDEX instead of OFFSET. We are going to take advantage of the fact that if an INDEX function returns a single cell, we can put that INDEX function on either side of a colon “:” and Excel will treat the INDEX function like a cell address rather than a cell value. In other words, the construction =AVERAGE(INDEX(…):INDEX(…)) will give us the average of the range that starts and ends at the cells designated by the two INDEX functions. This is just another way of achieving what we did in the previous column with OFFSET.
In cell P5, write a formula with the form =AVERAGE(INDEX(…):INDEX(…)) and apply it down the column to calculate the average price for each month. Remember that a formula =INDEX([column of cells],[position]) will return a single cell from the column, in the designated position. When you are done, submit the value of the check sum from cell P3.
7912
9.In column Q, write a formula (using either OFFSET or INDEX as you prefer) to calculate the average volume of each month. When you are done, submit the value of the check sum from cell Q3.
3725
10. In cell T6, use a MAX function to find the largest monthly average volume, and then in T7 write a formula (we suggest using INDEX and MATCH) to find the month that this maximum average volume occurs in. Submit as your answer the month and year, typed as “month year”. For example, if the maximum value was in cell Q7, you would type “March 2013” (without quotations). Use the English name for the month.
April 2013
11. In cell T9, use a LARGE function with 20 as the second argument to find the 20th highest average monthly price. In cell T10, write a formula to identify what month this occurs in. Submit as your answer the month and year, typed in the format of “March 2013” just like the previous question.
Save your work. Well done.
June 2015