Concatenate with text() to format as currency truncates trailing zero

[Me_In_Va]

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

 

[Joe4]

You have the wrong format selected. ".##" will drop trailing zeroes in a decimal. Choose ".00" instead.

Try:

[COLOR=#333333]=IF(H14>I14,CONCATENATE("Over by: ",TEXT(H14-I14,"$#,###.[/COLOR][COLOR=#ff0000]00[/COLOR][COLOR=#333333]")),CONCATENATE(TEXT(I14-H14,"$#,###.[/COLOR][COLOR=#FF0000]00[/COLOR][COLOR=#333333]")," was not spent")))[/COLOR]

 

[Me_In_Va]

Thank you! Worked like a charm!

You have the wrong format selected. ".##" will drop trailing zeroes in a decimal. Choose ".00" instead.

Try:

[COLOR=#333333]=IF(H14>I14,CONCATENATE("Over by: ",TEXT(H14-I14,"$#,###.[/COLOR][COLOR=#ff0000]00[/COLOR][COLOR=#333333]")),CONCATENATE(TEXT(I14-H14,"$#,###.[/COLOR][COLOR=#FF0000]00[/COLOR][COLOR=#333333]")," was not spent")))[/COLOR]

 

[Scott Huish]

A shorter formula:

=TEXT(H14-I14,"""Over by: ""$#,###.00;$#,###.00 "" was not spent"";""$.00 was not spent""")

 

[Rick Rothstein]

A shorter formula:

=TEXT(H14-I14,"""Over by: ""$#,###.00;$#,###.00 "" was not spent"";""$.00 was not spent""")

Maybe the OP would like to take advantage of the H14=I14 condition and tailor the message for it...

=TEXT(H14-I14,"""Over by: ""$#,###.00;$#,###.00 "" was not spent"";""Exact amount was spent""")