Looks like no one’s replied in a while. To start the conversation again, simply ask a new question.

How do you use a logic function to see if text is there?

I have a column of Dates, and a column of Categories. I want to Numbers to be able to tell me whether a certain Category, in this case "Phone", appears in the Category column during the month of February.

While playing with the IF and AND functions, aside from Errors, the only results I've come up with are "FALSE", when it clearly is in the category. I have checked everything over and can't find any error on my own part, aside from not understanding clearly how these functions work. Can someone clarify what I need to do to get the desired results?

Thank you so much!

MacBook Pro, Mac OS X (10.6.6), 2.4GHz Intel Core 2 Duo, 4GB RAM DDR3

Posted on Feb 6, 2011 2:53 AM

Reply
7 replies

Feb 6, 2011 3:22 AM in response to KOENIG Yvan

KOENIG Yvan, thank you so much for working it out. Unfortunately, it is still returning the "missing" status, though the Phone category is clearly there:

User uploaded file

Also, is there a way to specify its "existence" to a date range? What I am trying to do is have a separate table show whether or not I have paid the phone bill each month. So without a date range, it will display that I've paid it on every month. (Assuming I can get it to display the proper status in the first place.)

Thank you again for your time, though. I really appreciate it!

Feb 6, 2011 11:05 AM in response to Vangard

Vangard wrote:
Also, is there a way to specify its "existence" to a date range? What I am trying to do is have a separate table show whether or not I have paid the phone bill each month. So without a date range, it will display that I've paid it on every month. (Assuming I can get it to display the proper status in the first place.)


What you're looking for is a "breakout table"

These require an index column on the main table that marks the target rows with an identifier (a serial number) that's used by the LOOKUP function to retrieve other information from the marked rows.

For the example, I've placed Date, category and amount in columns A, B and C of the main table, and used column E as the index column.

On the Breakout table, the Category is in A1, dates are listed under it, and amounts in column B.
User uploaded file

Formulas:
Table 1
E2 and filled down the column: =IF(B=$E$1,MAX($E$1:E1)+1,"")

Table 2
A2 and filled down the column: =IF(ROW()-1>MAX(Table 1 :: $E),"",LOOKUP(ROW()-1,Table 1 :: $E,Table 1 :: $A))
B2 and filled down the column: =IF(ROW()-1>MAX(Table 1 :: $E),"",LOOKUP(ROW()-1,Table 1 :: $E,Table 1 :: $C))

The IF part is used to blank cells where LOOKUP would be looking for a number greater than the maximum value in the index column. Because LOOKUP cannot be set to look for exact matches only, it would repeat the last found entry if this part of the formula were omitted.

Note that the values (text) in the category column, and cell E1 of Table 1 must match exactly for the row to be included in the breakout table. (This may also be the reason for the 'missing' message you are getting from Yvan's formula.) The easiest way to accomplish this is to enter the category labels using a pop-up menu, and to use the same menu in E1.

Extension: For a single breakout table with a choice of extracting any one of the categories, place a copy of the pop-up menu in cell A1 of Table 2, and replace the value in E1 of table 1 with the formula =Table 2::A1

Regards,
Barry



Descriptions of ROW, MAX and LOOKUP may be found in the iWork Formulas and Functions User Guide, which may be downloaded through the Help menu in Numbers.

Feb 7, 2011 8:31 PM in response to Vangard

As I understand it, you need to do a COUNTIFS as in "count if B=phone and A=February" and if the count greater than zero, there is a february-phone match. The problem is that it you can't use "February" as a condition for the count. If you add another column that converts the date into a number, you can use that in a COUNTIF.

E = MONTH(A)

then
=IF(COUNTIFS(B,"phone",E,2)=0,"missing","available")

Another option that does not require another column but requires that you are specifying February 2011, not just any February is
=if(COUNTIFS(B,"phone",A,">="&date(2011,2,1),A,"<-"&DATE(2011,3,1))=0,"missing", "available")

You will have to remove the "-" from "<-" for this to work. The forum uses the left angle character (or whatever it is called) for special purposes and thereby mangles the formula so I had to add this extra character after it so you could see the entire formula.

Another option for the second formula is
=IF(COUNTIFS(B,"phone",A,">="&DATE(2011,2,1),A,"<="&EOMONTH(DATE(2011,2,1),0))=0 ,"missing","available")

You can modify the last formula so the date Feb 1, 2011 is calculated in another cell and then referenced by the formula. That would make it easier to change it to other dates

Message was edited by: Badunit

How do you use a logic function to see if text is there?

Welcome to Apple Support Community
A forum where Apple customers help each other with their products. Get started with your Apple ID.