TM1 Forum

In Excel it's nice and straightforward to format 1,000 to look like 1, by using custom formatting #,

What about going the other way? Does anyone know of a way of formatting 1 to look like 1000? At the moment I've got each cell being multiplied by 1000, but I want to do it without multiplying as it pollutes the DBRW formula.

Martin

I've not read all the way through but I think this should help

http://vbatips.com/2007/12/10/scaling-numbers-in-excel/

Or just do it with a scaled consolidation in Tm1 and reference that??

Sorry the above link is the wrong way.

Try this

#",000"

seems to work...
Cheers

Kinda. That rounds the figure, then adds three zeroes. E.g. 2.45 becomes 2,000. It seems to take whatever is in the quotes literally and tacks that on the end of whatever you've formatted to.

Martin

Steve Rowe wrote: Or just do it with a scaled consolidation in Tm1 and reference that??

Didn't notice that a moment ago. That'll do it.

Still, I'd like to hear if anyone can manage to do it with formatting as it'd be a neat trick to have in the hat.

Martin

Hmm, i had a need to do this a few years ago and never found a way in Excel. Had to store data in TM1 differently for different measures (some Mil, some Thou etc) which is fine until measure values change so much that the base needs to be changed.

Ozgrid is pretty damn good for Excel help, but even there it doesn't seem to show any way of doing it
http://www.ozgrid.com/Excel/CustomFormats.htm

I don't think you can, in the past I also couldn't find a way to achieve this.

Wim