Question

In: Finance

1. What are examples of instances when you might use conditional formatting in an Excel spreadsheet?...

1. What are examples of instances when you might use conditional formatting in an Excel spreadsheet? Try to come up with an example that is not used in the chapter.

2.Provide an example of when you would use the COUNTIF function and one of when you would use the COUNTIFS function.

Which of the following functions identified in this or the previous chapter do you think will be the most useful? Why?

3.Why you might want to use tables in Excel?

Provide an example of when someone might want to perform calculations on parts of a spreadsheet.

Solutions

Expert Solution

1. Conditional formatting is a function available in MS excel which is used for applying different types of formatting to a range of cells as per some criteria or a condition like highlighting cells in the range whose value is above 50, highlighting the top 10 values in a range of cells, highlighting cells containing text such as Monday and many other options are also available.

Example: Suppose you are a senior HR manager of a company. You are required to monitor the performance of a large no. of employees working within the organization. Now, you have a long list of all the sales managers in your system, defining the number of products sold by them in the last 6 months. Now you need to find out the top 5 performers of the company for the last 6 months, simply using excel and applying conditional formatting of highlighting cell containing top 5 values, you can easily detect the top performers which would otherwise might be a hectic process for you to search in a long list for finding out the best performers.

2. Countif function is a function that is used for counting the number of cells in a range according to criteria: greater than, less than, or equal to particular value.

Use: Suppose you have a long list of your students taking coaching at your institution. You need to count the students who have secured marks above 90. simply by using countif function, you can in seconds find out the number of students who have above 90 marks.

Countifs function is also used for counting the number of cells in a range with more and multiple criteria, unlike countif function which counts only on the basis of one criteria.

Use: If in the same above example you want to find out the count of students whose name starts with "A" and have secured above 70% marks. You can easily find the count of such students in few seconds using this function in MS excel.

Countifs function is more beneficial function of Microsoft excel in comparison to countif function. The reason is that, in countif you can count the number of cells only on the basis of single criteria whereas in countifs functions you can count the cells on the basis of multiple criteria. Countifs function is one of the most useful function used in corporates nowdays for counting values as per desired criteria among a large set of data.

3. Table function is a function that helps you to arrange your set of data into proper tabulated format in excel. It arranges your data into rows, columns and headers as per your needs. Data converted into the table provides numerous benefits: First of all it looks better in a proper arrange format, it makes it easy for analyzing and comparison of data when available in a table format, provides various filters options to quickly sort and filter data, effortless data entry, quick data formatting, fast calculations, dynamic charts and many other options.

Use: Suppose you have a large set of unorganized data, using the table function in excel you can just in seconds arrange in proper formats and apply different types of formatting. In absence of table function, it becomes a quite hectic process to arrange you set of data.


Related Solutions

