**Comprehensive Excel Formula Types & Elements Video 12 Examples**

### DESCRIPTION

**(00:39)** Overview.

**(03:31)** Hours worked formula. Type of Formula: Calculating. Formula Elements: Equal sign, parenthesis, cell reference, math operator, cell reference, parenthesis, math operator, number that does not change.

**(05:13) **Gross pay formula. Type of Formula: Calculating. Formula Elements: Equal sign, cell reference, math operator, cell reference.

**(05:38) **Number of sales each sales rep made formula. Type of Formula: Calculating. Formula Elements: Equal sign, function, absolute range of cells, relative cell reference.

**(07:31)** Text extracting formula. Type of Formula: Text Formula. Formula Elements: Equal sign, RIGHT function, relative cell reference, number that does not change.

**(09:05)** Text join formula. Type of Formula: Text Formula. Formula Elements: Equal sign, relative cell reference, join symbol, ampersand ( & ), relative cell reference.

**(10:03)** Over Hurdle formula. Type of Formula: Logical Formula. Formula Elements: Equal sign, relative cell reference, comparative operator, absolute cell reference.

**(11:19)** IF function to show “over” or “under” text formula. Text Formula that contains the formula elements: equal sign, IF function, relative cell reference, comparative operator, absolute cell reference, “text”.

**(13:50)** VLOOKUP to retrieve employee record formula. Type of Formula: Lookup Formula. Formula Elements: Equal sign, VLOOKUP function, absolute cell reference, absolute range of cells, function argument element. and Drop-down arrow: Data Ribbon, Data Validation, List.

**(17:54)** Robust Net Income Formula. Type of Formula: Calculating. Formula Elements: Equal sign, cell reference, math operator, built-in function, range of cells.

**(20:51) **Add Top 3 Sales Formula. Type of Formula: Array Formula. Formula Elements: Equal sign, SUM function, LARGE function, range of cells, array constant.

**(26:02) **Add with 2 criteria (conditions) formula. Type of Formula: Calculating formula. Formula Elements: Equal sign, SUMIFS function (add with 2 criteria), range of cells, cell reference.

**(29:05) **Count with 2 criteria formula (count numbers between a lower and upper limit) formula. Type of Formula: Calculating formula. Formula Elements: Equal sign, COUNTIFS function (count with 2 criteria), absolute range of cells, relative cell reference.

**Types of formulas:**

**1.** Calculating Formulas that calculate a number answer (like adding)

**2.** Logical Formulas that give you a logical value, either TRUE or FALSE (like formulas that say whether two accounts are in balance)

**3.** Text Formulas that deliver a word to a cell or create labels for reports (like a label for a loan payment).

**4.** Lookup Formulas that look up an item in a table (like looking up a price for a product).

**5.** Array Formulas are advanced formulas that contain a formula element that operate on arrays instead of individual items and produce an array of answers.

**Formula elements:**

**1.** Equal sign (starts all formulas).

**2.** Cell references (also defined names, sheet references, workbook references, table nomenclature).

**3.** Math operators.

**4.** Numbers (if the number will not change; for example, 12 months, 24 hours).

**5.** Functions (IF, MATCH, INDEX, COUNTIF, and so on).

**6.** Function argument elements (such as a 0 in the third argument of MATCH to tell function to do an “Exact Match” lookup,or FALSE in the 4th argument of VLOOKUP to do an “Extact Match” lookup).

**7.** Comparative operators.

**8.** Join operator: Ampersand (join symbol: &).

**9.** Text within quotation marks (for example, “For the Month Ended”).

**10.** Array constants (for example, {1,2,3}).

## Leave a Reply

You must be logged in to post a comment.