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.