If Formula with Nested "&Text"

[Smokeyham]

I have the following formula, which works fine:

=IF($T$424>$X$406,"You Met Your Goal!", TEXT(($T$424-$X$406),"$0,0"))

If they do not meet the test, then it portrays a figure, correctly formatted, which is what I want, except I want to have some text in front of it:

“You still need to cut $150”

I tried the following (and lots of variations), but with no luck. I keep on getting an error message in Excel 2003.

=IF($T$424>$X$406,"You Met Your Goal!",“You still need to cut”&TEXT(($T$424-$X$406),"$0,0"))

I'm sure I'm missing something perfectly simple.... but have gone brain dead.

Any help would be appreciated!

Gordon

 

[Peter_SSs]

Thanks Peter,

I'd be curious to find out what I was doing differently. I'll have to play with this some more.

Gordon

I suspect they were actually the wrong quotes though they may have looked OK in Excel. If you copy that final formula from your original post and paste it into the formula bar in Excel and press Enter, you should get an error message.

If you click OK to that error message you will see your formula in the formula bar and the double quotes all look OK, but Excel will have highlighted (it did for me anyway) "You (which you will note includes the first quotes that look wrong in your original post here). If you select and re-type that " and also select and re-type each " from there to the end of the formula, then press Enter, you should find that the formula works.

 

[Smokeyham]

Previously, T424>X406 to meet goal, now T242<X406 meets goal.

Sorry for the confusion. I'm also playing with the values since I'm working with negative numbers and trying different variations to see which gives me the results I want. Basically, they have to cut a budet by 10%.

 

[mikerickson]

TEXT(($T$424-$X$406),"""You need to add: ""$0,0"))
...
How does it know to put the text in front of the calculation, since the text comes after the calculation?

TEXT acts the same as cell formatting.
If a cell, containing 3000 was formatted with the custom formatting
"You need to add: "$0,0
what the user sees in the cell is the text
You need to add: $3,000
That text string is what the TEXT function returns. So
TEXT(3000, """You need to add: ""$0,0") = "You need to add: $3,000"

 

[Smokeyham]

TEXT acts the same as cell formatting.
If a cell, containing 3000 was formatted with the custom formatting
"You need to add: "$0,0
what the user sees in the cell is the text
You need to add: $3,000
That text string is what the TEXT function returns. So
TEXT(3000, """You need to add: ""$0,0") = "You need to add: $3,000"

So, using "Text" you could not end up with a result such as "$3,000 needs to be added"?

Gordon

 

[mikerickson]

I'd use
=TEXT(3000,"$0,0"" needs to be added""")

This is like custom formatting, the 0 is where the number is expressed.

 

[Smokeyham]

I'd use
=TEXT(3000,"$0,0"" needs to be added""")

This is like custom formatting, the 0 is where the number is expressed.

=TEXT(3000, """You need to add: ""$0,0") = "You need to add: $3,000"

Great.... thanks ..... Having both formats is very helpful:

=TEXT(3000,"$0,0"" needs to be added""") results in "$3,000 needs to be added

=TEXT(3000, """You need to add: ""$0,0") results in "You need to add: $3,000"