Excel - use formatting to multiply

Not related to a specific OLAP tool. (Includes forum policies and rules).
Post Reply
User avatar
Martin Ryan
Site Admin
Posts: 1988
Joined: Sat May 10, 2008 9:08 am
OLAP Product: TM1
Version: 10.1
Excel Version: 2010
Location: Wellington, New Zealand
Contact:

Excel - use formatting to multiply

Post by Martin Ryan »

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
Please do not send technical questions via private message or email. Post them in the forum where you'll probably get a faster reply, and everyone can benefit from the answers.
Jodi Ryan Family Lawyer
User avatar
Steve Rowe
Site Admin
Posts: 2410
Joined: Wed May 14, 2008 4:25 pm
OLAP Product: TM1
Version: TM1 v6,v7,v8,v9,v10,v11+PAW
Excel Version: Nearly all of them

Re: Excel - use formatting to multiply

Post by Steve Rowe »

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
Technical Director
www.infocat.co.uk
User avatar
Martin Ryan
Site Admin
Posts: 1988
Joined: Sat May 10, 2008 9:08 am
OLAP Product: TM1
Version: 10.1
Excel Version: 2010
Location: Wellington, New Zealand
Contact:

Re: Excel - use formatting to multiply

Post by Martin Ryan »

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
Please do not send technical questions via private message or email. Post them in the forum where you'll probably get a faster reply, and everyone can benefit from the answers.
Jodi Ryan Family Lawyer
User avatar
Martin Ryan
Site Admin
Posts: 1988
Joined: Sat May 10, 2008 9:08 am
OLAP Product: TM1
Version: 10.1
Excel Version: 2010
Location: Wellington, New Zealand
Contact:

Re: Excel - use formatting to multiply

Post by Martin Ryan »

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
Please do not send technical questions via private message or email. Post them in the forum where you'll probably get a faster reply, and everyone can benefit from the answers.
Jodi Ryan Family Lawyer
User avatar
Steve Vincent
Site Admin
Posts: 1054
Joined: Mon May 12, 2008 8:33 am
OLAP Product: TM1
Version: 10.2.2 FP1
Excel Version: 2010
Location: UK

Re: Excel - use formatting to multiply

Post by Steve Vincent »

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

:?
If this were a dictatorship, it would be a heck of a lot easier, just so long as I'm the dictator.
Production: Planning Analytics 64 bit 2.0.5, Windows 2016 Server. Excel 2016, IE11 for t'internet
Wim Gielis
MVP
Posts: 3103
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.9.18
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: Excel - use formatting to multiply

Post by Wim Gielis »

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

Wim
Best regards,

Wim Gielis

IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
Post Reply