Almost everyone has created Excel reports that round numbers to the thousands or millions. With such reports there are sometimes situations where the displayed total does not match the displayed components.
We can see the revenue sums correctly when looking at the natural value in the table below.
However, when we round the numbers to thousands using the number format, the revenue does not display correctly.
However, when we round the numbers to thousands using the number format, the revenue does not display correctly.
We know that the values are rounded and hence the cause for perceived variance, but an executive who looks at this may not realize it and could think that the report is incorrect or missing data. We can use the SUM and ROUND functions together to display a total that matches the displayed numbers above it.
What we’ll do is nest the ROUND function inside the SUM function. Using ROUND in this situation, you can reference a range instead of a single cell. For the second parameter in ROUND, you would enter a negative number that represents the number of digits you want to round off; -3 for thousands and -6 for millions, etc.
The resulting numbers will display as below.
IBM Planning Analytics, which TM1 is the engine for, is full of new features and functionality. Not sure where to start? Our team here at Revelwood can help. Contact us for more information at info@revelwood.com. And stay tuned for more Planning Analytics Tips & Tricks weekly in our Knowledge Center and in upcoming newsletters!
Read more Excel Tips & Tricks:
IBM Planning Analytics Tips & Tricks: Excel Keyboard Navigation
IBM Planning Analytics Tips & Tricks: Excel OFFSET Function
IBM Planning Analytics Tips & Tricks: Excel LET Function
