Common Excel Functions - Documentation

This Microsoft article can help explain more. For example, understanding how the Function Dialog (fx) Box works as shown below can help:

This web page serves as a tutorial guide covering some common key functions you should know.

Among the most widely used functions is the SUM() function.

To add the constant numbers 1, 3, and 5, we can simply write =SUM(1,3,5)

Suppose we have to find the sum of the following data:

There are a few ways we can add these numbers to get the total. We can:

• Using a Manual Addition Formula: =B2 + C2 + D2 + E2
  Each quarters sales figure is added individually via a formula
• Use the SUM() function on each individual number: =SUM(B2,C2,D2,E2)
  Each quarters sales figure is added individually via the SUM() function
• Use the SUM() function with a range: =SUM(B2:E2)
  The best way to calculate the total is by summing the values in cells B2 to E2, which contain the sales figures for Quarters 1 to 4.

Few things to note:

• The word "SUM" doesn’t need to be in all caps, but it's common to write it that way.
• The SUM function ignores cells with text.

Let's take a look at a few examples:

The AVERAGE() function returns the average (arithmetic mean) of each of the arguments.

It is typed as '=AVERAGE' and is formatted as:

=AVERAGE(value1, value2, [value3, ...])

Looking back at our earlier SUM example, let's now find the AVERAGE of the following data:

Let's take a look at a few examples:

A text function that concatenates (joins) values. The values are not limited to text as it can join numbers too.

NOTE: There are no delimiters added between the combined cell values.
A delimiter is a character/symbol used to seperate values apart from each other e.g. / \ , | -

It is typed as '=CONCAT' and it is a new version of the old CONCATENATE() function. It is formatted as:

=CONCAT(value1, value2, [value3, ...])

This operator is equivalent to using the concatenation operator (&) as shown below:

You can use the CONCAT function to join text and numbers. For example, a manufacturer part number (MPN) is a unique alphanumeric code that manufacturers assign to their products for easy identification.

Note: The CONCAT() function improves on older CONCATENATE() function by supporting ranges. For example, to combine values from A1, A2, and A3, you can reference the range as =CONCAT(A1:A3)

Use the TEXTJOIN function to join multiple values with a delimiter. It does everything CONCAT does but also lets you add a delimiter and skip empty values.

The COUNTIF function is a built-in "statistical" function that counts the number of cells meeting a given criterion/condition. The function returns a number representing cells counted

=COUNTIF(range, criterion)

As an example, let's take a look at a grocery list of items

Suppose we want to find the number of times the word "Apples" show up. We can use the COUNTIF function

= COUNTIF(B1:G1, "Apples") // Output: 2

NOTE: Since the function criteria requires an exact match, we cannot use the singular form "Apple" as there are no exact matches available.

Spelling Case doesn't matter but actual characters used does.

The SUMIF function returns the sum of values in a range that fit in a group/condition a.k.a. a conditional sum.

=SUMIF(range, criteria, [sum_range])

The sum range should be the same size and shape as range.
NOTE: Sorting the data by any column will not make a difference to the final output.

Let's take a look at an example:

Suppose we want to find all total sales for Q2. We can write the SUMIF Function as:

The IF function returns one of two values: one value if the condition is TRUE and another value if the condition is FALSE.

=IF(logical_test, [value_if_true], [value_if_false])

Please look at the following example:

Expense Worksheet

In the above example, depending on the cost variance, the answer will be 1 of 2 answers: "Over Budget" or "Under Budget." We can express this in 2 different ways:

• If the output is greater than 0, output "Under Budget". Else output "Over Budget"
  =IF(D1>0,"Under Budget","Over Budget")
• If the output is less than 0, output "Over Budget". Else output "Under Budget"
  =IF(D1<0,"Over Budget","Under Budget")

Let's take a look at a few examples: