Comments on: Number Formats

By: XLCubed

XLCubed — Mon, 18 Nov 2019 09:08:39 +0000

Hi Rob, if you go to Options > Workbook Options from the XLCubed ribbon, under the Formula Options tab you can set a value to replace null values with.

By: Rob Allison

Rob Allison — Thu, 07 Nov 2019 19:44:16 +0000

I am having trouble when XL3Lookup returns nothing from the cube. I would like it to return zero because the alternative just shows a blank cell and users are worried the formula did not work.

By: AdamV

AdamV — Mon, 12 Dec 2011 21:14:59 +0000

Aside from the obvious “red for negative numbers”, a personal favourite of mine for using colour in number formats is to colour zeroes eg blue, especially when showing numbers with little precision eg % growth or variance to only one decimal – it is quite possible that some figures will be very nearly zero and appear as 0.0, so it is handy to be able to quickly identify any which are exactly zero as that often indicates a slightly different situation.
I also advocate using the 0, or 0,, approach very often with Pivot Tables – a summary should be easy to read and interpret, and if reducing the precision helps to do that, all the better. In these cases I would not add the label repeatedly in every value (which is fine in other contexts) but in the field name eg “Sales (GBP Millions)” or “Cost (USD ‘000)”

By: xlcubedblog

xlcubedblog — Tue, 09 Aug 2011 07:18:24 +0000

Ola, it is possible to use negative time formats without transforming them into text. This Microsoft article (http://support.microsoft.com/kb/182247) explains how.

Note that you should be careful using the 1904 date system if you need to link to other workbooks, since you can get errors if you try to compare dates in different formats.

By: Ola

Ola — Mon, 08 Aug 2011 15:59:38 +0000

What I miss is the ability to show negative time format.
Example: -01:30

If it is transformed into text the ability mark cells to sum is lost.