Is there a way to shorten the numbers? So instead of $789,137,674.05 It can be 789 and the title say
million? I'm pulling data from else where, but I'd like to shorten it so it's easier to read. Here is the formula so far:
=ROUND(SUMIFS({Value}, {Sheet}, OR(@cell = "REXUS Activation", @cell = "Pending REXUS"), {Lease Exp. Date}, AND(@cell >= DATE(YEAR(TODAY()) - 2, MONTH(TODAY()), DAY(TODAY())), @cell <= TODAY())), 2)
Best Answer
-
Hi, Abbie.
One approach is to divide your answer by 1000000 and then take the integer of that, INT(). You can drop the ROUND() function since you don't need the result to that scale.
=INT(SUMIFS({Value}, {Sheet}, OR(@cell = "REXUS Activation", @cell = "Pending REXUS"), {Lease Exp. Date}, AND(@cell >= DATE(YEAR(TODAY()) - 2, MONTH(TODAY()), DAY(TODAY())), @cell <= TODAY()))/1000000)
Example: INT(789.13767405...) = 789
Sometimes you'll want one decimal place (e.g., $789.1billion). To do that, move your divisor one decimal to the left (100 000 instead of 1 000 000), take the integer, and then divide the result by 10.
Example: INT(7891.3767405...)/10 = 789.1
Answers
-
Hi, Abbie.
One approach is to divide your answer by 1000000 and then take the integer of that, INT(). You can drop the ROUND() function since you don't need the result to that scale.
=INT(SUMIFS({Value}, {Sheet}, OR(@cell = "REXUS Activation", @cell = "Pending REXUS"), {Lease Exp. Date}, AND(@cell >= DATE(YEAR(TODAY()) - 2, MONTH(TODAY()), DAY(TODAY())), @cell <= TODAY()))/1000000)
Example: INT(789.13767405...) = 789
Sometimes you'll want one decimal place (e.g., $789.1billion). To do that, move your divisor one decimal to the left (100 000 instead of 1 000 000), take the integer, and then divide the result by 10.
Example: INT(7891.3767405...)/10 = 789.1
-
Thank you @tfcRobert!!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.4K Get Help
- 396 Global Discussions
- 213 Industry Talk
- 448 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 133 Just for fun
- 56 Community Job Board
- 454 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 293 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!