Basic Formulas and Functions - Excel

Categories

  • Formulas

    • Creating Formulas

    • Relative, Absolute, and Mixed References

    • Using Formulas in Practice

  • Common Functions

    • SUM, AVERAGE, MIN, MAX, IF, VLOOKUP, CONCATENATE, TEXT, NOW, TODAY, SUMIF, COUNTIF

Formulas

Formulas in Excel are expressions that perform calculations on values in your worksheet. They always start with an equal sign (=) and can include numbers, cell references, operators, and functions.

- Creating Formulas -

Basic Arithmetic: You can use arithmetic operators to perform basic calculations:

  • Addition (+): =A1 + B1

  • Subtraction (-): =A1 - B1

  • Multiplication (*): =A1 * B1

  • Division (/): =A1 / B1

  • Exponentiation (^): =A1 ^2

Combining Operators: You can combine multiple operations in a single formula:

  • =(A1 + B1) * C1

Using Cell References: Referencing other cells allows for dynamic calculations that update automatically when the data changes:

  • =SUM(A1:A10) + C1

For more information on Operators, please visit our Excel Operators page: Link to Excel Operators Page

- Relative, Absolute, and Mixed References -

Relative References: Change when a formula is copied to another cell.

  • Original Formula in C1: =A1 + B1

  • If this formula is copied over to cell D1 then the formula would change to =B1 + C1, because you moved over a column.

  • Explanation:

    • In C1, A1 refers to the cell two columns to the left, and B1 refers to the cell one column to the left.

    • When you paste the formula into D1, A1 (two columns left of C1) changes to B1 (two columns left of D1) and B1 (one column left of C1) changes to C1 (one column left of D1).

Absolute References: Does not change when a formula is copied, They are created by adding a dollar sign ($) before the column letter and the row number. This locks the reference to a specific cell, regardless of where the formula is copied.

  • $A$1 is an absolute reference and does not change when copied. It always refers to cell A1.

Mixed References: Combine relative and absolute references. You can lock either the row or the column by placing a dollar sign before either the column letter or the row number.

  • $A1: The column (A) is fixed, but the row is relative.

  • A$1: The row (1) is fixed, but the column is relative.

  • Example:

    • Original Formula in C1: =$A$1 + B$1

    • When you copy the formula from cell C1 to cell D2, the formula becomes =$A$1 + C$1

    • $A$1 is fully absolute and B$1 is partially absolute.

- Using Formulas in Practice -

Calculating Totals: Summing values is one of the most common operations in Excel. You can add individual cell values or use the SUM function for a range of cells.

Adding Individual Cell Values

=A1 + A2 + A3

Using the SUM Function

=SUM(A1:A3)

More information on the SUM Function: SUM Function Page

Calculating Averages: To find the average of a set of numbers, you can either add the values and divide by the number of values or use the AVERAGE function.

Manual Calculation

=(A1 + A2 + A3) / 3

Using the AVERAGE Function

=SUM(A1:A3)

More information on the AVERAGE Function: AVERAGE Function Page

Common Functions

Excel provides a wide array of built-in functions to perform complex calculations. Functions simplify your formulas and increase efficiency.

- SUM Function -

  • Adds a range of cells.

  • Syntax: =SUM(number1, [number2], …)

  • Example: =SUM(A1:A10)

  • More information on the SUM Function: SUM Function Page

- AVERAGE Function -

  • Calculates the average of a range of cells.

  • Syntax: =AVERAGE(number1, [number2], …)

  • Example: =AVERAGE(A1:A10)

  • More information on the AVERAGE Function: AVERAGE Function Page

- MIN Function -

  • Returns the smallest number in a range.

  • Syntax: =MIN(number1, [number2], …)

  • Example: =MIN(A1:A10)

  • More information on the MIN Function: MIN Function Page

- MAX Function -

  • Returns the largest number in a range.

  • Syntax: =MAX(number1, [number2], …)

  • Example: =MAX(A1:A10)

  • More information on the MAX Function: MAX Function Page

- IF Function -

  • Performs a logical test and returns one value for TRUE and another for FALSE.

  • Syntax: =IF(logical_test, value_if_true, value_if_false)

  • Example: =IF(A1>10, “Yes”, “No”)

  • More information on the IF Function: IF Function Page

- VLOOKUP Function -

  • Looks up a value in the first column of a range and returns a value in the same row from a specified column.

  • Syntax: =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

  • Example: =VLOOKUP(B1, A1:C10, 3, FALSE)

  • More information on the VLOOKUP Function: VLOOKUP Function Page

- CONCATENATE Function -

  • Joins two or more text strings into one string.

  • Syntax: =CONCATENATE(text1, [text2], …)

  • Example: =CONCATENATE(A1, “ “, B1)

  • More information on the CONCATENATE Function: CONCATENATE Function Page

- TEXT Function -

  • Formats a number and converts it to text.

  • Syntax: =TEXT(value, format_text)

  • Example: =TEXT(A1, “0.00”)

  • More information on the TEXT Function: TEXT Function Page

- NOW Function -

  • Returns the current date and time.

  • Syntax: =NOW()

  • Example: =NOW()

  • More information on the NOW Function: NOW Function Page

- TODAY Function -

  • Returns the current date and time.

  • Syntax: =TODAY()

  • Example: =TODAY()

  • More information on the TODAY Function: TODAY Function Page

- SUMIF Function -

  • Adds the cells that meet a single criterion.

  • Syntax: =SUMIF(range, criteria, [sum_range])

  • Example: =SUMIF(A1:A10, “>10”)

  • More information on the SUMIF Function: SUMIF Function Page

- COUNTIF Function -

  • Adds the cells that meet a single criterion.

  • Syntax: =COUNTIF(range, criteria)

  • Example: =COUNTIF(A1:A10, “Yes”)

  • More information on the COUNTIF Function: COUNTIF Function Page