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

 

The Problem with the MROUND Function

The ROUND function rounds values to decimal places on either side of the decimal point. It is useful and popular. The MROUND function is meant to allow you more flexibility in your rounding calculations. Let’s say you want round to closest 0.05. The MROUND is meant to handle this calculation but unfortunately it provides inconsistent results.