Questions and Answers of Microsoft Excel Data Analysis 7th

Use the data in the file named Stock.xlsx to construct histograms for monthly returns for GE and Intel. A 49 50 51 Date B Microsoft GE min max Intel D E F -0.240320429 -0.202509 0.276619107
The file named Problem12data.xlsx contains in a single cell a state’s name and population. Use Excel formulas to extract the population to a new column. If a new row is added, then your formulas
The file named Problem13data.xlsx contains sales data on 2,000 companies. Write formulas to count the number of banking firms and calculate the fraction of banking revenue earned by banks making at
Add a spin button for the car NPV example that allows you to vary the tax rate between 30 percent and 50 percent.
Add a spin button for the car NPV example that allows you to vary the interest rate between 5 percent and 20 percent.
The Format Control dialog box allows a minimum value of 0. Despite this limitation, can you figure out a way to use a spin button to vary sales growth between –10 percent and 20 percent?
For the weekend example used in Chapter 24, “Conditional formatting,” set up a check box that turns conditional formatting on or off.
In the financial formulas described in Chapter 10, “More Excel financial functions,” I used a last argument of 1 to indicate end-of-month cash flows and 0 to indicate beginning-of-month cash
Using the data for Problem 14 in Chapter 22, “The OFFSET function,” create a spinner that lets you graph the last few months of sales (ranging from three to eight months).Data from in problem
The file named Suppliers.xlsx contains the unit price you pay to each of your suppliers and the units purchased. Create a list box that allows you to pick the supplier and that also returns the unit
The file named Kingslineups.docx contains performance ratings for some of the Sacramento Kings lineups. Import this data into Excel.
In the example discussed in this chapter, the time each lineup played (column H) ends with an m. Modify the file so that the time played by each lineup is an actual number.
The website https://www.loc.gov/rr/print/list/057_chron.html contains information about each US president. Create a web query that places each president’s start year and end year in a separate
Use the data in the file named Stock.xlsx to generate descriptive statistics for Intel and GE stock. A 49 50 51 Date B Microsoft GE min max Intel D E F -0.240320429 -0.202509 0.276619107
Use your answer to Problem 1 to compare the monthly returns on Intel and GE stock.Data from in Problem 1Use the data in the file named Stock.xlsx to generate descriptive statistics for Intel and GE
The website https://en.wikipedia.org/wiki/List_of_largest_companies_in_the_United_States_by_revenue contains data on the 500 largest US companies. Create a web query that includes the company name,
Using the website listed in Problem 2, create a web query that ranks firms according to revenue per employee. You might need to delete the first row (from the Home tab) and, in the Transform tab,
The workbook PQPROBLM4DATA.XLSX contains product sales for January through April in tabular format. Use the Power Query Editor to flatten this table.
The workbook PQPROBLEM5DATA.XLSX contains product sales in a flat file. Use the Power Query Editor to put the data in a tabular format.
The workbook PQPROBLEM6DATA.XLSX contains statistics on NBA players. Use the Power Query Editor to insert each player’s team and also to place their individual statistics in separate rows.
Use the LARGE or SMALL function to sort the annual stock returns in the file Historicalinvest.xlsx. What advantage does this method of sorting have over clicking the Sort button? 6 7
Use descriptive statistics to compare the annual returns on stocks, T-bills, and corporate bonds. Use the data contained in the file Historicalinvest.xlsx. 6 7
The workbook PQPROBLEM7DATA.XLSX contains sales data from all four quarters of a year in separate worksheets. Use the Power Query Editor to combine the data.
Use the geometric mean to estimate the mean annual return on stocks, bonds, and T-bills in the file named Historicalinvest.xlsx. 6 7
Use the data in the file named Historicalinvest2009.xlsx to create histograms for annual returns on stocks and bonds. Then compare the annual returns of these stocks and bonds.
The file named Deming.xlsx contains the measured diameter (in inches) for 500 rods produced by Rodco, as reported by the production foreman. A rod is considered acceptable if it is at least 1 inch in
Compare the mean, median, and trimmed mean (trimming 10 percent of the data) of the annual returns on stocks, T-bills, and corporate bonds given in the file named Historicalinvest.xlsx. 6 7
The file named Unemployment.xlsx contains monthly US unemployment rates. Create a histogram from the data. Are the unemployment rates symmetric or skewed?
The file named Teams.xlsx contains runs scored by Major League Baseball teams during a season. Create a histogram from the data. Are the runs scored symmetric or skewed?
The file named Nflpoints.xlsx contains points scored by NFL teams during a season. Create a histogram from the data. Are the points scored symmetric or skewed?
Using the data in the file named Problem7data.xlsx, create a histogram that summarizes the heights of American men.
The data in the file named Problem8data.xlsx contains the points scored by each Division I NCAA football team during the 2015 season. Create a histogram to summarize this data. Does the data appear
The data in the file named Problem9data.xlsx contains income of families in Smalltown, USA. Create a Pareto chart to summarize the family incomes.
City Power & Light produces voltage-regulating equipment in New York and ships the equipment to Chicago. A voltage regulator is considered acceptable if it can hold a voltage of 25 to 75 volts.
In the file named Decadeincome.xlsx, you are given a sample of incomes (in thousands of 1980 dollars) for a set of families sampled in 1980 and 1990. Assume that these families are representative of
In 1969 and 1970, eligibility for the US armed-services draft was determined on the basis of a draft-lottery number. The number was determined by birth date. A total of 366 balls, one for each
In the Makeupsort.xlsx file, sort the sales data alphabetically by location, then by product type, then by name, then by date of sale, and finally by units sold. E 3 Trans
The file named Jordan.xlsx gives (hypothetical) starting salaries of all 1984 geography graduates from the University of North Carolina (UNC). What is your best estimate of a “typical” starting
How many units of lip gloss did Colleen sell outside the West region? G H 4 Trans Number Name 5 6 7 8 9 10 11 123 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 1 Betsy 2 Hallagan 3 Ashley 4
Using the data in the file named Makeupdb.xlsx, how many units of lip gloss did Zaret sell during 2004 and 2005? G H 4 Trans Number
Create a data table that contains each person’s total revenue and units sold. G H 4 Trans Number Name 5 6 7 8 9 10 11 123 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 1 Betsy 2 Hallagan 3
The file Dow.xlsx contains monthly returns on 30 Dow stocks during the past 20 years. Use this data to determine the three stocks with the largest mean monthly returns.
Using the Dow.xlsx data, determine the three stocks with the most risk or variability.
Using the Dow.xlsx data, determine the three stocks with the highest skew. 1 2 3 4 5 69 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 A Date AA B AIG C AXP D BA E C F G 0 CAT DD
Use the data in the file named Makeupdb.xlsx to create a data table that shows the average per-unit revenue generated by each person for sales in which the average per-unit price exceeded $3.30.
Using the Dow.xlsx data, determine how the trimmed-mean returns (trim off 10 percent of the returns) differ from the overall mean returns.
The file named Incomedata.xlsx contains incomes of a representative sample of Americans in the years 1975, 1985, 1995, and 2005. Describe how US personal income has changed over this time period.
The file named Coltsdata.xlsx contains yards gained by the 2006 Indianapolis Colts on each rushing and passing play. Describe how the outcomes of rushing plays and passing plays differ.
In the file Makeupdb.xlsx, find all the unique combinations of name, product, and location occurring during the first three months of 2006. G H 4 Trans Number
In the file named Problem17datat.xlsx, you are given daily returns on Facebook stock. Use this data to answer the following questions: Do Facebook stock returns exhibit significant
One theory in brain science states that the level of dopamine in a person’s nervous system determines whether someone will exhibit psychotic behavior. In the file named Problem18data.xlsx, you are
Participants in a group of 508 people were asked to estimate the percentage of African nations that are members of the United Nations. A wheel of fortune containing the numbers 1 through 100 was spun
Use the data in the file Historicalinvest.xlsx to create a count of the number of years in which stock, bond, and T-bill returns are from –20 percent through –15 percent, –15 percent through
The Sortday.xlsx file contains hours worked on different days of the week. Sort the data so that the Monday data is followed by the Tuesday data, and so on.
In the Makeupdb.xlsx file, what was the average number of lipstick units sold by Jen in the East region? G H 4 Trans Number Name 5 1 Betsy 2 Hallagan 3 Ashley 4 Hallagan 5 Zaret 6 Colleen 7
The Sorticons.xlsx file contains annual investment returns with an up arrow used to indicate good years, a horizontal arrow used to indicate average years, and a red arrow used to indicate bad years.
In the Makeupdb.xlsx file, how many dollars of sales were made by Emilee or in the East region? G H 4 Trans Number Name 5 1 Betsy 2 Hallagan 3 Ashley 4 Hallagan 5 Zaret 6 Colleen 7 Cristina 8
The file named Makeupsortfont.xlsx contains our makeup data with certain dates shown in blue, red, or brown font. Sort the data so that the brown dates are on top, followed by the red dates and then
In the Makeupdb.xlsx file, how many dollars’ worth of lip gloss did Jen sell? G H 4 Trans Number Name 5 1 Betsy 2 Hallagan 3 Ashley 4 Hallagan 5 Zaret 6 Colleen 7 Cristina 8 Colleen 9
Sort the sales data in workbook Chapter32Problem5data.xlsx in ascending order of units sold and, in case of ties, alphabetically A–Z by product name.
Perform a case-sensitive A–Z sort by name on the data in the workbook Chapter32Problem6data.xlsx.
In the Makeupdb.xlsx file, how many dollars’ worth of lipstick were sold by Colleen or Zaret in the East region? G H 4 Trans Number
List all the salespeople.Each row gives the salesperson, product sold, units sold, and revenue from a sales transaction. In all cases, make sure your answers will automatically update if new data is
List all salesperson/product/units combinations.Each row gives the salesperson, product sold, units sold, and revenue from a sales transaction. In all cases, make sure your answers will automatically
Use the data in the file named Sales.xlsx to determine the following:Total dollar sales in the MidwestTotal dollars that Heather sold in the EastTotal dollars that Heather sold or that were sold in
List all salesperson/product/units combinations that occur exactly once.Each row gives the salesperson, product sold, units sold, and revenue from a sales transaction. In all cases, make sure your
The file named Housepricedata.xlsx contains the following information for selected homes:Square footagePriceNumber of bathroomsNumber of bedroomsUse this information to answer the following
The file named Deciles.xlsx contains the unpaid balance for 20 accounts. Use DBASE functions to compute the total unpaid balances in each decile. 8. In the file Makeupdb.xlsx, find all the unique
Find all transactions where Liam sold candy.Each row gives the salesperson, product sold, units sold, and revenue from a sales transaction. In all cases, make sure your answers will automatically
Find all transactions where Ava was the salesperson or magazines were sold.Each row gives the salesperson, product sold, units sold, and revenue from a sales transaction. In all cases, make sure your
The Duplicate worksheet in the Chapter35data.xlsx file contains two lists of names. Use an array formula to count the number of names appearing on both lists.
The Find Errors worksheet in the Chapter35data.xlsx file contains some calculations. Use an array formula to count the number of cells containing errors.
Sort the data in descending order of units, breaking ties based on dollars in descending order.Each row gives the salesperson, product sold, units sold, and revenue from a sales transaction. In all
Sort the transactions in descending order of units.Each row gives the salesperson, product sold, units sold, and revenue from a sales transaction. In all cases, make sure your answers will
The Sales worksheet in the Chapter35data.xlsx file contains 48 months of sales at a toy store. Create an array formula to add (beginning with Month 3) every fifth month of sales.
Also in the Sales worksheet in the Chapter35data.xlsx file, use an array function to compute the third, fifth, and seventh power of each month’s sales.
The Product worksheet in the Chapter35data.xlsx file contains sales during April through August of products 1 through 7. Sales for each product are listed in the same column as each month. Rearrange
Find all transactions involving 39 units and sort them in descending order of revenue.Each row gives the salesperson, product sold, units sold, and revenue from a sales transaction. In all cases,
The Lillydata.xlsx file contains information from a market-research survey that was used to gather insights to aid in designing a new blood-pressure drug. Fifteen experts (six from Eli Lilly and
The file Arrayexam1data.xlsx contains sales data by company and date. Your job is to break the sales down on a quarterly basis by using array formulas. Summarize the data using only array formulas by
Explain why entering the formula =SUM(1/COUNTIF(Info,Info)) will yield the number of unique entries in the range Info. Apply this formula to the data in the file named Unique.xlsx and verify that it
The file Salaries.xlsx contains the salaries of NBA players. Write an array formula that adds the four largest player salaries.
For an N-month $100,000 loan with monthly payments at a 10 percent annual rate, use the SEQUENCE function to determine each month’s contribution to principal and interest. If you change the number
Use the SEQUENCE function to list all integers from 1 to 10,000 in 1,000 rows and 10 columns.Each row gives the salesperson, product sold, units sold, and revenue from a sales transaction. In all
You want to enter nonnegative whole numbers in the cell range C1:C20. Set up data validation to ensure that each entry is a nonnegative whole number.
You are entering the dates of transactions that occurred during July 2004 in the cell range C1:C15. Set up data validation to ensure that each date entered occurs in July 2004.
Using the List option in the Allow drop-down menu found in the Settings tab of the Data Validation dialog box, you can generate an error message if a value that is not included in a list is entered
With the Text Length option in the Allow drop-down menu found in the Settings tab of the Data Validation dialog box, you can generate an error message when the number of characters in a cell does not
You are entering employee names in the cell range A1:A10. Use data validation to ensure that no employee’s name is entered more than twice.
Suppose you want every entry in the cell range B2:B15 to contain text and not a numerical value. Use data validation to ensure that entering a numerical value returns an error.
Set up data validation to ensure that all numbers typed in column E will contain exactly two decimal places.
The file named Latitude.xlsx contains a formula to compute the distance between two cities using their latitude and longitude. The file also contains the latitude and longitude of various US cities.
Ensure that if new cities are added to the list of cities in Problem 9, the drop-down menu will include them.Data from in problem 9The file named Latitude.xlsx contains a formula to compute the
In the file named Weekendformatting.xlsx, highlight all weekdays in red, and highlight in blue all days that occur in the first 10 days of the month. Which color takes precedence when both colors are
The file named Test.xlsx contains exam scores for students. The top 10 students received an A, the next 20 students received a B, and all other students received a C. Highlight the A grades in red,
Using the data in the file named Sandp.xlsx, use conditional formatting in the following situations:Format in bold each month in which the value of the S&P index increased, and format with an
For our drug cost example (see the file named Offsetcost.xlsx from Chapter 22, “The OFFSET function,”), format the worksheet so that all Phase 1 costs are displayed in red, all Phase 2 costs are
The file Names.xlsx contains a list of names. Highlight all the duplicates in green and all the names containing Ja in red. Name
In the file named Historicalinvest.xlsx, set up conditional formatting with three icons so that 10 percent of returns have an up arrow, 10 percent have a down arrow, and 80 percent have a horizontal
Write a formula that will total the entries in the cell range G2:K2, even if you insert one or more columns between columns G and K.

Showing 200 - 300 of 878