Question

In: Accounting

What are the favorite aspect/functions of excel

What are the favorite aspect/functions of excel

Solutions

Expert Solution

Excel is a tool, like most apps, and is great at managaing data charts and building math based formulas like a P&L. Excel allows us to perform calculations (like a calculator) and manipulate text (like a word processor). Its power comes from being able to do multiple related calculations, based on its grid structure. So while with a calculator or on paper we can do simple sums, in Excel we can do the same sum many, many times. It features calculation, graphing tools, pivot tables, and a macro programming language called Visual Basic for Applications.

Functions of excel:-

  1. Mathematical Functions

SUM - Adds up all the values in a range

SUMIF - Adds all the values in a range that meet specific critera

SUMIFS (2007+) - Adds values in a range based on multiple criteria

SUMPRODUCT - Sum a range of cells that meet multiple criteria

ROUND - Round a number to a specified number of digits

ROUNDUP - Round a number up to a specified number of digits

ROUNDDOWN - Round a number down to a specified number of digits

CEILING - Round a number up to a multiple of significance

FLOOR - Round a number down to a multiple of significance

  1. Statistical Functions

COUNT - Counts all the values in a range

AVERAGE - Calculates the average number from a range of values

MAX - Finds the maximum value in a range

MIN - Finds the minimum value in a range

COUNTA - Counts all non-empty cells in a range

COUNTBLANK - Counts all blank cells in a range

COUNTIF - Counts all the cells in a range that meet specific critera

COUNTIFS (2007+) - Counts all the cells in a range that meet multiple criteria

AVERAGEIF (2007+) - Calculates the average of a range of values that meet specific criteria

AVERAGEIFS (2007+) - Calculates the average of a range of values that meet multiple criteria

LARGE - Return a value dependent upon its ranking in a range of values in descending order

SMALL - Return a value dependent upon its ranking in a range of values in ascending order

RANK - Returns the rank or position of a number within a range of numbers

3.Text Functions

LEN - Returns the length, in number of characters, of the contents of a cell

REPT - Repeats a character a specified number of times

TRIM - Remove unwanted spaces from cells

LEFT - Extracts a specific number of characters from the start of a cell

RIGHT - Extracts a specific number of characters from the end of a cell

MID - Extracts a specific number of characters from the middle of a cell

UPPER - Converts the contents of a cell to uppercase

LOWER - Converts the contents of a cell to lowercase

PROPER - Converts the contents of a cell to proper case

REPLACE - Replace existing characters in a cell with a different set of characters

SUBSTITUTE - Replace existing characters with a different set of characters

4.Financial Functions

PMT - Calculates loan repayments based on constant payments and a constant interest rate

RATE - Returns the interest rate per period of a loan or investment

PV - Returns the present value of an investment based on a constant interest rate and payments

FV - Returns the future value of an investment based on constant payments and a constant interest rate

IPMT - Calculates the interest paid during a period of a loan or investment

PPMT - Calculates the principal payment made in a period of an investment

IRR - Returns the internal rate of return on a series of regular investments

XIRR - Returns the internal rate of return on a series of irregular payments on an investment

NPV - Returns the net present value of an investment based on a series of cash flows and a discount rate

XNPV - Returns the net present value of an investment based on a series of cash flows, the dates of the cash flows and a discount rate

Lookup and Reference Functions

VLOOKUP - Looks vertically down a list to find a record and returns information related to that record

HLOOKUP - Looks horizontally across a list to find a record and returns information related to that record

MATCH - Returns the position of a value in a list

INDEX - Returns an item from a specific position in a list

INDIRECT - Allows you to use a cell reference entered as a text string

OFFSET - Returns a value from a cell, or range of cells that are a specified number of rows and columns from another cell

CHOOSE - Returns a value from a list of values based on a specified position

ADDRESS - Returns a text representation of a cell address from specified row and column numbers

5. Logical Functions

IF - Tests a condition and takes an alternative action depending on the result

AND - Test up to 30 conditions using logical And

OR - Test up to 30 conditions using logical Or

IFERROR - Performs a specified action if a formula evaluates to an error, and displays the formula result if not

