Nov 03 2020 12:38 PM
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.
Nov 03 2020 12:56 PM
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)
Nov 03 2020 12:59 PM
SolutionCells 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:
Workaround 2:
=IF(A3<>" ",A3,B2)
Both should result in the formulas returning the desired result.
Nov 03 2020 01:07 PM
@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
Nov 03 2020 01:10 PM
En groeten terug! :smiling_face_with_smiling_eyes:
Nov 03 2020 12:59 PM
SolutionCells 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:
Workaround 2:
=IF(A3<>" ",A3,B2)
Both should result in the formulas returning the desired result.