Rounding can often be a problem in a spreadsheet. We’ll start with some relatively simple background stuff then move on to some lesser-known aspects of rounding and displaying rounded values.
What you see isn’t what you calculate – mostly!
The first thing to grasp is that the way Excel displays the contents of a cell generally has no effect on the actual contents of that cell as used in calculations. So a value of 100.05678 will remain as 100.05678 even if the format of the cell is set to display no decimals. There is one important exception to this. There is an option in the ‘Calculations’ tab of the Tools, Options screen that forces Excel to change the contents of the cell to match the ‘precision’ of the format. So if the cell is formatted to display no decimal places, the contents will be permanently rounded to no decimal places – our 100.05678 would become 100.00000:
Before turning on ‘Precision as displayed’
This is an option to be used with great care. It applies to the entire workbook and as soon as it is turned on potentially decreases the accuracy of any figure displayed to fewer decimal places than it actually contains.
A less drastic method of avoiding rounding errors is to use Excel’s ROUND() function. This allows you to round a number to a specific number of decimal places for use in calculations, without changing the original number. Round takes two arguments – the number to be rounded and the number of decimal places to round to. In this case we have used =ROUND(A2,0) to round to round pounds:
The ‘sum’ formula in cell C5 sums the rounded numbers in C2 to C4, so returns 99 rather than 100. If you need the total to be a particular figure, for example if the 100 was a profit share, then you could set the final rounded figure to be the total minus the sum of the other rounded figures. In this case we have entered a figure of 100 in cell E5, then rounded the calculation of the two shares in E2 andE3, with E4 being =E5-SUM(E2:E3)
More fun with rounding
As well as 0 for no decimal places or a positive number for that number of decimal places, you can also enter a negative number as the decimal place argument of the Round function. For example you could enter -3 to round to thousands:
To change the format to display numbers rounded to thousands you would use the following custom format:
The speech marks are necessary for the m, but not the k, because m is used in formatting to signify a month format.
Well, given the name of the site I had to do this one sooner rather than later!
Some of you may remember the peril sensitive sunglasses worn by Zaphod Beeblebrox in the Hitchhiker’s guide to the galaxy. Excel has its own version of this useful accessory which it refers to, rather strangely, as ‘Conditional formatting’.
The point of peril sensitive sunglasses was to protect the wearer from danger by turning completely opaque when any threat was imminent. This concept is highly relevant to spreadsheets which often communicate financial or other information that may be upsetting to the user. In this example we are listing the weekly turnover figures for 7 shops, and also including a ‘peril’ level. Results below the peril level will trigger alarm and dismay, so we will use peril sensitive formatting to hide them. Select the cells containing the values – B4 to B11 and then choose Format, Conditional Formatting. Choose ‘Cell Value Is’, ‘less than’ and then refer to our peril level cell, B1. Then click on the Format button and set the ‘Patterns’ to a black background, and the Font to a black font colour:
Once the ‘Pattern’ and the font have been changed to black the screen should look as follows:
If we click the ‘OK’ button, and then click outside our selected area, we can see the effect of the peril sensitive formatting:
The formatting is dynamic, so if we enter new values, or change the peril level, the formatting will change accordingly:
You can set up to three different formats on a cell or range of cells (press the ‘add’ button on the conditional formatting screen to add a second or third format). So you could, for example, use a ‘traffic lights’ system to set the background colour of all cells with values below a ‘minimum’ level to red, those with values above a ‘maximum’ value to green and the ones in between to amber. In Excel 2007 conditional formatting has been greatly enhanced to enable the use of a much wider range of formats and icons to highlight certain values.
After an Excel course I was running at the highly impressive Sage HQ in Newcastle, one of the delegates pointed out that it’s all very well protecting your spreadsheets, but how do you stop users cutting and pasting between unlocked data cells? The effect of this is to potentially mess up the references to the data cells affected even in the protected cells. Of course it would be possible to write VB code to prevent this, or to use form fields or some other method of data entry, but is there a simpler way?
In Excel XP and later, the protection capabilities have been substantially enhanced and allow choice as to what a user can and cannot do. In addition, there is the ability to ‘Allow users to edit ranges’. I wondered whether this might be the solution, so tried leaving all the cells in a sheet locked, but then adding the data cells to an ‘allowed range’ – in this case without setting a password. This did seem to work. When the sheet was protected the protection did not allow cutting and pasting in any locked cells (which was of course all the cells – including those in the allowed range), but because of the allowed range, with no password, data could be entered in these cells. I don’t know whether this is a well-known feature and obvious method of implementing protection since XP changed the protection options, or an unintended but useful consequence. I also haven’t yet tested the way it works thoroughly and am waiting to hear how well the solution works for the person who asked the question in the first place.