Questions and Answers of Microsoft Excel Data Analysis 7th

What is the serial format for February 14, 1950?
The file named Emailproblem.xlsx gives first and last names of several new Microsoft employees. To create an email address for each employee, you need to follow the first letter of the employee’s
The workbook named Quarterlygnpdata.xlsx contains quarterly GNP data for the United States (in billions of 1996 dollars). Extract this data to three separate columns in which the first column
The file named FootballProblem8.xlsx shows statistics on NFL quarterbacks. Enter a QB’s name in cell G3 and a statistic in cell H3. Then write a formula that returns in cell I3 the QB’s
In the file named Baseballproblem7.xlsx, you are given Major League Baseball teams’ statistics. Enter a team’s name in cell I2 and a statistic in cell J2. Write a formula in cell K2 that returns
The file named Salesdata.xlsx contains the number of computers in a store sold by each salesperson. Create a formula that returns the units sold by a given salesperson. 1 2 345 6700
The file named Buslist.xlsx gives the amount of time between bus arrivals (in minutes) at 45th Street and Park Avenue in New York City. Write a formula that, for any arrival time after the first bus,
The file named Matchthemax.xlsx gives the product ID codes and unit sales for 265 products. Use the MATCH function in a formula that yields the product ID code of the product with the largest unit
The file named Matchtype1.xlsx lists in chronological order the dollar amounts of 30 transactions. Write a formula that yields the first transaction for which the total revenue to date exceeds
Using the distances between US cities given in the file named Index.xlsx, write a formula using the MATCH function to determine (based on the names of the cities) the distance between any two of the
“Lookup functions.” Here is the problem again: The file named Employees.xlsx contains the rating (on a 0–10 scale) that each of 35 workers has given to three jobs. The file also gives the job
Jerry Jones and the Dallas Cowboys are embarking on a road trip that takes them to Chicago, Denver, Los Angeles, Phoenix, and Seattle. How many miles will they travel on this road trip?
Use the INDEX function to compute the total distance from Dallas to the seven other cities listed in Figure 4-1. 123 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 A B Boston-Denver Seattle- Miami 1
The file named Assign.xlsx gives the assignment of workers to four groups. The suitability of each worker for each group (on a scale from 0 to 10) is also given. Write a formula that gives the
The file Grades.xlsx contains students’ grades on an exam. Suppose the curve is as follows:Use VLOOKUP formulas to return each student’s letter grade on this exam. Score Below
The file named Lookupmultiplecolumns.xlsx contains information about several sales made at an electronics store. A salesperson’s name is entered in B17. Write an Excel formula that can be copied
In the file named Pinevalley.xlsx, the first worksheet contains the salaries of several employees at Pine Valley University, the second worksheet contains the ages of the employees, and the third
The file named Qb2013.xlsx gives NFL quarterback (QB) statistics for the 2013 season. Write formulas in cells J2 and K2 that return the QB’s TDs and interceptions when you type a QB’s name in
The file named Employees.xlsx contains the rating (on a 0–10 scale) that each of 35 workers has given to three jobs. The file also gives the job to which each worker is assigned. Use a formula to
Redo Problem 9 using XLOOKUP formulas.Data from in Problem 9The file Grades.xlsx contains students’ grades on an exam. Suppose the curve is as follows:Use VLOOKUP formulas to return each
The file Stock.xlsx contains monthly stock returns for General Motors and Microsoft. Name the ranges containing the monthly returns for each stock and compute the average monthly return on each
Create a range name that averages the last five years of sales data. Assume annual sales are listed in a single column.
A person’s body mass index (BMI) is computed as BMI=703*Weight/Height2. Set up a worksheet to compute a person’s BMI.
In my class, I give five homework assignments, each worth 25 points, and three exams, each worth 100 points. I currently compute a student’s final grade by giving 75 percent of the weight to exams
A curious economist dropped 1,008 wallets in city, rural, and suburban locations. For each wallet, you are told whether or not the wallet was returned. You want to use the results to determine
Your MBA team members are Wayne, Taylor, Britney, and Katy. Create a macro that enters the team members in cells A1:D1.
Create a macro that will place a bold red border around any set of selected cells.
Create a macro that will format any selected range in bold italics.
Pressing Ctrl+A selects all cells in the current worksheet. Given this fact, create a macro that will ensure that each cell in a worksheet is formatted in boldfaced italics.
The annual rate your bank charges you to borrow money for 1, 5, 10, or 30 years is shown in the following table.If you borrow money from the bank for any duration from 1–30 years that’s not
You are thinking of advertising Microsoft products on a popular TV music program. You pay one price for the first group of ads, but as you buy more ads, the price per ad decreases, as shown in the
Often, I want to freeze the results of a range resulting from formulas and then change the formulas to values. Create a macro that will find, based on the selected cell, the largest range of cells
The distance between any two US cities (excluding cities in Alaska and Hawaii) can be approximated by the following formula:The file Citydata.xlsx contains the latitude and longitude of selected US
Create a macro that will highlight an entire column of data in yellow. Your macro should work no matter how many rows of data there are in the column.
You are thinking of advertising Microsoft products on a sitcom. As you buy more ads, the price of each ad decreases, as shown in the following table.For example, if you buy eight ads, you pay $11,000
Write a LET formula that multiplies two numbers and adds a third number to the product.
Write a LAMBDA formula that multiplies two numbers and adds a third number to the product.
Use a LAMBDA helper function to create an array of three rows and seven columns in which each cell is equally likely to be Mon, Tues, Wed, Thur, or Fri.
If a salesperson’s total sales are at least $110, their bonus is 40 percent of sales; if their total sales are from $100 to $109, their bonus is 20 percent of sales. Otherwise, their bonus is 5
Consider two projects with the following cash flows:For what rate of interest will Project 1 have a larger NPV? Project 1 Project 2 Year 1 -$1,000 -$900 Year 2 $400 $100 Year 3 $350 $100 Year
My income in Year 1 is $80,000. I will work for 35 years and then retire. Each year, assume my income increases 5 percent per year, and my income is received at the beginning of the year. After
Greg Winston has gone bankrupt and started a lemonade stand. He has invested $400 in lemonade equipment, and he sells a glass of lemonade for $4. His unit cost of producing a cup of lemonade is
The current annual interest rate is 5 percent, and you are taking out a 20-year loan with a monthly end-of-month payment. If you can afford monthly payments of $3,000 per month, what is the most you
It is April 15, and so far this month, we have sold $35,000 worth of merchandise. Our quota is to sell $72,500 worth of units in April. If we charge a price p for our product, we will sell 800 – 4p
Suppose you are borrowing $500,000 and are making annual payments for 20 years at the beginning of each year. What annual interest rate corresponds to this situation?
To celebrate the Lewis and Clark Expedition, I am traveling 40 miles upstream and then 40miles downstream in a canoe. The speed of the river current is 5 miles per hour. If the trip takes me 5 hours
I am buying 40 pounds of candy. Some of the candy sells for $10 per pound, and some sells for $6 per pound. How much candy at each price should I buy to arrive at an average cost of $7 per pound?
I am managing a conference at my college. My fixed costs are $15,000. I must pay the 10 speakers $700 each, and the college union $300 per conference participant for food and lodging costs. I am
I expect to earn 10 percent per year on my retirement investments. At the end of each of the next 40 years, I want to put the same amount of money in my retirement portfolio. How much money do I need
At the end of each of the next 40 years, I’m going to put $20,000 in my retirement fund. What rate of return on my investments do I need so that I will have $2 million available for retirement in
How could I use Goal Seek to determine a project’s internal rate of return (IRR)?
In our mortgage example, suppose I need to borrow $200,000 for 15 years. If my maximum payments are limited to $2,000 per month, how high an annual interest rate can I tolerate?
Today, you bought five shares of Stock 1 for $30 per share and three shares of Stock 2 for $25 per share. In a month, you will sell all your stock. Use a data table to determine how total profit
When a customer places an order from a catalog, assume that a profit of $50 (not including the cost of mailing the catalog) is earned. Use a data table to determine how the total profit earned from
Currently, a magazine has 5,000 subscribers. Determine how the number of subscribers at the end of Year 6 changes as the number of new subscribers varies between 1,000 and 5,000 and the annual
You are investing in a new Broadway play, Insect in the Light. You are given the following information about the play:The fixed cost of opening the play is $5 million.The average ticket price is
You are thinking of opening a restaurant that will have six four-person tables. Each day the restaurant is open, there are two lunch seatings and three dinner seatings. In addition:The restaurant
You can retire at age 62 and receive $8,000 per year or retire at age 65 and receive $10,000 per year. What is the difference (in today’s dollars) between these two choices as you vary the annual
You are borrowing $200,000 and making payments at the end of each month. For an annual interest rate ranging from 5 percent to 10 percent and loan durations of 10, 15, 20, 25, and 30 years, determine
If you earn interest at an annual rate of x percent per year, then in n years $1 will become (1+ x)n dollars. For annual rates of interest from 1 percent to 20 percent, determine the exact time (in
Assume I have $100 in the bank. Each year, I withdraw x percent (ranging from 4 percent to 10 percent) of my original balance. For annual growth rates of 3 percent to 10 percent per year, determine
If you earn interest at percentage rate r per year and compound your interest n times per year, then in y years, $1 will grow to $(1 + (r / n))ny. Assuming a 10 percent annual interest rate, create a
You have begun saving for your child’s college education. You plan to save $5,000 per year. You want to calculate the amount of money you will have in the college fund after you save for 10 to 15
Find all records in which the product cell is colored yellow.
Find all the unique combinations of name, product, and location occurring during the first three months of 2006.
Find all sales by Jen between August 10 and September 15, 2005.
Find all top-30 sales (by units) occurring in 2005 that involved lip gloss or mascara.
Find all the unique combinations of the product and salesperson’s name.
Find all transactions in which Zaret or Betsy sold either lipstick or foundation.
Find all foundation transactions during the first three months of 2004 for which the per-unit price was larger than the average price received for foundation during the entire time period.
Find all transactions involving sales of at least 60 units during 2004 for which the per-unit price was a maximum of $3.10.
Find the top 20 revenue-generating transactions that involve foundation sales.
Find all transactions that rank in the top 5 percent with regard to units sold.
Find all transactions in which Hallagan sold eyeliner in the West region.
Use the Automatic data type to extract all available information related to the novel of the (19th) century, Les Misérables.
Compare the average monthly rainfall in Las Vegas and Honolulu.
Find the boiling point and density of ammonia.
Find the average body mass index, weight, and age for those suffering with gout.
What is the latitude and longitude of Yosemite National Park?
What is the height of Mt. Kilimanjaro?
What is the exchange rate between China’s currency and US dollars?
For IBM, find the number of employees, latest closing stock price, and price-to-earnings ratio.
What is the function of your ribs?
Find the average distance from Earth of each planet in our solar system.
Find Adele’s birthdate, a list of her albums, and a list of all the tracks on her Greatest Hits album.
Find the US gross, revenue, release date, production budget, and director for the top 10 grossing movies of all time.
Find the latitude and longitude of the cities listed in the Problem 1 worksheet.
How many units were sold by people other than Colleen? Use the file named Makeup2007.xlsx
Determine the total revenue earned by people who have five letters in their names. Use the file named Makeup2007.xlsx
Determine the total units sold by salespeople whose last names begin with C. Use the file named Makeup2007.xlsx
Determine the total revenue earned before December 10, 2005. Use the file named Makeup2007.xlsx
For each product, determine the total number of units and dollar volume sold.Use the file named Makeup2007.xlsx
How many songs that are exactly three minutes long and sung by Britney Spears were played later than 2004?
How many songs at least four minutes long and sung by Mandy Moore were played in 2007 or earlier?
How many songs sung by Barry Manilow were played in 2004?
Create a formula that always yields the number of songs played today.
How many songs were sung by singers whose names contain an e?
How many songs were sung by singers whose last names begin with M?
How many songs were played between June 1, 2004, and July 4, 2006?
How many songs were played before June 15, 2004?

Showing 800 - 900 of 914