# Use of IF

Copper Contributor

# Use of IF

=IF(B9=0;0;IF(B9='B3'='B2';IF(B9='B4'='B3)))

I get error messages

If a field='B3', I want to replace it with 'B2', if a field ='B4', I want to replace it with'B3*The fields are either blank(emty) =B3 or = B4.

Can you help me?

28 Replies

# Re: Use of IF

text in a formula needs to go between double quotes:
=IF(A1="The text","Some text if true","Some text if false")

# Re: Use of IF

Thank you for informing me that double quots are necessary.

I have other problems as well. Can you see what is wrong with the following?

=IF(b9=blank;blank;IF(b9=''B3'';''B2''))

Regards Jan Erik

# Re: Use of IF

You have to use the double quote character " (Shift+' on my keyboard), not two single quote characters ' next to each other.

And blank is not a valid keyword in Excel. Use the empty string "" (two double quote characters) instead:

``=IF(B9="";"";IF(B9="B3";"B2"))``

What do you want to return if B9 is not empty and not equal to "B3"? In that case, the formula will currently return FALSE because you don't specify the return value.

# Re: Use of IF

Thank you for your help. I do not know the syntax in excel well. At the end I would enter a double quote. Actually, there is an addition to this problem: If b9=“” B4»»; “B3””,””. This problem is a playlist in tennis which i recurring twice a year.

# Re: Use of IF

This time. you're using "curly quotes" ”” instead of straight quotes "".

And you definitely shouldn't use »» in Excel.

Also, you forgot the parentheses and a semicolon.

``=IF(B9="";"";IF(B9="B3";"B2";""))``

Alternatively, slightly shorter:

``=IF(B9="B3";"B2";"")``

# Re: Use of IF

Thank you again for the information. I think I get the gist of it now. I will even try to expand the problem to include B4 changed to B3, then there will be three IF's. I hope I can do it with four parenthesis at he end.

# Re: Use of IF

Sorry to bother you again. When enter exactly what you suggest, I get an error message: The formula contains unknown text. I do not understand it. Any suggestions?

# Re: Use of IF

Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?

# Re: Use of IF

Hans, the easiest for me would be to attach an excerpt to an email.

I don't know how to make it available to you via Drop Box or similar. I suppose this is not according to rules to use email

# Re: Use of IF

You can attach a file to a private message. Click on my user picture.

# Re: Use of IF

Hans,
I'm not sure how to do that, sorry!

# Re: Use of IF

hans dot vogelaar at gmail dot com

# Re: Use of IF

I'll send you an email with an attachment shortly.

# Re: Use of IF

Thanks, I have received the workbook. I don't see any formulas. In which cell(s) do you want a formula?

# Re: Use of IF

It starts in B9, but a system has to work for all cells - a couple of hundred.

# Re: Use of IF

A formula in B9 cannot refer to B9. That would cause a circular reference.

Can you explain what you want to accomplish?

# Re: Use of IF

For the time being, we use courts 2 and 3, but it used to be courts 3 and four. The change I would like is one in court 3 plays in court 2, one who plays in court 4, goes to court 3. If one cell is empty, then it continues empty. Of course, I know I could change the cells manually, bur then the problem reoccurs next season. I also saw this as an intellectual challenge, if I was able to change it quickly by a formula. I hope this makes sense!

# Re: Use of IF

You could use the Replace dialog (Ctrl+H).

Select the range.

First, enter B3 in the 'Find what' box and B2 in the 'Replace with' box, then click 'Replace All'.

Next, enter B4 in the 'Find what' box and B3 in the 'Replace with' box, then click 'Replace All'.

Done!

If you prefer formulas, you could return the desired values in another range, not in the same range.

See the attached version.

It would also be possible to run a macro to replace the values, but that might be overkill.

# Re: Use of IF

Hans,
Thank you very much for your valuable help. I believe that I prefer formulas that I can later use in solving similar problems, although there seems to be more than one way to skin a cat!