TEXT Function with decimals

[ldoodle]

Hey,

I have this formula: =TEXT(CONVERT(G4,C4,"Gibyte"),"#,##0.00#######")&K$1

K$1 is either GB or TB

In one example the calculated value is: 640.00GB
In another example, using Tibyte instead, the calculated value is: 0.625TB

I need decimals in only some cases. But I'd prefer 640.00GB to be shown as 640GB, and 1.00TB to be shown as 1TB.

I can change the TEXT format to #,##0.######### but this results in 640.GB and 1.TB

So, how do I either make the . optional, or have 2 different TEXT formats: if all the decimals are 0, use something like #,###0 and if any decimal is not zero, use the current format - I know IF funciton, just not how to detect for 'all zero decimals'

Thanks

 

[RoryA]

You can test if the value is equal to INT(value). If so, there are no decimals. Sadly there is still no simple option for an optional decimal point. I would also suggest that you update your profile to indicate which version(s) of Excel you are using.

 

[ldoodle]

Thanks, that worked.

 

[Peter_SSs]

I'm not sure exactly what formula you ended up with but as I understand it, this structure might also do what you want?

=SUBSTITUTE(youroriginalformula,".00"&K$1,K$1)

 

[ldoodle]

PS: I currently have Excel 365 but need to support at least 2016.

 

[ldoodle]

I'm not sure exactly what formula you ended up with but as I understand it, this structure might also do what you want?

=SUBSTITUTE(youroriginalformula,".00"&K$1,K$1)

=TEXT(CONVERT($G4,$C4,"byte"),IF(CONVERT($G4,$C4,"byte")=INT(CONVERT($G4,$C4,"byte")),"#,##0","#,##0.00#################"))&I$1

But I will give your option a go also. Thanks

 

[Peter_SSs]

Then for that same formula, this would be my shorter equivalent
=SUBSTITUTE(TEXT(CONVERT($G4,$C4,"byte"),"#,##0.00#################")&I$1,".00"&I$1,I$1)

 

[ldoodle]

Nice, thanks

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.