Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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.... |
#2
|
|||
|
|||
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.... |
#3
|
|||
|
|||
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.... |
#4
|
|||
|
|||
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.... |
#5
|
|||
|
|||
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.... |
#6
|
|||
|
|||
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.... |
#7
|
|||
|
|||
"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? |
#8
|
|||
|
|||
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? |
#9
|
|||
|
|||
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.... |
#10
|
|||
|
|||
"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 ---- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Searching TEXT in formulae, rather than results of formulae | Excel Worksheet Functions | |||
Dynamic formulae - similar to lotus 123 for excel | Excel Discussion (Misc queries) | |||
how can i test whether cell contains keyed constant or a formulae | Excel Worksheet Functions | |||
imported data not recognised in formulae | Excel Discussion (Misc queries) | |||
Change column Number vrs Text | Excel Worksheet Functions |