Learning Excel’s Function Arguments

When you start to use a function it can take some time to learn the arguments required and understand what Excel expects for each argument. Eg should it be a cell or a range or either?

When you have the start of a function in the formula bar, you can either press Ctrl + a or click the fx symbol on the left of the formula bar – see image below.

function_1

In the image above, the argument in square brackets [range_lookup] is optional. Square brackets around an argument mean it is optional.

This will display a dialog with a listing of the arguments required by a function. The bold names are required, the non-bold names are optional.

function_2

This listing provides a lot more detail on what Excel is expecting for each function argument. This helps you learn more about how to create and use the function.

[related_posts_by_tax taxonomies="category,post_tag"}

Summing a range with Errors

If you have a column of values with errors, but you want to see what the values add up to, use the AGGREGATE function (added in Excel 2010).

If column A has the values and errors use

=AGGREGATE(9,6,A:A)

The 9 means SUM. The 6 means ignore errors.

Financial Model Guidelines

RSM UK
2016-07-06

“A new financial modelling guide authored by the ICAEW Corporate Finance Faculty and RSM aims to help businesses of all sizes plan and reduce risk. ” – website

If you use or build financial models then this pdf guide may be worth downloading – its free and no email is required – at least when I downloaded it.

Happy reading.

Don’t forget you can read pdfs on your kindle and iPad.

 

Always Refer to Cell A1

If you need to ALWAYS refer to cell A1, regardless of whether row or columns are inserted or deleted, then use the following formula.

=INDIRECT("A1")

This will always display the entry in cell A1 on the current sheet.

Another formula that always refers to cell A1 on the current sheet is

=INDEX(1:1048576,1,1)


Days in Month Formula

If you need to calculate how many days in a month, you can use two functions together.

Assuming cell A1 has a date within the month, you can use

=DAY(EOMONTH(A1,0))