bmgt301f excel midterm
MIN function
The Excel MIN function returns the smallest numeric value in a range of values. The MIN function ignores empty cells, the logical values TRUE and FALSE, and text values. =MIN (number1, [number2], ...)
STDEV.S function
The Excel STDEV.S function calculates the standard deviation for a sample set of data. STDEV.S replaces the older STDEV function, with the same behavior. =STDEV.S (number1, [number2], ...)
MEDIAN function
The MEDIAN function returns the median (middle number) in a group of supplied numbers. For example, =MEDIAN(1,2,3,4,5) returns 3. can also choose a cell
AND function
- The Excel AND function is a logical function used to require more than one condition at the same time - AND returns either TRUE or FALSE. - To test if a number in A1 is greater than zero and less than 10, use =AND(A1>0,A1<10) - The AND function can be used as the logical test inside the IF function to avoid extra nested IFs, and can be combined with the OR function =AND (logical1, [logical2], ...)
IF function
- The IF function can perform a logical test and return one value for a TRUE result, and another for a FALSE result - For example, to "pass" scores above 70: =IF(A1>70,"Pass","Fail") - More than one condition can be tested by nesting IF functions - The IF function can be combined with logical functions like AND and OR =IF (logical_test, [value_if_true], [value_if_false])
NPV function
=NPV (rate, value1, [value2], ...) Arguments rate - Discount rate over one period. value1 - First value(s) representing cash flows. value2 - [optional] Second value(s) representing cash flows. (NOT LIKE TI CALCULATOR NPV FUNCTION)
COUNTIF function
COUNTIF is a function to count cells that meet a single criteria=COUNTIF (range, criteria)
CUMIPMT function
Calculates cumulative interest for a specified payment period =CUMIPMT (rate, nper, pv, start_period, end_period, type) rate - The interest rate per period. nper - The total number of payments for the loan. pv - The present value, or total value of all payments now. start_period - First payment in calculation. end_period - Last payment in calculation. type - When payments are due. 0 = end of period. 1 = beginning of period.
STDEV.P function
Calculates standard deviation based on the entire population given as arguments (ignores logical values and text). The standard deviation is a measure of how widely values are dispersed from the average value (the mean). =STDEV.P(number1,[number2],...)
VAR.P function
Calculates variance based on the entire population (ignores logical values and text in the population). =VAR.P(number1,[number2],...)
PROPER function
Capitalizes the first letter in a text string and any other letters in text that follow any character other than a letter. Converts all other letters to lowercase letters. =PROPER(text)
VAR.S function
Estimates variance based on a sample (ignores logical values and text in the sample). =VAR.S(number1,[number2],...)
What is Goal Seek?
Goal Seek is Excel's built-in What-If Analysis tool that shows how one value in a formula impacts another. More precisely, it determines what value you should enter in an input cell to get the desired result in a formula cell. Goal Seek is accessible via What-If Analysis which is found in the forecast group under the data tab. Goal Seek will provide a dialogue box with 3 options for inputs: 1. Set cell - the reference to the cell containing the formula 2. To value - the formula result you are trying to achieve 3. By changing cell - the reference for the input cell that you want to adjust
VLOOKUP function
In its simplest form, the VLOOKUP function says: =VLOOKUP(What you want to look up, where you want to look for it, the column number in the range containing the value to return, return an Approximate or Exact match - indicated as 1/TRUE, or 0/FALSE). *PLEASE USE THIS OR SEE IT USED ON YOUTUBE*
AVERAGE function
Returns the average (arithmetic mean) of the arguments. For example, if the range A1:A20 contains numbers, the formula =AVERAGE(A1:A20) returns the average of those numbers. =AVERAGE(number1, [number2], ...)
CUMPRINC
Returns the cumulative principal paid on a loan between start_period and end_period. =CUMPRINC(rate, nper, pv, start_period, end_period, type) rate - The interest rate per period. nper - The total number of payments for the loan. pv - The present value, or total value of all payments now. start_period - First payment in calculation. end_period - Last payment in calculation. type - When payments are due. 0 = end of period. 1 = beginning of period.
LARGE function
Returns the k-th largest value in a data set. You can use this function to select a value based on its relative standing. For example, you can use LARGE to return the highest, runner-up, or third-place score. LARGE(array, k) Array - Required. The array or range of data for which you want to determine the k-th largest value. K - Required. The position (from the largest) in the array or cell range of data to return.
MAX function
Returns the largest value in a set of values. =MAX(number1, [number2], ...)
CONCAT function
The CONCAT function combines the text from multiple ranges and/or strings, but it doesn't provide delimiter or IgnoreEmpty arguments. CONCAT replaces the CONCATENATE function. However, the CONCATENATE function will stay available for compatibility with earlier versions of Excel.
AVERAGEIF function
The Excel AVERAGEIF function computes the average of the numbers in a range that meet the supplied criteria =AVERAGEIF(range, citeria, [average_range])
AVERAGEIFS function
The Excel AVERAGEIFS function calculates the average of numbers in a range that meet one or more supplied criteria. =AVERAGEIFS (avg_rng, range1, criteria1, [range2], [criteria2], ...)
CONCATENATE function
The Excel CONCATENATE function concatenates (joins) join up to 30 text items (cells with text) together and returns the result as text. =CONCATENATE(text1, [text2], ...)
COUNTIFS function
The Excel COUNTIFS function returns the count of cells that meet one or more criteria=COUNTIFS (range1, criteria1, [range2], [criteria2], ...)
IPMT function
The Excel IPMT function can be used to calculate the interest portion of a given loan payment in a given payment period. For example, you can use IPMT to get the interest amount of a payment for the first period, the last period, or any period in between. =IPMT (rate, per, nper, pv, [fv], [type]) rate - The interest rate per period. per - The payment period of interest. nper - The total number of payment periods. pv - The present value, or total value of all payments now. fv - [optional] The cash balance desired after last payment is made. Defaults to 0. type - [optional] When payments are due. 0 = end of period. 1 = beginning of period. Default is 0.
LEFT function
The Excel LEFT function extracts a given number of characters from the left side of a supplied text string. For example, =LEFT("apple",3) returns "app". =LEFT (text, [num_chars]) does not strictly have to be text inputted into the function; can reference a cell with ONLY text inside of it
NPER function
The Excel NPER function is a financial function that returns the number of periods for loan or investment. You can use the NPER function to get the number of payment periods for a loan, given the amount, the interest rate, and periodic payment amount. =NPER (rate, pmt, pv, [fv], [type])
PPMT function
The Excel PPMT function can be used to calculate the principal portion of a given loan payment. For example, you can use PPMT to get the principal amount of a payment for the first period, the last period, or any period in between. =PPMT (rate, per, nper, pv, [fv], [type])
RATE function
The Excel RATE function is a financial function that returns the interest rate per period of an annuity. You can use RATE to calculate the periodic interest rate, then multiply as required to derive the annual interest rate. The RATE function calculates by iteration. =RATE (nper, pmt, pv, [fv], [type], [guess])
RIGHT function
The Excel RIGHT function extracts a given number of characters from the right side of a supplied text string. For example, RIGHT("apple",3) returns "ple". =RIGHT (text, [num_chars])
SMALL function
The Excel SMALL function returns numeric values based on their position in a list ranked by value. In other words, it can retrive "nth smallest" values - smallest value, 2nd smallest value, 3rd smallest value, etc. =SMALL (array, n)
TRIM function
The Excel TRIM function strips extra spaces from text, leaving only a single space between words and no space characters at the start or end of the text. =TRIM (text)
IFS function
The IFS function checks whether one or more conditions are met, and returns a value that corresponds to the first TRUE condition. IFS can take the place of multiple nested IF statements, and is much easier to read with multiple conditions. =IFS([Something is True1, Value if True1,Something is True2,Value if True2,Something is True3,Value if True3)...
LOWER function
The Microsoft Excel LOWER function converts all letters in the specified string to lowercase. If there are characters in the string that are not letters, they are unaffected by this function. does not strictly have to be text inputted into the function; can reference a cell with ONLY text inside of it
OR function
The OR function is a logical function to test multiple conditions at the same time- OR returns either TRUE or FALSE- OR function can be used as the logical test inside the IF function to avoid extra nested IFs, and can be combined with the AND function=OR (logical1, [logical2], ...)
SUM function
The SUM function adds values. You can add individual values, cell references or ranges or a mix of all three. For example: =SUM(A2:A10) Adds the values in cells A2:10. =SUM(A2:A10, C2:C10) Adds the values in cells A2:10, as well as cells C2:C10. =SUM(number1,[number2],...)
SUMIF function
The SUMIF function is a worksheet function that adds all numbers in a range of cells based on one criteria (for example, all cells with values equal to 2000 or larger) =SUMIF(range, criteria, [sum_range])
SUMIFS function
The SUMIFS function, one of the math and trig functions, adds all of its arguments that meet multiple criteria =SUMIF(sum_range, criteria_range1, criteria1, criteria_range2, criteria2,...)
SUMPRODUCT function
The SUMPRODUCT function multiplies ranges or arrays together and returns the sum of products =SUMPRODUCT (array1, [array2], ...) *PLEASE USE THIS OR SEE IT USED ON YOUTUBE*
TEXT function
The TEXT function lets you change the way a number appears by applying formatting to it with format codes. It's useful in situations where you want to display numbers in a more readable format, or you want to combine numbers with text or symbols. =TEXT(Value you want to format, "Format code you want to apply") *PLEASE USE THIS OR SEE IT USED ON YOUTUBE*
UPPER function
The UPPER function is an Excel Text function, that will convert text to all capital letters (UPPERCASE). Thus, the function converts all characters in a supplied text string into upper case. =UPPER(Text)
COUNT function
Use the COUNT function to get the number of entries in a number field that is in a range or array of numbers. For example, you can enter the following formula to count the numbers in the range A1:A20: =COUNT(A1:A20). In this example, if five of the cells in the range contain numbers, the result is 5.