onopilot

Q: Use of number formula

Numbers 3.6.2

Header 1Header 2Header 3Header 4Header 5
7:00xzy

 

I need to solve a problem.

The header 1 is a duration. If in header 2 or 3 is present x the value in header 1 will be copied in header 4. If x is not present in 2 or 3, the value in header 1 will be copied in header 5.

Can somebody help me with this?

Thank you

MacBook Pro with Retina display, OS X El Capitan (10.11.6)

Posted on Sep 17, 2016 4:26 AM

Close

Q: Use of number formula

  • All replies
  • Helpful answers

  • by t quinn,Helpful

    t quinn t quinn Sep 20, 2016 2:43 AM in response to onopilot
    Level 5 (5,041 points)
    Mac OS X
    Sep 20, 2016 2:43 AM in response to onopilot

    Hi onopilot,

     

    Here is an approach. First note the format for duration that Numbers recognizes. You can get the display to show what you have but your data entry should be like above if you don't want to struggle against the program.

    Screen Shot 2016-09-17 at 7.40.05 AM.png

    D2= IF(OR(B2="x",C2="x"),A2,"")

    This will copy the value in A2 if there is  an "x" in either B2 or C2.

    Screen Shot 2016-09-17 at 7.44.46 AM.png

    E2= IF(LEN(D2)=0,A2,"")

    This checks to see if there are characters in D2 and if there are not, copies the value in A2.

     

    Both these formulas can be filled down.

     

    quinn

  • by onopilot,

    onopilot onopilot Sep 17, 2016 8:05 AM in response to t quinn
    Level 1 (4 points)
    iWork
    Sep 17, 2016 8:05 AM in response to t quinn

    I'm sorry: It does not work for me.

    I use numbers in italian but it's the same, i imagine.

    I open the formula selecting "logic and info" and I chose IF, after OR

    Then just have a look on the sequence of screenshots enclosed also compared to yours.

     

    Also: can you better identify the LEN function (as I have to translate it) to search and use for.

    Thank you again1.png2.png3.png

  • by t quinn,

    t quinn t quinn Sep 17, 2016 7:32 PM in response to onopilot
    Level 5 (5,041 points)
    Mac OS X
    Sep 17, 2016 7:32 PM in response to onopilot

    Hi onopilot,

     

    Sorry you are having a difficulty with the formulas.

     

    Are you building the formulas by pointing and clicking? Sometimes that smoothes out errors. Is the red flag a syntax error? I can't tell from the screenshot. Does your region use "," or ";" in formulas? That may be the glitch.

     

    Screen Shot 2016-09-17 at 8.24.02 PM.png

    LEN() is a text function that counts the characters in a string. It is very useful for testing if a cell as a value.

     

    I hope this helps.

     

    quinn

  • by onopilot,

    onopilot onopilot Sep 18, 2016 4:34 AM in response to t quinn
    Level 1 (4 points)
    iWork
    Sep 18, 2016 4:34 AM in response to t quinn

    Hello Quinn

    thank you for your help, but It does not work.

    I also reverted my system in english to minimize differences without any result. I have always used  "point and Click" but to completely copy your suggested formulas I had to change ";" in ",".

    So I had to find another solution using IF with another cell so I got the result that I was looking for (although it's not the best I wanted).

    It seems I'm not capable to use IF with OR or LEN. I'm perfectly able to use single functions.

    So, what is my problem?

    Thank you for your further help!

  • by t quinn,

    t quinn t quinn Sep 18, 2016 6:47 AM in response to onopilot
    Level 5 (5,041 points)
    Mac OS X
    Sep 18, 2016 6:47 AM in response to onopilot

    Hi onopilot,

     

    "," or ";" is a regional thing. If your function editor uses ";" then leave it alone. it is what is needed for your region.

     

    You are not providing details of your difficulties. You do not describe how things don't work. I assume a syntax error due to you replacing the ";" generated by the function editor with ",". I am guessing due to incomplete info.

     

    quinn

  • by onopilot,

    onopilot onopilot Sep 19, 2016 1:54 PM in response to t quinn
    Level 1 (4 points)
    iWork
    Sep 19, 2016 1:54 PM in response to t quinn

    Hello quinn,

    we got into same solution because I tried so many times and, at the end, found the solution. Then I saw your clarification about the differences in our systems.

    I did have the syntax error, always, and I continue to have it with IF LEN.

    But, I completed my work in another way as anticipated.

    thank you for your time and patience (especially when supporting non english mother tongue)

    onopilot

  • by t quinn,Helpful

    t quinn t quinn Sep 20, 2016 2:43 AM in response to onopilot
    Level 5 (5,041 points)
    Mac OS X
    Sep 20, 2016 2:43 AM in response to onopilot

    Okay onopilot,

     

    So your question is answered? You might want to post your solution so anybody with the same question can see it.

     

    I am still confused as to what your syntax error was if it was not the , and ;

     

    q

  • by onopilot,Solvedanswer

    onopilot onopilot Sep 20, 2016 2:41 AM in response to t quinn
    Level 1 (4 points)
    iWork
    Sep 20, 2016 2:41 AM in response to t quinn

    Hello quinn, unfortunately I am not able to describe the syntax error that I got (the red triangle)

    but I can sho you what I did to solve the problem. First of all:Schermata 2016-09-20 alle 11.34.41.png

    this is the application of your first formula suggestion: as shown the syntax is different from the your ( I have "," instead of ",")

    I solved my problem with:

    Schermata 2016-09-20 alle 11.37.56.pngSchermata 2016-09-20 alle 11.39.08.png

    So I choose another parameter, reported in a single column, to move the info that i need.

    So I thank you for your help because i went deeply in Numbers ( although the LEN function is still a mystery for me!)

    Onopilot