Decimal rounding in cell using text and values

[erniepoe]

Hi all,

I have the following formula, which produces the following text:

"$350M notes at 3.8% repaid in July"

=IF(AND(EH17=0,EG17>0),"$"&ROUND(EG17/1000000,0)&"M notes at "&ROUND(100*$J17,2)&"%"&" repaid in "&EH$155,0)

The part of the formula "ROUND(100*$J17,2)" is where it is pulling out the decimal (3.8%), and you can see i have it rounded to two decimals. The problem is the rate in cell J17 is 3.8%, so Excel is just shortening it to 3.8%, when i prefer it read 3.80%.

Is there a way to fix this within the formula, or will it always just be an after-the-fact edit I will have to make?

Thanks so much for any help.

 

[Alex Blakenburg]

Try replacing this:

ROUND(100*$J17,2)&"%"

With this

TEXT(ROUND($J17,4),"0.0%")

Or since you are actually allowing for 1 more decimal place, this:

TEXT(ROUND($J17,4),"0.0#%")