Forum Discussion
Formulas in a certain worksheet seem not to work.
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.
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 Replies
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.
HansVogelaar 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
En groeten terug! 😊
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)
