Can't find function

[ScotTFO]

I am not sure if something like this even exists.

I am trying to add a formula that's pretty extensive and the end result comes out looking like this SOMETIMES.

"545.0000003 KB"

Is there a function I can add to the formula to trim the numbers down to 2 decimal places?

I guess I could use the LEFT function but I was just wondering if there was a better wya that would do rounding too.

 

[Jonmo1]

I"m assuming your formula is a concatenate of some sort...

the part of the formula that produces the NUMBER, you can use TEXT to set the dicimal places..

=CONCATENATE(TEXT(formula that produces the number,"0.00")," KB")

Note that the number will be ROUNDED...

 

[RoryA]

When you are building up the value, use the ROUND function on the numbers before you add the "KB" (or MB or GB) part.
=ROUND(1080/1024,2)
for example.

 

[Mike Blackman]

Hi Scot,

Maybe Something like this?

=ROUND(SUBSTITUTE(A1,RIGHT(A1,2),""),2)&" "&RIGHT(A1,2)

 

[ScotTFO]

I tested them all and I think for my needs this one turned out to be the best solution but they all do work and I appreciate the help!

Yeah it's a bit of a beast of a code, and originally I did it all in VBA and it worked except now they are asking to have the cells update realtime so I am trying to replace all the VBA coding with these formulas so I am ending up with beasts, like this...

=IF(F16-D16>=1073741824,TEXT((F16-D16)/1024/1024/1024,"0.00")&" GB",IF(F16-D16>=1048576,TEXT((F16-D16)/1024/1024,"0.00")&" MB",IF(F16-D16>=1024,TEXT((F16-D16)/1024,"0.00")&" KB",IF(F16-D16>=-1024,TEXT((F16-D16),"0.00"),IF(F16-D16>=-1048576,TEXT((F16-D16)/1024,"0.00")&" KB",IF(F16-D16>=-1073741824,TEXT((F16-D16)/1024/1024,"0.00")&" MB",IF(F16-D16<1073741824,TEXT((F16-D16)/1024/1024/1024,"0.00")&" GB")))))))

The code is taking 2 cells that contain the byte sizes of folders that are pulled using VBA and the FileSystemObject. This formula is doing the math and finding out how much a user is over their alloted space and then formatting it into a common file size format that someone using this can understand.

Thanks again guys!