If statement inside another statement

I have a value of 1500.00 in A1 that displays, as currency as \$1,500.00
and a value of 2500.00 in A2 that displays, as currency as \$2,500.00
and a value of 4500.00 in A3 that displays, as currency as \$4,500.00

In a general cell, for example, A10 I have the phrase...

="This would range between "&DOLLARA(1&)" and "&DOLLAR(A2)&"."

WHICH DISPLAYS....

This would range between \$1,500.00 and \$2,500.00.

But I want the statement to read, if there is a value in A3.

This would range between \$1,500.00 and \$2,500.00, but \$4,500.00 would be more realistic.

If there is no value in A3, it should read...

This would range between \$1,500.00 and \$2,500.00 and that should be all-inclusive.

Tried...

="This would range between "&DOLLARA(1&)" and "&DOLLAR(A2)&" "=IF(A3<>0, but "&DOLLAR(A3)&" would be more realistic., "and that should be all inclusive.)"

Not working and lost

.
LVL 1
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Commented:
Use formulas...

="This would range between " & IF(A3="",DOLLAR(A1) & " and " & DOLLAR(A2) & " and that should be all-inclusive.",DOLLAR(A1) & " and " & DOLLAR(A2) & ", but " & DOLLAR(A3) & " would be more realistic.")

Saurabh...
Excel & VBA ExpertCommented:
Same construction.....

="This would range between "&DOLLAR(A1)&" and "&DOLLAR(A2)&IF(A3<>"",", but "&DOLLAR(A3)&" would be more realistic."," and that should be all inclusive.")
Executive Managing MemberAuthor Commented:
I give up!  This is what I was trying to accomplish with my example and I was hoping to learn from your answer.  Can you show me what I am doing wrong with this formula, without re-constructing the phrase.  It seems to me this should work but I am getting nothing but errors with no indication of what the problem is. Maybe I can learn from my mistakes!

="We expect this will be difficult. "&IF(BasicData!O38=0, We will need to set aside "&DOLLAR(BasicData!O37)&" ".", we should set aside "&DOLLAR(BasicData!S37)&".""

I am using Excel 2003.  Do later versions of Excel offer any more help regarding syntax?
Excel & VBA ExpertCommented:
Pay attention to the text you have used inside the formula. You need to enclose the text entry with double quotes. Your formula will work if you put it like this....
Of course, you will need to correct the punctuation used in the formula as I haven't corrected them...
You also used extra double quotes as well.

="We expect this will be difficult. "&IF(BasicData!O38=0,"We will need to set aside "&DOLLAR(BasicData!O37)&". we should set aside "&DOLLAR(BasicData!S37)&".")
Executive Managing MemberAuthor Commented:
Ok, now all I get is We expect to be difficult. FALSE
Shouldn't there be a comma somewhere to pickup one statement and amount or the other?
Excel & VBA ExpertCommented:
Okay I see.
It should be like this.....

="We expect this will be difficult. "&IF(BasicData!O38=0,"We will need to set aside "&DOLLAR(BasicData!O37)&".", "We should set aside "&DOLLAR(BasicData!S37)&".")

Does this work now?

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Executive Managing MemberAuthor Commented:
Yes, it works.  Thanks a million, or at least 500 points!
Excel & VBA ExpertCommented:
You're welcome. Glad I could help. :)
And my apologies also because I should have paid attention to the complete syntax in the very first attempt. But happy that at last it worked for you.
Executive Managing MemberAuthor Commented:
One last thing.  How do I add the phrase, This would be my recommendation." at the end but still within the cell?

="We expect this will be difficult. "&IF(BasicData!O38=0,"We will need to set aside "&DOLLAR(BasicData!O37)&".", "We should set aside "&DOLLAR(BasicData!S37)&".")
Excel & VBA ExpertCommented:
Like this....

="We expect this will be difficult. "&IF(BasicData!O38=0,"We will need to set aside "&DOLLAR(BasicData!O37)&".", "We should set aside "&DOLLAR(BasicData!S37)&". This would be my recommendation.")
Executive Managing MemberAuthor Commented:
That only appears based on the outcome of the if statement.  I want to make the last statement, and a lot more text, regardless of the outcome of the IF statement.
Excel & VBA ExpertCommented:
Try this. I hope I understood your question correctly.

="We expect this will be difficult. "&IF(BasicData!O38=0,"We will need to set aside "&DOLLAR(BasicData!O37)&".", "We should set aside "&DOLLAR(BasicData!S37)&".")&" This would be my recommendation."

The bold text will appear in the formula cell at the end of the string and it is outside the If statement as well.
So you can change this text as per your requirement.
Executive Managing MemberAuthor Commented:
Once again, a million thanks.
Bill
Excel & VBA ExpertCommented:
You're welcome Bill!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.