In: Accounting
What are the favorite aspect/functions of excel
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:-
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
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