For ex: I have $4,478,999. I would like to convert it to $4.479,000 or say nearest 1000 value and then cut the last three digits at last to get $4,479 only.
Any idea how I can get it?
@Genevieve P. - Thanks so much. I was able to make the changes to my sheet and ultimately to the dashboard that I will be presenting to my manager, all with your help.
@Genevieve P. - Yes, that makes sense as it won't be in a number format anymore after adding K. But am still using your formula and just added a text to dashboard saying numbers are in thousands.
Is it always going to be a 7 digit number? If so, that's easy. Otherwise, that gets complex depending on what numbers you're dealing with regularly. One formula might work for the number in your example but then when you have a number like 250,000 it's not going to act the same and would throw out what you consider an incorrect value.
It may be 7, 8 9 or 10 digit numbers.
I know there is a formula to truncate characters =LEFT (Cellreference, (LEN(Cellreference) - 3)) but how can i use it with my current formula?
Current formula =MROUND(Data30, 1000)
@Mike TV - Thanks a lot for help. I just did post my comment in clarification of my question.
Hi @Nancy Patel
Try something like this:
=ROUND([Cell Reference]@row, -3) * 0.001
@Genevieve P. - in the below example I need K (to represent 1000) to be added at the end. How can I do that? Like I need $4,478K, I need both $ sign and K at the end. Am not able to get it. Any ideas?
You can add letters to numbers by using the + sign and quotes around the letters, like so:
="$" + (ROUND(MROUND(Data34 + Data35, 1000), -3) * 0.001) + "K"
@Genevieve P. - You make things so easy to follow. Appreciate all your help! Hope you have an amazing new year!
I'm glad I could help! I hope you have an amazing new year as well 🙂
@Genevieve P. - So I was able to add $ and K signs but then the number format is not correct. I have lost the commas. in between numbers. How can I achieve it? Formula used now - ="$" + ROUND(MROUND(Data35 + Data36, 1000), -3) * 0.001 + "K"
Ah yes, my apologies. If you're using the formatting toolbar at the top to add in the commas then using formulas to add data will get rid of the formatting. This is because your formula is outputting text now, instead of numbers.
You could go back to the original formula and use formatting to add in the $ sign, however we cannot add the K and keep the commas. Does that make sense?
Hey Smart Heads, I have another question about our beloved topic of formulas. Background: I have a autogerated column with row created date. In the file, rows are added in 2 ways: In bulk, by senior auditor Ad-hock by auditor I would like to diferentiate these two types of inflow. I came up with an idea of finding…
We had an employee leave and we are unable to edit the fields directly in the smart sheet. They had some kind of system set up where their fields were being populated via a dynamic view. I can not longer access this view because the person in question left our org. Is there a way to get access to that view now their…
I have an automated workflow that requests a form if certain fields are blank. It's triggered when a new row is added or changed. More specifically, the automated flow is activated when a required field is added or changed. This works fine. However, the automated flow doesn't issue a follow up form request when some of the…
Help shape the future of Smartsheet.
Share your ideas and feature requests.
©2023. All Rights Reserved Smartsheet Inc.