08-01-2022
01:40 PM
- last edited on
05-24-2023
11:37 AM
by
JackLacava
Hello,
Is there a way to make cells display numerical data in the millions of dollars amount? As an example 1,500,000 would be displayed as 1.5
I have looked around but did not find much information on the NumberFormat paramater so it is kind of confusing in terms of synthax.
Thank you in advance for your time and participation.
Solved! Go to Solution.
08-02-2022 07:50 AM
Hi, then you can try the conditional formatting.
For example, you can choose to only apply the scale to the rows without "percentage" in the row names using the below:
If (RowName DoesNotContain 'percentage') Then
Scale = 6, NumberFormat = N1
End If
08-01-2022 02:21 PM
It is the excel syntax for number formats.
Is there a reason, not to use the scale option?
08-01-2022 03:00 PM
Hello,
No there is no reason not to use the scale option. Do you think that it is better suited?
08-01-2022 05:13 PM - edited 08-01-2022 05:15 PM
Yes, if you set it to 6 and then use number format N1 or N2, then you should get what you are looking for.
If you want to use format, you can try #,, #.0,, or #.00,,
If you google excel custom format, you will get plenty of results explaining the concepts behind it.
08-01-2022 06:00 PM
Hello, I tried NumberFormat = N1, Scale = 6 and it works in terms of displaying the numbers in millions, however it messed up some percentage calculations that I was making beforehand. I think this is because of the scaling. Is there a way to get around it?
08-02-2022 07:50 AM
Hi, then you can try the conditional formatting.
For example, you can choose to only apply the scale to the rows without "percentage" in the row names using the below:
If (RowName DoesNotContain 'percentage') Then
Scale = 6, NumberFormat = N1
End If
08-02-2022 09:22 AM
Thank you Leasa, this works for me in this instance!
08-01-2022 04:18 PM
Hey FM,
Take a look at this thread. I provided an example with attached syntax on how we use UD8 as a Reporting Helpers dimension and allow the users to run reports or use the excel add-in to dynamically scale their numbers (whole numbers, scaled to thousands, and scaled to millions).
Re: XFgetcell pulls in future months as YTD negative - OneStream Community (onestreamsoftware.com)
08-01-2022 04:51 PM
aricgresko, I appreciate your post but it seems more involved than I was hoping for. I would expect that changing number formatting should be easier than that.
08-01-2022 05:18 PM
Hi aricgresco
This is very helpful, if you want to make different versions of scaling available for everybody, but maybe too much if you only need it for a report.
08-01-2022 06:32 PM
For a single report, yes my suggestion would be more involved than needed and Christian's suggestion is the way to go. I'm assuming you would want to drive consistency among all reports, so a good way to do that is to leverage the UD8 scaling idea. Either way, I think you have a few good options!