40  Excel formulas and functions

40.1 Formulas

Important basics about formulas in Excel:

• Formulas in Excel always begin with an equals sign: =
• The formula is always typed into the cell where the answer should appear.
• The formula is completed by pressing Enter on the keyboard.

40.2 Basic arithmetic operators

Below are some examples for formulas using arithmetic operators.

Add the values in cells A1 and B1: =A1+B1

To get the answer in C1, you need to add the formula =A1+B1 to the cell C1:

Subtract the value in B1 from the value in A1: =A1-B1

Multiply A1 with B1: =A1*B1

Divide A1 by B1: =A1/B1

You get the idea. More information on arithmetic operators can be found here.

40.3 Functions

Formulas can make use of predefined functions in Excel. We will only describe a small set of functions that will be fundamental to our analyses. For more information, see this page about Excel functions sorted by category or this page about Excel functions in alphabetical order.

If you want help on functions in Excel, click on the function symbol \(fx\):

This will open the Formula Builder, providing you with information on the available functions, and if you double-click on a specific function, help with building the formula. Alternatively, go to “Help” → “Excel Help”, or search online.

IF

IF is one of the most important logical functions in Excel. It works in the following way:

IF(something is True, then do something, otherwise do something else)

An IF statement can have two possible outcomes, depending on whether the condition is True or False. A potential use case for this function could be to create a column that includes only correct RTs.

Imagine you have the following PsychoPy output:

The first column contains all RTs, and the second column information about accuracy (remember that PsychoPy codes correct responses as 1, and incorrect responses as 0). Your aim is to have only RTs associated with correct responses in your third column.

You can use the following formula in cell C2 to achieve this:

=IF(B2=1, A2, "")

This states that:

• If B2 equals 1 (i.e., if the trial is correct)
  • Then C2 should be equal to A2 (basically copying the value from A2 to C2)
• If this is not the case (i.e., if the trial is not correct) [this part is implicit to the function]
  • Then C2 should remain empty (indicated by "")

If we apply this formula to all rows in our worksheet (by copying it to the adjacent cells - see below for details), we get the following output:

Column C now only contains RTs associated with correct trials.

Note that you can create more complex statements by combining multiple conditions using AND or OR. For example, you might not only want to remove RTs from incorrect trials, but also RTs that are extremely fast (presumably representing anticipatory responses) or extremely slow (presumably representing lapses of attention), independent of their accuracy. Based on previous research, you might consider RTs below 150 ms as anticipatory, and RTs over 4000 ms as lapses of attention. You could use the following formula to achieve your aim:

=IF(AND(B2=1, A2>=150, A2<=4000), A2, "")

This states that:

• If B2 equals 1 and A2 is equal to or above 150 ms and A2 is equal to or less than 4000 ms
  • Then C2 should be equal to A2 (basically copying the value from A2 to C2)
• If this is not the case
  • Then C2 should remain empty (indicated by "")

Result:

Quite frequently, you will take into account an additional column indicating the condition. For example, there might be an additional column coding the congruency of the stimulus, with the levels con and incon. If you wanted to have separate columns for your congruent and incongruent RTs, you could do this as illustrated below.

Create a column with all congruent RTs eligible for further analysis:

=IF(AND(B2=1, A2>=150, A2<=4000, C2="con"), A2, "")

Create a column with all incongruent RTs eligibel for further analysis:

=IF(AND(B2=1, A2>=150, A2<=4000, C2="incon"), A2, "")

Please note that letter strings need to be in quotes.

Result:

SUM

SUM is another relevant function. You can add individual values, cell references or ranges or a mix of all three.

For example:

=SUM(A2:A10): Add the values in cells A2 to A10.

=SUM(A2:A10, C2:C10): Add the values in cells A2 to A10 and in cells C2 to C10.

AVERAGE

AVERAGE is another function you will frequently need. It returns the average (arithmetic mean). For example, if the range A1:A20 contains numbers, the formula =AVERAGE(A1:A20) returns the average of those numbers.

Please note that the value 0 will be treated like any other number by this function (thus affecting your mean!), whereas empty cells are ignored.

Variants of AVERAGE are AVERAGEIF and AVERAGEIFS. These variants allow the specification of one or multiple conditions that must be met in order for numbers to be averaged.

Other functions

Other potentially helpful functions are:

• IFS: test multiple conditions and return a value corresponding to the first TRUE result; unlike the IF function, IFS allows you to test more than one condition without nesting
• COUNT: count cells with numbers; will not count empty cells, logical values, text, or error values
• COUNTA: count cells with logical values, text, or error values; will not count empty cells
• COUNTIF: count cells dependent on the outcome of a logical test
• COUNTIFS: count cells dependent on the outcomes of multiple logical tests
• ROWS: returns the number of rows in a range
• MEDIAN: calculate the median
• STDEV.S: standard deviation of a sample
• CORREL: correlation

More details about all these function can be found in the Excel help and online.

40.4 Copying formulas

Formulas can be copied to adjacent cells using what Excel calls the “fill handle”:

If you place your mouse cursor over the fill handle, it will turn into a plus sign. If you now drag the fill handle, you can copy formulas to adjacent cells. Note that this will update relative cell references. For example, if you copy the formula =A1+B1 from row 1 to row 2, the formula will become =A2+B2. Typically, this is the behaviour you want.

However, sometimes you might not want to update a cell reference. For example, you might have calculated the mean of some values and now you would like to calculate the deviation from the mean (i.e., subtract the mean from your other values.)

This is when absolute cell references (see Section 39.8) are required. Use the following formula in cell C2 to subtract the mean from all values: =B2-$B$6 (where $B$6 is the absolute reference to cell B6)

When you copy this formula downwards, only the first cell reference will be updated. That is, the formulas will become:

• Row 3: =B3-$B$6
• Row 4: =B4-$B$6, etc.

If you want to copy a formula to a new cell without changing anything initially, the following procedure typically works best:

• Select the cell with the to-be-copied formula.
• Select the formula in the formula bar (the field next to \(fx\)).
• Press Cmd + C or Ctrl + C.
• Press Esc. (Don’t forget this step!)
• Click on the cell to which the formula should be copied.
• Press Cmd + V or Ctrl + V.

40.5 Excel formulas and functions quiz

What is the essential first character that must be typed when creating any formula in Excel?

The dollar sign ($).The ampersand (&).The equals sign (=).The plus sign (+).

The equals sign (=) must always be the first character when creating a formula in Excel. This tells Excel that the cell content should be interpreted as a formula rather than text.

• False. While the dollar sign is used in Excel for absolute cell references, it is not the required first character for formulas.
• False. The ampersand is used for text concatenation but not for starting formulas.
• True. Every Excel formula must begin with an equals sign.
• False. The plus sign is an arithmetic operator but not the required first character for formulas.

What does the Excel “fill handle” allow you to do?

Insert new rows or columns automatically.Copy formulas to adjacent cells while updating relative cell references.Add borders around selected cells.Lock cell references to prevent them from changing.

The fill handle is a small square in the bottom-right corner of a selected cell that, when dragged, copies formulas to adjacent cells. When copying formulas this way, Excel automatically updates relative cell references. For example, if you copy =A1+B1 to the next row, it becomes =A2+B2.

• False. This requires using the insert command or right-click menu.
• True. The fill handle makes copying formulas efficient by automatically adjusting cell references.
• False. This is done using the borders tool in the formatting menu.
• False. This is done using absolute cell references with dollar signs ($).

When working with Excel functions, which of the following statements are correct? Select all that apply.

The AVERAGE function ignores empty cells in its calculations.The COUNT function will include cells containing text in its count.The COUNTA function will count cells containing logical values, text, or error values.The SUM function treats empty cells the same as cells containing zero.

Two statements are correct. The AVERAGE function does ignore empty cells in its calculations. The COUNTA function counts cells with logical values, text, or error values, but not empty cells. However, COUNT only counts cells with numbers (not text), and SUM treats empty cells as zero, not the same as cells containing zero.

• True. Empty cells are automatically excluded from AVERAGE calculations.
• False. COUNT only counts cells containing numbers, not text.
• True. COUNTA counts any non-empty cell, including those with text, logical values, or errors.
• False. SUM treats empty cells as zero, while cells containing zero are counted as actual values.

Which of the following statements about Excel’s IF function are correct? Select all that apply.

Text values used within IF functions must be enclosed in quotation marks.The IF function always requires exactly three arguments.IF functions can be nested within other IF functions.IF functions can only test for equality, not for greater than or less than.

• True. Any text strings in IF functions must be enclosed in quotation marks.
• True. IF always needs a condition, what to do if true, and what to do if false.
• True. Multiple IF functions can be nested to test multiple conditions.
• False. IF functions can use various comparison operators like >, <, >=, <=.