6.Date & Time Functions

TODAY - Returns the current date

NOW - Returns the current date and time

DATE - Returns the sequential serial number for the specified date and formats the result as a date

DAY - Returns the day corresponding to a date represented by a number between 1 and 31

MONTH - Returns the month corresponding to a date represented by a number between 1 and 12

YEAR - Returns the year corresponding to a date represented by a number in the range 1900 to 9999

WORKDAY - Returns the date a specified number of workings days before or after a date

WEEKDAY - Returns the day of the week corresponding to a specified date

NETWORKDAYS - Returns the number of workdays between two dates

EOMONTH - Calculates the last day of the month a specified number of months before or after a date


Related Solutions

In Excel, What are some of the functions that can be used to manipulate text?
In Excel, What are some of the functions that can be used to manipulate text?
Excel functions to calculating binomial, Poisson, and normal distributions. Identify the functions and discuss how Excel...
Excel functions to calculating binomial, Poisson, and normal distributions. Identify the functions and discuss how Excel can be used to calculate probabilities of X, X. Apply an example to at least one business scenario.
W72A) I am learning EXCEL Functions. Please answer in EXCEL Functions in detail Stock Returns (relationship...
W72A) I am learning EXCEL Functions. Please answer in EXCEL Functions in detail Stock Returns (relationship between hypothesis testing and confidence intervals) Suppose you as an investor with a stock portfolio of hundreds of thousands of dollars decide to sue your broker because of low returns due to lack of portfolio diversification, i.e., too many holdings with similar return prospects. The 39 monthly returns, expressed as percentages, are shown in the table below and reproduced in your Excel answer template....
What is favorite bool?
What is favorite bool?
Can you please assist me with setting this question in excel and what functions to use...
Can you please assist me with setting this question in excel and what functions to use PROBLEM 5 Minneapolis Health System has bonds outstanding that have four years remaining to maturity, a coupon interest rate of 9 percent paid annually, and a $1,000 par value. a. What is the yield to maturity on the issue if the current market price is $829? b. If the current market price is $1,104? c. Would you be willing to buy one of these...
What would B and C excel functions be? Higgs Bassoon Corporation is a custom manufacturer of...
What would B and C excel functions be? Higgs Bassoon Corporation is a custom manufacturer of bassoons and other wind instruments. Its current value of operations, which is also its value of debt plus equity, is estimated to be $200 million. Higgs has $110 million face value, zero coupon debt that is due in 3 years. The risk-free rate is 5%, and the standard deviation of returns for similar companies is 60%. The owners of Higgs Bassoon view their equity...
Use EXCEL (show Excel functions) to determine the following: Ty bought a new car for $25,000....
Use EXCEL (show Excel functions) to determine the following: Ty bought a new car for $25,000. He put $2,000 down and financed the rest with the dealer who had a special offer of 2% interest for 60 monthly payments. How much will Ty pay each month? EXCEL function used: Inputs: Result: Twins Taylor and Tanner both received $1,000 from their grandmother for their birthday. She told them they had to save it for 5 years. Taylor found an account paying...
What excel codes would one need to complete the following functions? From the original sample of...
What excel codes would one need to complete the following functions? From the original sample of 100 data values you generated (including any outliers), create a new data column and label it as Measles. Use the following criteria to define the measles percent as either low, mid or high. Low Measles immunization is 60 percent or younger Mid Measles immunization is between 60 and 90 excluding endpoints High Measles immunization is 90 percent and more
[Refer to the first venture (Intertactical-See reference below). Use Excel built-in functions, include in the Excel...
[Refer to the first venture (Intertactical-See reference below). Use Excel built-in functions, include in the Excel file.] (a) Use PV function to calculate the present worth (i.e., at time zero) of each year’s CF. Find the total present worth. (b) Use NPV function to calculate the present worth (i.e., at time zero) of the venture. (c) Use FV function to calculate the future worth (i.e., at year 5) of each year’s CF. Find the total future worth. (d) Use PMT...
What is a complex aspect of binomial distributions?
What is a complex aspect of binomial distributions?
ADVERTISEMENT
ADVERTISEMENT
ADVERTISEMENT