BF 174 HW07: Goal Seek, Solver, Intermediate Charts
Various Functions: VLOOKUP, COUNT*, *A, EXACT
1. Before beginning:
- Make sure you have changed your “Region and Language” format from Turkish to English (USA)
- Open Excel, and add Solver feature as follows:
- Click File/Options/Add-Ins/Go
- Check Solver Add-in, then click “OK”
- In the “Data” tab, on the right, see that you have “Solver”
2. Open BF174-HW07_file.xlsx – save as: username07.xlsx
In Goal Seek
3. In row 14, find the four totals for BOB, AYŞE, JANET and CEM
4. In rows 15:16, use Goal Seek to find what is the percent of the total income that each person must save in order to have 3000
5. SAVE
In GPA
6. In D2, use VLOOKUP and the green table (HINT: without its header) to find COMMENT for the value in C2 + 1 – copy down for the rest of column D
7. In E2, use VLOOKUP and the green table to find MARK for the value in C2 + 1
– copy down for the rest of column E
8. For the next VLOOKUP question, you need to use the grey table; remember that for VLOOKUP tables, the first column must be sorted in alphabetical order – so, sort the whole grey table, by the first column, in A to Z order, with a header
9. After sorting the grey table, in F2, use VLOOKUP and the grey table to find QUALITY PTS FOR MARK for the MARK in E2 – copy down for the rest of column F
10. In H2, find QUALITY PTS FOR COURSE (HINT: F2*G2) – copy down for the rest of column H
11. In G16 and H16, find totals
12. In H17, find the GPA (HINT: H16 / G16) – round to 2 decimals
13. SAVE
In Solver[1]
14. Maximize P = 10x + 12y, subject to the constraints: x + y <= 60, x – 2y >= 0, and x, y > 0
– when you are done, keep solver solution – do not show any Reports
15. In B16, show why an example of how a different number does not work for x
16. SAVE
In COUNT-, -A
17. In B22, use mixed references and a function to show how many numbers are in B2:B21; copy down to B23:B26; edit B23:B26 to answer the other questions in A23:A26; make numbers in B25:B26 show 2 decimals; then, copy B22:B26 across for columns C:G
18. SAVE
In SAME OR NOT
19. In E2, use a function to find if NAMES in A2 and C2 are exactly the same; copy down to E51
20. In F2, use a function to find if SURNAMES in B2 and D2 are exactly the same; copy down to F51
21. SAVE
In GDP DATA
22. In C2, divide B2 by a million (1000000) – add one decimal place; in E2, divide D2 by 1000000 – add one decimal place; copy values down from C2 to C60, then copy/paste over to show values only; copy values down from E2 to E60, then copy/paste over to show values only; finally, delete the columns with the large numbers (column D and column B)
23. Select A1:C60; insert a 2D Line Chart (Line); make the chart be in D2:N17; choose Chart Layout 1
24. To the Chart Title: make size 14; then type: Nominal and Real GDP (1998 to 2012-Q3)
25. To the Y-Axis Title, type: TL (millions)
26. Look at the legend; see that “Real” is red and “Nominal” is blue – then, delete the legend
27. To the Plot Area: remove the horizontal gridlines; make the plot area about 1 column narrower (from the right) and about 3 rows shorter (from the bottom) – then, click out of chart and SAVE
28. To the right of the blue line, insert a text box (bold) that says: Nominal
29. Between the blue line and Nominal, use insert shape to insert a black arrow – group the text and arrow
30. Copy paste the grouped text and arrow to be to the right of the red line – change Nominal to Real
31. To the Y-axis: make the major units use intervals of 100; make the numbers have no decimals
32. To the X-axis: specify interval unit of 4, rotate all text 270 degrees
33. Below X-axis: insert a text box that says: Source: Turkish Statistical Institute – bold
34. Change the blue & red lines to be black – make one of the lines: Line Style/Dash Type: Square Dot
35. Make all of the text in the chart (including the 3 text boxes): Times New Roman
NOTE: I selected the chart, then each of the 3 text boxes
36. Make the Y- and X-axes bold
37. Click inside the chart – look at Print Preview
38. Click outside the chart – look at Print Preview
39. SAVE
Last printed: 3/24/2017 3:42 PM Page 2 of 2
[1] From Introductory Mathematical Analysis for Business, Economics, and the Life and Social Sciences, 10th Edition, Chapter 7, p. 315