ICAEW.com works better with JavaScript enabled.
Exclusive

Spreadsheet functions videos

Article

Published: 16 Sep 2021 Update History

Exclusive content
Access to our exclusive resources is for specific groups of students and users.
Contents

To support you in preparing for new examinable skills in the Financial Management, Business Strategy and Technology and Strategic Business Management exams from 2022 we have developed several videos on basic spreadsheet functions. View these videos to ensure you are comfortable using the functions within your exam.

Introduction to the ‘Average’ function

Use the ‘=Average’ function to calculate the average of a set of numbers. The numbers can be in a row, a column, a block, or even spread over multiple locations. Simply point to the numbers and the function will calculate the average.

Introduction to the ‘Count if’ function

Use the ‘=Countif’ function to count how many times a particular event occurs in a set of data.

Introduction to the ‘Count ifs’ function

Use the ‘=Countifs’ function to count how many times multiple criteria occur in a set of data.

Introduction to the ‘Correll’ function

Use the '=Correl’ function to calculate the correlation coefficient between two sets of data

Introduction to the ‘If’ function

Use the ‘=If’ function to return one of two different outputs depending on the outcome of a particular true/false test.

Introduction to the ‘IRR’ function

Use the '=IRR’ function to calculate the internal rate of return of a series of cash flows (the discount rate which results in zero net present value of the cash flows).

Introduction to the ‘MIRR’ function

Use the '=MIRR’ function to calculate the modified internal rate of return of a series of cash.

Introduction to the ‘NPV’ function

Use the '=NPV’ to calculate the Net Present Value of a series of cash flows, given a particular discount rate.

Introduction to the ‘Power’ function

Use the ‘'=Power’ function to raise a number to particular power (multiply it by itself a number of times).

Introduction to the ‘PV’ function

Use the '=PV’ function to calculate the present value of an investment that returns constant periodic payments.

Introduction to the ‘Rank’ function

Use the '=Rank’ function to determine where in a list of numbers a particular number is, in either ascending or descending order.

Introduction to the ‘Rate’ function

Use the '=Rate' function to calculate the effective interest rate of an investment comprising a number of periods, a regular periodic cash flow, the first cash flow and the final cash flow.

Introduction to the ‘StDev’ function

Use the '=STDEV’ function to calculate the Standard Deviation (measure of dispersion) of a set of numbers.

Introduction to the Sum function

Use the '=Sum’ function to add up a set of numbers.

Introduction to the ‘Sumif’ function

Use the '=Sumif’ function to add up numbers if a certain test is passed.

Introduction to ‘Absolute cell referencing’

When pasting a formula somewhere else you will want each particular cell reference in that formula to either move with you, not move at all, slide along the row or move up and down the column.

Introduction to ‘basic number formats’

There are numerous ways you can format data in excel. Some of the more common methods involve decimal points, currency options and percentages, but there are many others.

Introduction to ‘Nested IF statements'

It is possible to "nest" IF statements within other IF statements where there are more than two possible outcomes.

Download the spreadsheet template

XLSX (57kb)

Download the spreadsheet template to practise what is shown in the videos below.

Download