Formulas in a certain worksheet seem not to work.

[huib_bruijstens007]

Normally I do not have problems with formulas in worksheets. But now I can not find out why a simple IF formula does not work properly. If I run this formula in a new worksheet, no problem, works fine. However, I do have a large worksheet and in there this formula does not work.

If I copy a part of this worksheet into a new file (copy paste) and add the formula, I does not give the wanted outcome. I made a testsheet to illustrate, see attached.

What is happening below the surface that kind of blocks this formula to work properly.

Kindly request your insights.

[#2]

Huib Bruijstens:   I am new here and see, that the attached file does not show the formula.

Here it is     =IF(A2>0;A2;B1)

[#3 · Accepted solution]

@huib_bruijstens007 

Cells A4, A5 etc. contain two spaces. A space is a text value, and Excel treats all text values as greater than all numbers, in particular as >0. So A4>0 is TRUE.

Workaround 1:

• Select column A.
• Press Ctrl+H to activate the Replace dialog.
• Enter two spaces in the 'Find what' box and leave the 'Replace with' box empty.
• Click 'Options >>'.
• Tick the check box 'Match entire cell contents'.
• Click 'Replace All'

Workaround 2:

• Leave column A as it is.
• Change the formula in B3 to

=IF(A3<>"  ",A3,B2)

• Fill down.

Both should result in the formulas returning the desired result.

[#4]

@Hans Vogelaar   He, GREAT!!! This really helps because it works! Thanks a lot for teaching. I learned a lot tonight. Thank you very much. Greetings from the Netherlands, Huib

[#5]

@huib_bruijstens007 

En groeten terug! :smiling_face_with_smiling_eyes: