Decimal Puzzle

SAB3012

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.

Paul Newcome

Try 

 

=Number@row + "0"

ron.judenberg112096

This will may the sum 5 decimals long, regardless of how many trailing zeroes are needed

=LEFT(SUM(CHILDREN()) + "00000", FIND(".", SUM(CHILDREN())) + 5)

SAB3012

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.

 

SAB3012

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.