1 2 Previous Next 26 Replies Latest reply: Feb 6, 2013 2:48 PM by nandi
nandi Level 1 Level 1 (15 points)

I need a function to say

 

IF((B3>B2::THEN Write in B4"Some text")ELSE Write"Another text"))

 

eg

if 5 (B3) is greater than 3 (B2), write this text in B4 "Some text"

else write "Another text"

 

 

Part 2

A second action is required once the above is completed.

If A(then copy cell data from C1 into D10)(ELSE(copy cell data of D1 into D10))

 

eg.

IF((A is true copy the the "C1 Contents" into D10)(Else copy "D1 Contents" into D10))


MacBook Pro, Mac OS X (10.7.4), bought in 2012
  • 1. Re: If Then statement to write text in cell
    Jerrold Green1 Level 7 Level 7 (28,995 points)

    nandi wrote:

     

    I need a function to say

     

    IF((B3>B2::THEN Write in B4"Some text")ELSE Write"Another text"))

     

    eg

    if 5 (B3) is greater than 3 (B2), write this text in B4 "Some text"

    else write "Another text"

     

     

    Part 2

    A second action is required once the above is completed.

    If A(then copy cell data from C1 into D10)(ELSE(copy cell data of D1 into D10))

     

    eg.

    IF((A is true copy the the "C1 Contents" into D10)(Else copy "D1 Contents" into D10))

    The syntax for IF is:

     

    IF(if-expression, if-true, if-false)

     

    Just replace ::Then, and ELSE Write with commas and you have it.

     

    Remember, if you want to control what is in B4, you write the expression in B4. It's a Pull concept, not a Push concept. Fetch rather than Put, if you prefer. So your action If A(then copy cell data from C1 into D10)(ELSE(copy cell data of D1 into D10)) must be written in cell D10.

     

    Let me know if I wasn't clear.

     

    If you haven't yet discovered the Numbers User Guide, it's available from the Help menu and is a worthwhile read.

     

    Thanks,

     

    Jerry

  • 2. Re: If Then statement to write text in cell
    nandi Level 1 Level 1 (15 points)

    Thanks Jerry, so if I understand you, I say

     

    In Cell F5

    IF(B5>B6, "Text A","Text B")

     

    In Cell G9

    IF(B5>B6, B9,B10

    or possibly

    IF(B5>B6, =sum(B9),=sum(B10))

     

    both of these just sit as text in the cell and do not get recognized as programing.

    the first one was directly copied from the help center IF functions page.

  • 3. Re: If Then statement to write text in cell
    Jerrold Green1 Level 7 Level 7 (28,995 points)

    nandi wrote:

     

    Thanks Jerry, so if I understand you, I say

     

    In Cell F5

    IF(B5>B6, "Text A","Text B")

     

    In Cell G9

    IF(B5>B6, B9,B10

    or possibly

    IF(B5>B6, =sum(B9),=sum(B10))

     

    both of these just sit as text in the cell and do not get recognized as programing.

    the first one was directly copied from the help center IF functions page.

    Very close...

     

    In Cell F5

    =IF(B5>B6, "Text A","Text B")

     

    In Cell G9

    =IF(B5>B6, B9,B10)

    or

    =IF(B5>B6, sum(B9),sum(B10))  [This would work, but SUM(B9) is the same as B9.]

     

    ...will work.

     

    Jerry

  • 4. Re: If Then statement to write text in cell
    nandi Level 1 Level 1 (15 points)

    one = away but a world of difference

     

    many thanks Jerry, Im beginning to enjoy this programming opportunity

  • 5. Re: If Then statement to write text in cell
    Jerrold Green1 Level 7 Level 7 (28,995 points)

    I think you will enjoy programming in Numbers. I suggest that you read the first few chapters or so of the Numbers User Guide. Spend an evening with the guide with your favorite beverage at your side. It's a free download from the Numbers Help menu.

     

    Jerry

  • 6. Re: If Then statement to write text in cell
    nandi Level 1 Level 1 (15 points)

    ok i downloaded the guide - will review tonight

     

    my next adventure is to turn the text red or green and I've searched all over the software and web so I'll look at the pdf guide

     

    my best guess is =IF(B5>B6, "Text A" textcolor=green,"Text B" textcolor=red)

    but numbers - she no like that

     


  • 7. Re: If Then statement to write text in cell
    Jerrold Green1 Level 7 Level 7 (28,995 points)

    Cells inspector, Conditional Format. Well covered in the Guide.

     

    Jerry

  • 8. Re: If Then statement to write text in cell
    nandi Level 1 Level 1 (15 points)

    this programming is a challenge.

     

    I read through the guide and I watched a video on conditional format - it seems for a different use (ie the color of the text changes - that text is a result of math formula like sum).

     

    what Im looking to do is different than what the conditional format offers— at least to my novice programming mind

     

    say ive selected B5

    my best guess is adding a color text command to the earlier formula

    =IF(B2>B3, "Text A" textcolor=green,"Text B" textcolor=red)

     

    I couldnt find a syntax format for this anywhere

  • 9. Re: If Then statement to write text in cell
    Jerrold Green1 Level 7 Level 7 (28,995 points)

    Nandi,

     

    The "Condition" is a particular string of text. Here's an example where I set the default text color to Red and conditionally formatted it to Green...

     

    Screen Shot 2013-02-02 at 4.16.55 pm.png

    Text A is whatever you decide to make it. In your example, it's whatever you put in the formula.

     

    Jerry

  • 10. Re: If Then statement to write text in cell
    nandi Level 1 Level 1 (15 points)

    OK Jerry, I see how that works on static/text cells - by using the earlier formula I created 2 optional cell texts, and then I said in this second cell, if one text appears here turn it this color; the other text turn it this other color. Thanks for that.

     

    The question I was trying to understand is related to having to change a color of a number in one cell based on the result of two other cells. For example if B1<B2 then B3 should read the number in B1 (this number changes daily) and turn a color.

     

    So where Im stumped is how to take an everchanging number and turn it one color or the other?

  • 11. Re: If Then statement to write text in cell
    Barry Level 7 Level 7 (29,180 points)

    "For example if B1<B2 then B3 should read the number in B1 (this number changes daily) and turn a color."

     

    Conditional formatting rules compare the contents of the cell to be formatted (B3 in this case) with either a fixed value, or with the value in another cell.

     

    In the case you describe, what is to determine the colour used for the number in B3?

     

    Regards,

    Barry

  • 12. Re: If Then statement to write text in cell
    nandi Level 1 Level 1 (15 points)

    Hey Barry,

        Here is what the outcome would look like:

    if 655 > 543

    then B11 reads

    655

    and 6 is colored green

     

     

    From Jerry's help, the code presently stands like this -

     

    // in cell B11 I have this formula:

    =IF(B5>B6, B9,B10)

     

    // what I want to add is something like this:

    =IF(B5>B6, "B9" color=green,"B10" color=red)

     

    where b5 -b10 are numbers that are changeable on any given day or hour,

    and

    if the changeable number in cell B9 meets the criterior and is entered in B11 it is also turned green color

    or if

    if the changeable number in cell B10 meets the criterior and is entered in B11 it is turned red color as well.

  • 13. Re: If Then statement to write text in cell
    Barry Level 7 Level 7 (29,180 points)

    Hi nandi,

     

    The formula compares the values in cells B5 and B6. 

    There is no criterion for the number in B9 to meet, other than being in cell B9.

    There is no criterion for the number in B10 to meet, other than being in cell B10.

     

    IF the value in B5 is greater than the value in B6, THEN whatever value is in B9 is copied to B11.

    IF the value in B5 is NOT greater than the value in B6, THEN whatever value is in B10 is copied to B11.

    The table below shows the effect of that formula, revised to fit the column it is in, for the three possible cases in rows 5 and 6.

     

    The text colour is NOT set in the formula.

    Picture 8.png

     

    The text colour setting is format not content, and cannot be placed in the formula. It is controlled by a Conditional Format rule, set in the Cell Inspector. The first two rules in the box below the table are for cell B11.

    If the value in B11 is equal to that in B9, then the text colur is set to the green shown in the Text: Color Well in the first rule.

    If the first rule's condition is NOT met, then the second rule is checked.

    If the value in B11 is equal to that in B10, then the text colur is set to the red shown in the Text: Color Well in the second rule.

     

    As those are the only two values possible for B11, no further rules are necessary.

    The third (unset) rule is included only to show the icon at the right end of the box where the condition value is entered,

    To enter a fixed value, type it into the box.

    To reference a cell, click the icon, then click the cell whose vlue is to be compared to the value in B11.

     

    The conditional rules for cells C11 and D11 are the same as for B11, but compare the value in their cells with C9 C10 and D9 D10 respectively.

     

    Regards,

    Barry

  • 14. Re: If Then statement to write text in cell
    nandi Level 1 Level 1 (15 points)

    Thanks Barry,

    Im starting to understand how these 'equal to' rules work.

    Below u c I made a test and two things did not work

    1. the color did not change

    2. I was not able to add the second rule cell number (B10) - the white area when clicked, nothing happens.

    my test code.png

1 2 Previous Next