mixing text and formulae in same cell

G

gvm

I need a worksheet function so that a cell will display "which is 17% of
annual income" where 17% is actually the value that results from a formula
involving two cell references, eg $b$24/$c$24. Is this possible? Any help
will be much appreciated, cheers....
 
G

Govind

Hi,

Use the formula

="which is "&ROUND(($B$24/$C$24)*100,0)&"% of
annual income"

If you want the percentage to be rounded off to a different decimal,
make the necessary correction in the round formula.

For eg. if you want % in two decimal use

="which is "&ROUND(($B$24/$C$24)*100,2)&"% of
annual income"



Regards

Govind.
 
T

Trevor Shuttleworth

Try a custom format:

"which is "0" % of annual income"

Format | Cells | Number tab : Custom category | Type: ...

Regards

Trevor
 
G

gvm

Thanks Trevor, unfortunately I get the error message "Excel cannot use the
number format you typed. Try using one of the built-in formats etc etc". Is
some kind of add-in required?
 
G

Govind

Hi gvm,

Does my suggestion work?

Govind.
Thanks Trevor, unfortunately I get the error message "Excel cannot use the
number format you typed. Try using one of the built-in formats etc etc". Is
some kind of add-in required?

:
Click to expand...
 
M

Max

"which is "0" % of annual income"

There might be a slight typo in Trevor's line above

Try this ..

Select the formula cell
(or select the entire col / range with the formulas)

Click Format > Cells > Number tab
Click on "Custom" under "Category"

Paste this line into the "Type:" box:
"which is "0%" of annual income"

Click OK
 
T

Trevor Shuttleworth

There wasn't a typo ... I copied it from the custom format box. It is
possible that a typo was introduced in transcribing the format ;-)

Try copying and pasting from my response into the custom format box.

That said, the other suggestions work ... but you won't be able to do
arithemetic with the result. The custom format allows you to continue to do
calculations.

Note that if you type 12 into a cell formatted as per my suggestion it will
show up as 12 in the formula box. If you use Max's suggestion it will show
up as 12% ... slightly different result. Depends what you want to do.

Regards

Trevor
 
G

gvm

Excellent, that does it for me, thanks Govind and all others who provided
input. All of it has been valuable to me
 
M

Max

Trevor Shuttleworth said:
There wasn't a typo ..
Click to expand...

Apologies for the wrong inference, Trevor
Note that if you type 12 ..
Click to expand...

I was going on the premise that since the OP mentioned:

" ... results from a formula
involving two cell references, eg $b$24/$c$24..."

that the desired formatting was meant for the results from formulas, not for
formatting data entries, hence the resulting (wrong) inference ..
 
Top