SOLVED

Formulas in a certain worksheet seem not to work.

%3CLINGO-SUB%20id%3D%22lingo-sub-1850909%22%20slang%3D%22en-US%22%3EFormulas%20in%20a%20certain%20worksheet%20seem%20not%20to%20work.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1850909%22%20slang%3D%22en-US%22%3E%3CP%3ENormally%20I%20do%20not%20have%20problems%20with%20formulas%20in%20worksheets.%20But%20now%20I%20can%20not%20find%20out%20why%20a%20simple%20IF%20formula%20does%20not%20work%20properly.%20If%20I%20run%20this%20formula%20in%20a%20new%20worksheet%2C%20no%20problem%2C%20works%20fine.%20However%2C%20I%20do%20have%20a%20large%20worksheet%20and%20in%20there%20this%20formula%20does%20not%20work.%3C%2FP%3E%3CP%3EIf%20I%20copy%20a%20part%20of%20this%20worksheet%20into%20a%20new%20file%20(copy%20paste)%20and%20add%20the%20formula%2C%20I%20does%20not%20give%20the%20wanted%20outcome.%20I%20made%20a%20testsheet%20to%20illustrate%2C%20see%20attached.%3C%2FP%3E%3CP%3EWhat%20is%20happening%20below%20the%20surface%20that%20kind%20of%20blocks%20this%20formula%20to%20work%20properly.%3C%2FP%3E%3CP%3EKindly%20request%20your%20insights.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1850909%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1851004%22%20slang%3D%22en-US%22%3ERe%3A%20Formulas%20in%20a%20certain%20worksheet%20seem%20not%20to%20work.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1851004%22%20slang%3D%22en-US%22%3E%3CP%3EHuib%20Bruijstens%3A%20%26nbsp%3B%20I%20am%20new%20here%20and%20see%2C%20that%20the%20attached%20file%20does%20not%20show%20the%20formula.%3C%2FP%3E%3CP%3EHere%20it%20is%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%3DIF(A2%26gt%3B0%3BA2%3BB1)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1851040%22%20slang%3D%22en-US%22%3ERe%3A%20Formulas%20in%20a%20certain%20worksheet%20seem%20not%20to%20work.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1851040%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F856480%22%20target%3D%22_blank%22%3E%40huib_bruijstens007%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ECells%20A4%2C%20A5%20etc.%20contain%20two%20spaces.%20A%20space%20is%20a%20text%20value%2C%20and%20Excel%20treats%20all%20text%20values%20as%20greater%20than%20all%20numbers%2C%20in%20particular%20as%20%26gt%3B0.%20So%20A4%26gt%3B0%20is%20TRUE.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EWorkaround%201%3A%3C%2FP%3E%0A%3CUL%3E%0A%3CLI%3ESelect%20column%20A.%3C%2FLI%3E%0A%3CLI%3EPress%20Ctrl%2BH%20to%20activate%20the%20Replace%20dialog.%3C%2FLI%3E%0A%3CLI%3EEnter%20two%20spaces%20in%20the%20'Find%20what'%20box%20and%20leave%20the%20'Replace%20with'%20box%20empty.%3C%2FLI%3E%0A%3CLI%3EClick%20'Options%20%26gt%3B%26gt%3B'.%3C%2FLI%3E%0A%3CLI%3ETick%20the%20check%20box%20'Match%20entire%20cell%20contents'.%3C%2FLI%3E%0A%3CLI%3EClick%20'Replace%20All'%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3CP%3EWorkaround%202%3A%3C%2FP%3E%0A%3CUL%3E%0A%3CLI%3ELeave%20column%20A%20as%20it%20is.%3C%2FLI%3E%0A%3CLI%3EChange%20the%20formula%20in%20B3%20to%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DIF(A3%26lt%3B%26gt%3B%22%20%20%22%2CA3%2CB2)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CUL%3E%0A%3CLI%3EFill%20down.%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3CP%3EBoth%20should%20result%20in%20the%20formulas%20returning%20the%20desired%20result.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

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.

4 Replies
Highlighted

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)

Highlighted
Best Response confirmed by huib_bruijstens007 (New Contributor)
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.

 

Highlighted

@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

Highlighted