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 = tada! 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 19 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
 Smartsheet Customer Resources
 61.3K Get Help
 321 Global Discussions
 197 Industry Talk
 415 Announcements
 4.2K Ideas & Feature Requests
 126 Brandfolder
 153 Just for fun
 124 Community Job Board
 441 Show & Tell
 26 Member Spotlight
 1 SmartStories
 278 Events
 34 Webinars
 7.3K Forum Archives