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


The 9 means SUM. The 6 means ignore errors.

Financial Model Guidelines


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


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


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



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.

Random Dates

In Excel dates are stored as numbers.

To generate random dates you can use the RANDBETWEEN function with a start date and an end date.

random DateThe formula in cell C2 is


Cell C2 has been formatted as a date.

This formula is dynamic. Each time Excel calculates it will update and most likely change.

You can use Copy > Paste Values to capture the date(s).