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
Excel - use formatting to multiply
- 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
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
Jodi Ryan Family Lawyer
- Steve Rowe
- Site Admin
- Posts: 2417
- 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
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
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
www.infocat.co.uk
- 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
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
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
Jodi Ryan Family Lawyer
- 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
Didn't notice that a moment ago. That'll do it.Steve Rowe wrote: Or just do it with a scaled consolidation in Tm1 and reference that??
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
Jodi Ryan Family Lawyer
- 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
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
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
Production: Planning Analytics 64 bit 2.0.5, Windows 2016 Server. Excel 2016, IE11 for t'internet
-
- MVP
- Posts: 3123
- 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
I don't think you can, in the past I also couldn't find a way to achieve this.
Wim
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
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