Use of IF

[Jeffern]

=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? 

[#2]

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

[#3]

@JKPieterse 

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

[#4]

@Jeffern 

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.

[#5]

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.

[#6]

@Jeffern 

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";"")

[#7]

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.

[#8]

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?

[#9]

@Jeffern 

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?

[#10]

@HansVogelaar 

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

[#11]

@Jeffern 

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

[#12]

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

[#13]

@Jeffern 

My email address is

hans dot vogelaar at gmail dot com

[#14]

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

[#15]

@Jeffern 

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

[#16]

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

[#17]

@Jeffern 

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

Can you explain what you want to accomplish?

[#18]

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!

[#19]

@Jeffern 

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.

[#20]

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!