I am trying to get a formula to display a currency total using the text() function and the concatenate() function but it drops the trailing zero. Here is the formula that I am using now:

=IF(H14>I14,CONCATENATE("Over by: ",TEXT(H14-I14,"$#,###.##")),CONCATENATE(TEXT(I14-H14,"$#,###.##")," was not spent")))

The formula is comparing two figures:

Col H: Total spent

Col I: Budget toal

If total spent is greater than the budget total then the first condition will display "$xxx.xx over". If the total spent was less than budget then it should display "$xxx.xx was not spent". However, it is displaying "$100.2 not spent" instead of "$100.20 not spent" if the cents has a zero in the pennies place.

Any help on getting this to work the way I would like without the use of VBA would be greatly appreciated!

Thanks,

Olli

=IF(H14>I14,CONCATENATE("Over by: ",TEXT(H14-I14,"$#,###.##")),CONCATENATE(TEXT(I14-H14,"$#,###.##")," was not spent")))

The formula is comparing two figures:

Col H: Total spent

Col I: Budget toal

If total spent is greater than the budget total then the first condition will display "$xxx.xx over". If the total spent was less than budget then it should display "$xxx.xx was not spent". However, it is displaying "$100.2 not spent" instead of "$100.20 not spent" if the cents has a zero in the pennies place.

Any help on getting this to work the way I would like without the use of VBA would be greatly appreciated!

Thanks,

Olli

Last edited: