| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Substitute Text for Plus or Minus
Is there a way to substitute the words "UP" or "ROSE" and "DOWN" or "FELL"
for a calcualted value referenced in another cell in a concatenated statement combining text and the cell reference? (So, instead of saying "In May, such and such a value was -10%", I would be able to say it "fell 10%"). DOUG ECKERT |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Substitute Text for Plus or Minus
Say that your result of -10% is in Cell B4, then the following formula would
yield a result of "fell 10%" =IF(B4<0,"Fell "&-B4*100&"%","Rose "&B4*100&"%") You can replace Fell and Rose with Down and Up if you wish. Hope this helps! If so, click yes please! "DOUG" wrote: Is there a way to substitute the words "UP" or "ROSE" and "DOWN" or "FELL" for a calcualted value referenced in another cell in a concatenated statement combining text and the cell reference? (So, instead of saying "In May, such and such a value was -10%", I would be able to say it "fell 10%"). DOUG ECKERT |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Substitute Text for Plus or Minus
If A1 contained the percentage, you could use a formula like:
="In May, such and such a value " &TEXT(A1,"""rose ""0%;""fell ""0%;""stayed even""") The =text() function has 4 parts: positive;negative;zero;text I didn't touch the text portion. DOUG wrote: Is there a way to substitute the words "UP" or "ROSE" and "DOWN" or "FELL" for a calcualted value referenced in another cell in a concatenated statement combining text and the cell reference? (So, instead of saying "In May, such and such a value was -10%", I would be able to say it "fell 10%"). DOUG ECKERT -- Dave Peterson |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Substitute Text for Plus or Minus
Thank you, Dave!
"Dave Peterson" wrote: If A1 contained the percentage, you could use a formula like: ="In May, such and such a value " &TEXT(A1,"""rose ""0%;""fell ""0%;""stayed even""") The =text() function has 4 parts: positive;negative;zero;text I didn't touch the text portion. DOUG wrote: Is there a way to substitute the words "UP" or "ROSE" and "DOWN" or "FELL" for a calcualted value referenced in another cell in a concatenated statement combining text and the cell reference? (So, instead of saying "In May, such and such a value was -10%", I would be able to say it "fell 10%"). DOUG ECKERT -- Dave Peterson |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Substitute Text for Plus or Minus
Thank you, lightbulb!
"lightbulb" wrote: Say that your result of -10% is in Cell B4, then the following formula would yield a result of "fell 10%" =IF(B4<0,"Fell "&-B4*100&"%","Rose "&B4*100&"%") You can replace Fell and Rose with Down and Up if you wish. Hope this helps! If so, click yes please! "DOUG" wrote: Is there a way to substitute the words "UP" or "ROSE" and "DOWN" or "FELL" for a calcualted value referenced in another cell in a concatenated statement combining text and the cell reference? (So, instead of saying "In May, such and such a value was -10%", I would be able to say it "fell 10%"). DOUG ECKERT |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Substitute Text for Plus or Minus
Dave: I noticed that when I place a period at the end of these VBA
statements they become invalid. Is there a way to add a period in such a way as to NOT invalidate the preceding statement? DOUG "Dave Peterson" wrote: If A1 contained the percentage, you could use a formula like: ="In May, such and such a value " &TEXT(A1,"""rose ""0%;""fell ""0%;""stayed even""") The =text() function has 4 parts: positive;negative;zero;text I didn't touch the text portion. DOUG wrote: Is there a way to substitute the words "UP" or "ROSE" and "DOWN" or "FELL" for a calcualted value referenced in another cell in a concatenated statement combining text and the cell reference? (So, instead of saying "In May, such and such a value was -10%", I would be able to say it "fell 10%"). DOUG ECKERT -- Dave Peterson |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Substitute Text for Plus or Minus
"Dave Peterson" wrote: If A1 contained the percentage, you could use a formula like: ="In May, such and such a value " &TEXT(A1,"""rose ""0%;""fell ""0%;""stayed even""") The =text() function has 4 parts: positive;negative;zero;text I didn't touch the text portion. DOUG wrote: Is there a way to substitute the words "UP" or "ROSE" and "DOWN" or "FELL" for a calcualted value referenced in another cell in a concatenated statement combining text and the cell reference? (So, instead of saying "In May, such and such a value was -10%", I would be able to say it "fell 10%"). DOUG ECKERT -- Dave Peterson Dave: The cell reference is dropping out for some reason. I entered =(($G$32&" 09 - Compared to last month, RVUs "&TEXT($J$32,"""rose""0%;""fell"";""were unchanged"""))) The resulting sentence says: MAY 09 - Compared to last month, RVUs fell The cell reference $J$32 does not appear in the result. Do you know why? DOUG |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Substitute Text for Plus or Minus
Dave: I got it to work using the following instructions.
=$G$32&" 09 - Compared to last month, RVUs "&TEXT($J$32,"""rose"" 0%;""fell"" 0%;"" were unchanged""") I had left off the "0%" after the word "fell"! I would still like to know how to place a period at the end of the sentence without invalidating the instructions, if you do not mind. DOUG "Dave Peterson" wrote: If A1 contained the percentage, you could use a formula like: ="In May, such and such a value " &TEXT(A1,"""rose ""0%;""fell ""0%;""stayed even""") The =text() function has 4 parts: positive;negative;zero;text I didn't touch the text portion. DOUG wrote: Is there a way to substitute the words "UP" or "ROSE" and "DOWN" or "FELL" for a calcualted value referenced in another cell in a concatenated statement combining text and the cell reference? (So, instead of saying "In May, such and such a value was -10%", I would be able to say it "fell 10%"). DOUG ECKERT -- Dave Peterson |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Substitute Text for Plus or Minus
just add another string
=$G$32&" 09 - Compared to last month, RVUs "&TEXT($J$32,"""rose"" 0%;""fell"" 0%;"" were unchanged""")&"." DOUG wrote: Dave: I got it to work using the following instructions. =$G$32&" 09 - Compared to last month, RVUs "&TEXT($J$32,"""rose"" 0%;""fell"" 0%;"" were unchanged""") I had left off the "0%" after the word "fell"! I would still like to know how to place a period at the end of the sentence without invalidating the instructions, if you do not mind. DOUG "Dave Peterson" wrote: If A1 contained the percentage, you could use a formula like: ="In May, such and such a value " &TEXT(A1,"""rose ""0%;""fell ""0%;""stayed even""") The =text() function has 4 parts: positive;negative;zero;text I didn't touch the text portion. DOUG wrote: Is there a way to substitute the words "UP" or "ROSE" and "DOWN" or "FELL" for a calcualted value referenced in another cell in a concatenated statement combining text and the cell reference? (So, instead of saying "In May, such and such a value was -10%", I would be able to say it "fell 10%"). DOUG ECKERT -- Dave Peterson -- Dave Peterson |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Substitute Text for Plus or Minus
Dave: Thanks. I had tried that without the ampersand, using just the quotes.
What is the best way for an amateur like me to de-code VBA statements? Is there a good online source (which happens to be free and accessible)? DOUG "Dave Peterson" wrote: just add another string =$G$32&" 09 - Compared to last month, RVUs "&TEXT($J$32,"""rose"" 0%;""fell"" 0%;"" were unchanged""")&"." DOUG wrote: Dave: I got it to work using the following instructions. =$G$32&" 09 - Compared to last month, RVUs "&TEXT($J$32,"""rose"" 0%;""fell"" 0%;"" were unchanged""") I had left off the "0%" after the word "fell"! I would still like to know how to place a period at the end of the sentence without invalidating the instructions, if you do not mind. DOUG "Dave Peterson" wrote: If A1 contained the percentage, you could use a formula like: ="In May, such and such a value " &TEXT(A1,"""rose ""0%;""fell ""0%;""stayed even""") The =text() function has 4 parts: positive;negative;zero;text I didn't touch the text portion. DOUG wrote: Is there a way to substitute the words "UP" or "ROSE" and "DOWN" or "FELL" for a calcualted value referenced in another cell in a concatenated statement combining text and the cell reference? (So, instead of saying "In May, such and such a value was -10%", I would be able to say it "fell 10%"). DOUG ECKERT -- Dave Peterson -- Dave Peterson |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Substitute Text for Plus or Minus
There are lots of tutorials on the web.
Debra Dalgleish has some notes how to implement macros he http://www.contextures.com/xlvba01.html David McRitchie has an intro to macros: http://www.mvps.org/dmcritchie/excel/getstarted.htm Ron de Bruin's intro to macros: http://www.rondebruin.nl/code.htm If you search these newsgroups for "tutorial macro excel", I'm sure you'll find tons of suggestions--heck, you could even search the web (bing.com or google.com) and get lots. DOUG wrote: Dave: Thanks. I had tried that without the ampersand, using just the quotes. What is the best way for an amateur like me to de-code VBA statements? Is there a good online source (which happens to be free and accessible)? DOUG "Dave Peterson" wrote: just add another string =$G$32&" 09 - Compared to last month, RVUs "&TEXT($J$32,"""rose"" 0%;""fell"" 0%;"" were unchanged""")&"." DOUG wrote: Dave: I got it to work using the following instructions. =$G$32&" 09 - Compared to last month, RVUs "&TEXT($J$32,"""rose"" 0%;""fell"" 0%;"" were unchanged""") I had left off the "0%" after the word "fell"! I would still like to know how to place a period at the end of the sentence without invalidating the instructions, if you do not mind. DOUG "Dave Peterson" wrote: If A1 contained the percentage, you could use a formula like: ="In May, such and such a value " &TEXT(A1,"""rose ""0%;""fell ""0%;""stayed even""") The =text() function has 4 parts: positive;negative;zero;text I didn't touch the text portion. DOUG wrote: Is there a way to substitute the words "UP" or "ROSE" and "DOWN" or "FELL" for a calcualted value referenced in another cell in a concatenated statement combining text and the cell reference? (So, instead of saying "In May, such and such a value was -10%", I would be able to say it "fell 10%"). DOUG ECKERT -- Dave Peterson -- Dave Peterson -- Dave Peterson |
|
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Substitute Text for Plus or Minus
'Wonderful, Dave. Thank you so much!
DOUG ECKERT "Dave Peterson" wrote: There are lots of tutorials on the web. Debra Dalgleish has some notes how to implement macros he http://www.contextures.com/xlvba01.html David McRitchie has an intro to macros: http://www.mvps.org/dmcritchie/excel/getstarted.htm Ron de Bruin's intro to macros: http://www.rondebruin.nl/code.htm If you search these newsgroups for "tutorial macro excel", I'm sure you'll find tons of suggestions--heck, you could even search the web (bing.com or google.com) and get lots. DOUG wrote: Dave: Thanks. I had tried that without the ampersand, using just the quotes. What is the best way for an amateur like me to de-code VBA statements? Is there a good online source (which happens to be free and accessible)? DOUG "Dave Peterson" wrote: just add another string =$G$32&" 09 - Compared to last month, RVUs "&TEXT($J$32,"""rose"" 0%;""fell"" 0%;"" were unchanged""")&"." DOUG wrote: Dave: I got it to work using the following instructions. =$G$32&" 09 - Compared to last month, RVUs "&TEXT($J$32,"""rose"" 0%;""fell"" 0%;"" were unchanged""") I had left off the "0%" after the word "fell"! I would still like to know how to place a period at the end of the sentence without invalidating the instructions, if you do not mind. DOUG "Dave Peterson" wrote: If A1 contained the percentage, you could use a formula like: ="In May, such and such a value " &TEXT(A1,"""rose ""0%;""fell ""0%;""stayed even""") The =text() function has 4 parts: positive;negative;zero;text I didn't touch the text portion. DOUG wrote: Is there a way to substitute the words "UP" or "ROSE" and "DOWN" or "FELL" for a calcualted value referenced in another cell in a concatenated statement combining text and the cell reference? (So, instead of saying "In May, such and such a value was -10%", I would be able to say it "fell 10%"). DOUG ECKERT -- Dave Peterson -- Dave Peterson -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| SUBSTITUTE multiple text strings | Excel Discussion (Misc queries) | |||
| how do I substitute text in Excel? | Excel Worksheet Functions | |||
| Substitute a numeric value for a text value in a calculation. | Excel Discussion (Misc queries) | |||
| How do I substitute text for numbers | Excel Discussion (Misc queries) | |||
| Substitute text with assigned values | Excel Discussion (Misc queries) | |||
Linear Mode
