mixing text and formulae in same cell
 

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....

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.



gvm wrote:
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....

Try a custom format:

"which is "0" % of annual income"

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

Regards

Trevor


"gvm" wrote in message
...
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....

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?

"Trevor Shuttleworth" wrote:

Try a custom format:

"which is "0" % of annual income"

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

Regards

Trevor


"gvm" wrote in message
...
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....

Hi gvm,

Does my suggestion work?

Govind.

gvm wrote:
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?

"Trevor Shuttleworth" wrote:


Try a custom format:

"which is "0" % of annual income"

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

Regards

Trevor


"gvm" wrote in message
...

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....

Hi,

try this:

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

Regards,
KL


"gvm" wrote in message
...
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....

"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

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"gvm" wrote in message
...
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?

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


"Max" wrote in message
...
"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

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"gvm" wrote in message
...
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?

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

"Govind" wrote:

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.



gvm wrote:
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....

"Trevor Shuttleworth" wrote:
There wasn't a typo ..

Apologies for the wrong inference, Trevor

Note that if you type 12 ..

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 ..
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----