10 Replies Latest reply: Oct 23, 2013 12:15 PM by Jerrold Green1
BizzBro Level 1 (0 points)

Hi everyone! I hope it isnt a bug and we may could find a solution for my problem. I used the following formula and it ever works in Numbers but after upadating to the latest version of Numbers it doesn't. WENN(ISTLEER(A1);"";"1")  (germany - IF Cell is Empty, Return 1). I tried some other formulas but without succes.

 

Hope somebody can help ...

 

best regards


Numbers, OS X Mavericks (10.9), Numbers 3.0
  • Jerrold Green1 Level 7 (29,960 points)

    BB,

     

    I haven't completed the 10.9 install yet, so this is based on an educated guess.

     

    First, your Numbers expression doesn't seem to match your description of the desired resutt, True and False cases are reversed, I assume that is a simple typographic or translation error in your description.

     

    The following expression should operate the same way as WENN(ISTLEER(A1);"";"1"):

     

    =IF(LEN(A1)=0, 1, "")

     

    Jerry

  • BizzBro Level 1 (0 points)

    Thanx for your quick help. I tried =IF(ISBLANK(A1),"","1") and =IF(LEN(A1)=0, 1, "") but it still does not work.

    As i said before the formula WENN(ISTLEER(A1);"";"1")  (  =IF(ISBLANK(A1),"","1")  )  works fine until the update to Numbers 3.0 so i am perplexed.

     

  • Jerrold Green1 Level 7 (29,960 points)

    Sorry, I didn't notice at first that you need to use semi-colons for argument separators.

     

    You should write:

     

    =IF(LEN(A1)=0; 1; "")

     

    This is because your language uses commas for decimals.

     

    Jerry

  • BizzBro Level 1 (0 points)

    oh sorry,

     

    of course i tried =IF(LEN(A1)=0; 1; "") with semicolons, also with commas and also translated into the german formulas for Numbers... as well as i tried it with WENN(ISTLEER(A1);"";"1") 

     

    it still shows the red triangle with an exclamation mark... this is annoying because it is such a simple function.

     

    I opened an Excel Sheet and there the formula is working. confusing...

  • Jerrold Green1 Level 7 (29,960 points)

    What is the error message, in English if you don't mind translating?

     

    Jerry

  • BizzBro Level 1 (0 points)

    This formula can not refer to this cell or depend on some other formula that refers to that cell -  hope its correct.

     

    i know what it means but why shouldn't it work in the current version of numbers. And if it's not a bug whats the alternative solution?

  • Wayne Contello Level 6 (17,494 points)

    Bizz,

     

    Numbers has never allowed circular references.

  • Barry Level 7 (29,914 points)

    HI Bizz,

     

    Expanding what Wayne said:

     

    The error message indicates your formula is in cell A1, or that A1 contains a formula whose result depends on the value in the cell containing the formula.

     

    Regards,

    Barry

  • BizzBro Level 1 (0 points)

    Thanks,

     

    It seems to me that it is currently not possible to get a standard value for a cell. In the last three months it worked fine without any problems... Thank you, i'll try another way

  • Jerrold Green1 Level 7 (29,960 points)

    If you make the cell with the expression a Header, either Row Header or Column Header, you will avoid that error.

     

    Jerry