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

Say that your result of -10% is in Cell B4, then the following formula would
yield a result of "fell 10%"

=IF(B40,"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

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

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

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(B40,"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

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

"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

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

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