Number format question

[BobK]

I am using Excel 2007. If I type .01 in a cell, I want the format to return
showing "1 Basis Point" (no quotes needed). If I type .035, I wish to have
the cell show "3.5 Basis Points". The plural/non plural would be nice, but
showing either would be fine.

Thanks!

 

[Ryan Martin]

A1 being the referenced cell, the formula would be written thus:

=CONCATENATE(text(A1*100,"0.0")," Basis Point",IF((a1*100)>1,"s",""))

 

[Sheeloo]

If your nos are in Col A with header rows then enter this in B2 and copy down
=A1*100 & " Basis Points"

If you want the formats, as mentioned, then you will have to enter values as
1, 35,..
and use the CUSTOM FORMAT as
#.# "Basis Points";;
Remove both ;; if you will enter 0 as a valid value and want to see 0 Basis
Points...