Spreadsheet functions videos

To support you in preparing for the examinable spreadsheet skills in the Financial Management, Business Strategy and Technology and Strategic Business Management exams we have developed several videos on basic spreadsheet functions. The exam software does not use Excel but a similar spreadsheet software. View these videos to ensure you are comfortable using the functions within your exam.

Spreadsheet functionality webinar

Watch the webinar where we discuss the spreadsheet functions being used in exams. You can practice using this spreadsheet software by accessing the practice software resources here.

Watch the spreadsheet functionality webinar

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.

Download the transcript

This video content is provided by StreamAMG. We ask for your permission before anything is loaded, as they are using cookies and other technologies. You may want to read StreamAMG owner's PA Media Group privacy policy before accepting. If you are happy to view this content, please choose Allow all cookies.

Introduction to the ‘Average’ function

Introduction to the ‘Count if’ function

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

Download the transcript

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.

Download the transcript

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

Download the transcript

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.

Download the transcript

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).

Download the transcript

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.

Download the transcript

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 ‘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).

Download the transcript

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.

Download the transcript

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.

Download the transcript

Introduction to the ‘Rank’ function Description

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.

Download the transcript

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.

Download the transcript

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.

Download the transcript

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.

Download the transcript

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.

Download the transcript

Introduction to absolute cell referencing in Excel

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.

Download the transcript

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 transcript

Introduction to ‘Nested IF statements'

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

ICAEW Excel IFS

This is a short video on the IFS statement in Excel.

ICAEW Excel SUMIFS

This is a short video about the SumIfs function in Excel.

ICAEW Excel TREND

In this video we cover the TREND function in Excel

ICAEW Excel XIRR

This is a short video about the XIRR function in Excel.

Download the spreadsheet template

XLSX (77kb)

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

Download