3.) What type of formatting is completed based on a certain criteria/conditions being met? Conditional Formatting...
3.) What type of formatting is completed based on a certain criteria/conditions being met? Conditional Formatting Bold Formatting Color Formatting Criteria Formatting 4.) In a 2 variable data table, which cell location is the formula found? Northwest Corner Southwest Corner Northeast Corner Southeast Corner 5.) If instructed to create a 1 variable data table using a column input cell starting in cell A5, which cell is the first variable found in? A6 A5 B5 B6 6.) If instructed to create...
I need this in an excel file with formulas please: 1) Use an Excel spreadsheet to...
I need this in an excel file with formulas please: 1) Use an Excel spreadsheet to solve problem #31 (the PUTZ, Inc. project) for Chapter 10 in the textbook. 2) Conduct a sensitivity analysis that focuses on the sales price by increasing the price by 10% above the best estimate, and then by decreasing the price by 10% below the best estimate. 3) You must provide one spreadsheet for each of the three situations—the base case estimate, the best case,...
To complete this comprehensive exercise, use the Excel spreadsheet program. In this exercise you will be...
To complete this comprehensive exercise, use the Excel spreadsheet program. In this exercise you will be using linear programming and Solver in Excel to solve the problem described in the following scenario. At Goodman Shipping, the load master wants to determine the mix of cargo to be carried on the next trip. The ship's volume limit for cargo is 100,000 cubic meters, and its weight capacity is 2,310 tons. The master has five different types of cargo from which to...
1. Understand how to use EXCEL Spreadsheet (a)  Develop proforma Income Statement Using Excel Spreadsheet (b)  Compute  Net Project...
1. Understand how to use EXCEL Spreadsheet (a)  Develop proforma Income Statement Using Excel Spreadsheet (b)  Compute  Net Project Cashflows, NPV,  and IRR (c) Develop problem-solving and  critical thinking skills and make long-term investment decisions 1) Life Period of the Equipment = 4 years 8) Sales for first year (1) $200,000 2) New equipment cost $(200,000) 9) Sales increase per year 5% 3) Equipment ship & install cost $(35,000) 10) Operating cost (60% of Sales) $(120,000) 4) Related start up cost $(5,000)     (as a percent...
Objective: Analyze a subset of data from a published research paper using Excel. This will focus on data manipulation in Excel, including plot generation, if-statements, and conditional formatting.
Objective: Analyze a subset of data from a published research paper using Excel. This will focus on data manipulation in Excel, including plot generation, if-statements, and conditional formatting. You are given the relative abundances (fraction of the total number of microorganisms observed) of different types of microorganisms. One set was exposed to PFAAs, another set was not. Each set consists of three different replicates, or repeat trials.Problem: Check the statistical significance of each test, i.e., test whether there is a...
a. Use the multiple regression spreadsheet provided in the Excel spreadsheet to do the following: create...
a. Use the multiple regression spreadsheet provided in the Excel spreadsheet to do the following: create a new data set that is a subset of this larger provided data set. The new data set should include the dependent variable (VALUE) and the following independent variables (AGE, LOTSIZE, RMS, MOD KITCH, MOD BATH, AIRCON and FIREPL). b. For this new data set, use the Correlation option of Data Analysis in Excel and produce the correlation matrix associated with the dependent variable...
Instructions to solve the problem: You are required to use a financial calculator or spreadsheet (Excel)...
Instructions to solve the problem: You are required to use a financial calculator or spreadsheet (Excel) to solve the problem related to the cost of capital. You are required to show the following 3 steps for the problem. (i) Describe and interpret the assumptions related to the problem. (ii) Apply the appropriate mathematical model to solve the problem. (iii) Calculate the correct solution to the problem. Submit all answers as percentages and round to two decimal places. Problem: Cosa Nostra...
Instructions: You are required to use a financial calculator or spreadsheet (Excel) to solve the provided...
Instructions: You are required to use a financial calculator or spreadsheet (Excel) to solve the provided capital budgeting problem (sample questions and solutions are provided for guidance). Problem: Windrunner Corp. is considering a new machine that requires an initial investment of $800,000 installed and has a useful life of 10 years. The expected annual after-tax cash flows for the machine are $120,000 during the first 5 years, $150,000 during years 6 through 8 and $180,000 during the last two years....
Instructions: You are required to use a financial calculator or spreadsheet (Excel) to solve the following...
Instructions: You are required to use a financial calculator or spreadsheet (Excel) to solve the following capital budgeting problem (sample questions and solutions are provided for guidance): Kingston Corp. is considering a new machine that requires an initial investment of $520,000 installed, and has a useful life of 8 years. The expected annual after-tax cash flows for the machine are $76,000 during the first 3 years, $87,000 during years 4 through 6 and $92,000 during the last two years. (i)...
Instructions: You are required to use a financial calculator or spreadsheet (Excel) to solve the following...
Instructions: You are required to use a financial calculator or spreadsheet (Excel) to solve the following capital budgeting problem (sample questions and solutions are provided for guidance): Windrunner Corp. is considering a new machine that requires an initial investment of $800,000 installed, and has a useful life of 10 years. The expected annual after-tax cash flows for the machine are $120,000 during the first 5 years, $150,000 during years 6 through 8 and $180,000 during the last two years. (i)...
ADVERTISEMENT
ADVERTISEMENT
ADVERTISEMENT