Previous 1 2 26 Replies Latest reply: Feb 6, 2013 2:48 PM by nandi
Level 1

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
Solved by Jerrold Green1 on Feb 1, 2013 12:26 PM Solved

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

#### All replies

• Level 7

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

• Level 1

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.

• Level 7

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

• Level 1

one = away but a world of difference

many thanks Jerry, Im beginning to enjoy this programming opportunity

• Level 7

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

• Level 1

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

• Level 7

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

Jerry

• Level 1

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

• Level 7

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...

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

Jerry

• Level 1

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?

• Level 7

"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

• Level 1

Hey Barry,

Here is what the outcome would look like:

if 655 > 543

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.

• Level 7

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.

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

• Level 1

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.

Previous 1 2