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

 

[mikerickson]

Incorperate the prefix into the formatting string
TEXT(E11,"""You need to add ""$0,0")

 

[Peter_SSs]

Gordon, basically your formula looks OK to me. From the look of the formula, you may be having problems with the double quotes, some of which appear differently.

I suggest you put this formula in the cell then when you get the error message, just type over all the offending double quotes and see how you go.

The other minor change you will probably want is to have a space after the word cut and before that double quote.

 

[c_m]

Notice that the second quotes are not actually the quotes
"You Met Your Goal!",“You still need to cut”
Your formula is correct, just make sure that you use the quote symbol "
does that solve your issue?

 

[Smokeyham]

Incorperate the prefix into the formatting string
TEXT(E11,"""You need to add ""$0,0")

That worked a charm!! Thanks so much. For others who may be reading this, here is what the formula looks like:

=IF($T$424<$X$406,"You Met Your Goal!", TEXT(($T$424-$X$406),"""You need to add: ""$0,0"))

One question.... How does it know to put the text in front of the calculation, since the text comes after the calculation?

Thanks again.

Gordon

 

[Smokeyham]

Notice that the second quotes are not actually the quotes
"You Met Your Goal!",“You still need to cut”
Your formula is correct, just make sure that you use the quote symbol "
does that solve your issue?

They were correct in Excel, I just copied over to Word to help in drafting the post, which is when the different quote marks came into play.

 

[Peter_SSs]

They were correct in Excel, I just copied over to Word to help in drafting the post, which is when the different quote marks came into play.

If they were correct in Excel, then I'm wondering what error message you were getting in the first place?

Although I have no problem with putting the prefix in the formatting string, the original formula works perfectly for me - provided I use the correct double quotes. I did actually also change the , to a. at the end of the formula to fit my number formats. Here it is:

Excel Workbook
	T	U	V	W	X	Y
406					250
407
421
422
423
424	200					You still need to cut-$50.0
TEXT

 

[Peter_SSs]

here is what the formula looks like:

=IF($T$424<$X$406,"You Met Your Goal!", TEXT(($T$424-$X$406),"""You need to add: ""$0,0"))

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

 

[Smokeyham]

If they were correct in Excel, then I'm wondering what error message you were getting in the first place?

Although I have no problem with putting the prefix in the formatting string, the original formula works perfectly for me - provided I use the correct double quotes. I did actually also change the , to a. at the end of the formula to fit my number formats.

Thanks Peter,

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

Gordon

 

[c_m]

Good catch Peter

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