Decimal Puzzle
This one might be fun.
I have a number....let's say 32.1204
But what I really want is to have it be 32.12040 because I want to turn it into a string and peel off the right two numbers.
I've tried ROUND(number,5) but that doesn't work.
I can hit the increase decimal button and make it show as 32.12040, but then if I try to turn it into a string with =""+number, that right most "0" goes away and I'm back to 32.1204
So with my limited knowledge of Smartsheet functions, I came up with this.....using LEN and FIND.
=IF(LEN("" + SUM(CHILDREN())) - FIND(".", "" + SUM(CHILDREN())) = 4, "" + (SUM(CHILDREN()) + "0"), "" + SUMIF(CHILDREN()))
-------------------------------------
So the ""+SUM(CHILDREN())) adds up all the children cell values and the ""+ turns it into a string.
I then look at the length of the string and subtract the location of the FIND decimal point. So the string length is 7 and the decimal point is at location 3 gives me the value of 4.
So then IF the value is 4, then again, convert the value to a string and concatenate a "0" on the end to make it "32.12040"
Can somebody come up with a simpler way?
Note: Interesting that in the "true" case, without the parens around SUM(CHILDREN())+"0" I get an invalid operation error.
Comments
-
Try
=Number@row + "0"
-
This will may the sum 5 decimals long, regardless of how many trailing zeroes are needed
=LEFT(SUM(CHILDREN()) + "00000", FIND(".", SUM(CHILDREN())) + 5)
-
So many people in the world so much smarter than me
Excellent! Ron
I didn't need all the zeros, but it works great.
Background: I'm carrying two variables in a single column (% complete + task count*0.00001) After summing them all up, I seperate the variables from left (% complete) and right side (task count), divide them and get the value (project % complete) I'm interested in.
Your cleaner formula serves perfectly. Many thanks.
=VALUE(LEFT(SUM(CHILDREN()) + "0", 5)) / VALUE(RIGHT(SUM(CHILDREN()) + "0", 3))
I previously restricted my %complete to only 2 decimal places, so 5 places from the left side will always take the correct % complete total, and 3 places from the right (cause I'll never have a project with more than 999 tasks) for the task count = ta-da! project completion status.
Think I'll take the rest of the day off.
-
Okay - oops!
My above only works if the right side count ends a factor of 10. Any 1-9 digit ends up as 6 decimal places and blows up.
So back to Ron's formula and put the FIND back in, but am now using MID and starting at FIND"."
=VALUE(LEFT(SUM(CHILDREN()) + "0", 5)) / VALUE(MID(SUM(CHILDREN()) + "0", (FIND(".", SUM(CHILDREN()) + "0") + 3), 3))
This works!
Thanks again.
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 468 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 64 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives