Advanced Flashcards
Formula that checks ALL the arguments are met or not
=AND(cell>”logical1”, cell>”logical2”,…)
From where do you add a drop-down list?
Click on ‘Data’ ribbon, then click on ‘Data Validation’ tab
Formula that allows you to round up by multiples of any given number. e.g. if you wish to purchase 6 oranges, but they only sell in multiples of 8, you will be billed for 8; if you want 15 you will be billed for 16
=CEILING(cell,8)
Formula used to return a value from a list. You can use it to return text, numbers, dates and more. e.g. if you assign values 1 to 12 as months of the year, the formula will return the month corresponding to that number. 1 would return January, 2 would return February, etc.
=CHOOSE(index_num,value1,[value2],…)
index_num: The value to choose. A number between 1 and 254.
Value1: The first value from which to choose.
Value2: The second value from which to choose.
Formula used to compare data in columns; If we have two lists to compare in Excel.
=VLOOKUP(lookup_value, table_array, col_index_num, 0).
=IFERROR(VLOOKUP(lookup_value, table_array, col_index_num, 0), “New Value”).
Formula to find out if there is a correlation between two sets of data.
=CORREL(array1, array2)
In Excel, if you have a single numerical data set that you wish to have a visual indicator for, but don’t necessarily need a chart, in cell indicators can be used. Where can you find that?
Select your data, go to Conditional Formatting, then choose your formatting type
formula that is used to calculate the number of days, months or years between two dates.
=DATEDIF(start_date,end_date,unit)
Unit - The type of information you want returned (days, months or years.)
“d” = Days between the two dates.
“m” = Months between the two dates.
“y” = Years between the two dates.
“yd” = Days between the dates, as if the dates were in the same year.
“ym” = Months between the dates, as if the dates were in the same year.
“md” = Days between the two dates, as if the dates were in the same month and year.
formula that finds the average values of all cells in a column or database that contain the criteria you set.
=DAVERAGE(database, field, criteria)
field - the column of data you want to find
formula that will count the cells in a column or database that contain the criteria you set
=DCOUNT(database, field, criteria)
formula that will count the cells in a column or database that are not blank
=DCOUNTA(database, field, criteria)
Depreciation formula
=DB(cost, salvage, life, period)
Formula to extract a single value from a database or list column that matches the conditions you set.
=DGET(database, field, criteria)
Formula that will return the largest number in a column or database that contains the criteria you set
=DMAX(database, field, criteria)
Formula that will return the smallest number in a column or database that contains the criteria you set
=DMIN(database, field, criteria)
Formula that will multiply the value of all numbers in a column or database that contains the criteria you set
=DPRODUCT(database, field, criteria)
Formula that will add the values of all cells in a column or database that contain the criteria you set
=DSUM(database, field, criteria)
Formula that returns the date that is the specified amount of months after a set date
=EDATE(start_date, months)
Formula that returns the serial number of a date that is the last day of the specified amount of months after a set date
=EOMONTH(start_date, months)
find and replace information
On the Home tab, click on Find & Select, then choose Replace. Or you can use the keyboard shortcut of Ctrl + H.
Formula that gets the location of specific text inside a string of text
=FIND(find_text, within_text, [start_num])
or
=FIND(find_text, within_text, FIND(find_text, within_text)+1)
Formula that returns a value or a reference to a value based on a defined array within a table or range.
=INDEX (array,row_number,[column_num])
or
=INDEX(array,MATCH(lookup_value, array, 0),MATCH(lookup_value, array, 0))
Formula that gets information from a table, based on an exact match
=INDEX(array, MATCH(lookup_value,lookup_array,[match_type]))
A lookup formula used to return a value from text that is based on cell references.
=INDIRECT(ref_text)
or
=INDIRECT(ref_text &”!”& cell_ref)
Formula that returns the interest payment over a given timescale based on a single interest rate with constant payments.
=IPMT(rate, per, nper, pv, [fv], [type])
The “I” stands for “interest” and “PMT” for “payment”
Rate - This is the interest rate.
Per - This is the period you want to find the interest for.
Nper - This is the total number of payments to make.
Pv - This is the present value.
Fv - This is an optional argument. It is the future value. If it is not used, it is set to 0 by default.
Type - This is optional. You can use the numbers 0 or 1 to show when a payment is due.
Formula that checks whether a value in a cell contains an error
=ISERR(value)
or
=IF(ISERR(value),”Text to return if error”,value)
Formula that checks whether a value in a cell is logical
=ISLOGICAL(value)
Formula that checks whether a value in a cell is not text.
=ISNONTEXT(value)
or
=IF(ISNONTEXT(value),value_if_true,value_if_false)
Formula that checks whether a value in the cell is numeric
=ISNUMBER(value)
or
=IF(ISNUMBER(value),VLOOKUP(value,table_array,col_index_num,0),
VLOOKUP(value,table_array,col_index_num,0))
Formula that checks whether a value is odd.
=ISODD(value)
or
=IF(ISODD(value),value_if_true,value_if_false)
Formula that isolates the first name
=LEFT(text, FIND(“ “, text)-1)
Formula that checks whether a value in the cell is text based
=ISTEXT(value)
Formula that extract characters starting on the left side of a text.
Right
Middle
=LEFT(text, [num_chars])
=RIGHT (text, [num_chars])
MID(text, start_num, num_chars )
Formula that count how many characters there are in a specific text
=LEN(text)
Formula that search for a specific value in a range of cells. It will return the position of the value within the range, rather than the value itself.
=MATCH(lookup_value, lookup_array, [match_type])
Vlookup to return multiple results using a single query.
=VLOOKUP(lookup_value,table_array,COLUMNS(cell, cell), [range_ lookup])
check whether any of the conditions you have set in an argument are true
=OR(cell=”logical1”, cell=”logical2”)
or
=IF(OR(cell=”logical1”, cell=”logical2”)”value1”, “value2”)
Formula that calculate the payments for a loan if it has constant payments and a constant interest rate.
=PMT(rate, per, nper, pv, [fv], [type])
Formula that used to replace text based on its position within a string
=REPLACE(old_text, start_num, num_chars, new_text)
Shortcut that select all Cells with Comments
CTRL+Shift+O
What is a sparkline chart and how to create it?
Sparkline is a tiny chart contained within a cell’s background. Sparklines are useful for allowing you to spot patterns within your data.
Go on the ‘insert’ ribbon and click on ‘sparkline’ tab
Straight Line Depreciation formula
=SLN(cost, salvage, life)
Formula that calculates just how much the value of an asset decreases over the course of its specified lifetime
=SYD(cost, salvage, life, per)
Timeline in Pivot Table
click anywhere on the pivot table and then click on the ‘insert’ ribbon and then click on the ‘timeline’ tab.
VLOOKUP with 2 Criteria
=IF(ISTEXT(value),VLOOKUP(lookup_value,table_array,col_index_num,0),
VLOOKUP(lookup_value,table_array,col_index_num,0))
formula to look up the highest value in any data set.
=VLOOKUP(MAX(Column),Table,Col_num,0)
Column: The column in which the MAX data you need is stored.
Table: The full table of data. The first column selected needs to be the column, which has the MAX data you need.
Col_num: The column in which the data you need returned is stored.
