Help: IF formula: Cell value based on entered values of two other cells...

[XL*****]

Situation: Ages of two people are entered into cells A1 and B1. If either age is 60 or older, the value of cell C1 should be "AGE". If either value is 59 or younger, the value should be "NA". If no age is available for A1 or B1, "NA" is entered manually.

Problem: If A1<60 and B1 = "NA", C1 returns "AGE".

Formula: IF(A1="NA","NA",IF(A1>=60,"AGE",IF(B1>=60,"AGE",IF(A1<60,"NA",IF(B1<60,"NA")))))

I know that the formula I have does not recognize text (ie. "NA"), therefore it is ignoring the manually entered "NA". Any help making this work would be appreciated. I've tried to craft AND, OR and various IF formulas to no avail.

[Sam Capricci]

try ... =IF(OR(A1>59,B1>59),"AGE","NA")

[Sam Capricci]

sorry, I think I misread all your conditions so I came up with this instead... =IF(OR(A1="na",B1="na"),"NA",IF(OR(A1>=60,B1>=60),"AGE","NA"))

EDIT: works better incase of decimals.

[Phuocam]

try this...

=IF(AND(COUNT(A1:B1)=2,MAX(A1:B1)>=60),"Age","N/A")

[XL*****]

Sambo & Phuocam... thx for the suggestions but neither of these work. When i use the formulas, if A1>60 and B1=NA, C1 returns "NA".

C1 should return "AGE" if A1 or B1 is 60>=. C1 should return "NA" if both A1 and B1 are NA or... of both are 60<

Any other thoughts or suggestions?

[oeldere]

try the changed formula of sambo kid

=IF(AND(A1="na",B1="na"),"NA",IF(OR(A1>=60,B1>=60),"AGE","NA"))

[XL*****]

Still the same result.

Any other ideas?

I'm surprised that this is proving so difficult with all of the complicated formulas I have seen elsewhere.

[Tony Valko]

How about posting SEVERAL examples that cover every possible combination and show us what results you expect.

[oeldere]

Do you mean N/A (not available) instead of na?

Please post your excel file, without confidential information.

[XL*****]

How about posting SEVERAL examples that cover every possible combination and show us what results you expect.

Examples of what I want...
A1 B1 C1
60 NA AGE
59 60 AGE
60 59 AGE
NA NA NA
59 NA NA

The values for cells A1 & B1 are entered manually, including "NA," which represents "Not Applicable."

[XL*****]

Examples of what I want...
A1 B1 C1
60 NA AGE
59 60 AGE
60 59 AGE
NA NA NA
59 NA NA

The values for cells A1 & B1 are entered manually, including "NA," which represents "Not Applicable."

Also...
59 59 NA

[Sam Capricci]

based on what you've written my post #3 should work unless there is a hidden space in the cell.

[Sam Capricci]

I would say that oldere and Tony are asking you to post a sample spreadsheet with the data so we can see what we are dealing with.

[JohnTopley]

Try

=IF(OR(AND(ISNUMBER(A2),A2>59),AND(ISNUMBER(B2),B2>59)),"AGE","NA")

[Tony Valko]

Try this...

Data Range

	A	B	C
1	60	NA	AGE
2	59	60	AGE
3	60	59	AGE
4	NA	NA	NA
5	59	NA	NA
6	------	------	------

This formula entered in C1 and copied down:

=IF(COUNT(A1:B1)=0,"NA",IF(AND(COUNT(A1:B1),COUNTIF(A1:B1,">=60")),"AGE","NA"))

[Phuocam]

Examples of what I want...
A1 B1 C1
60 NA AGE

try this...
=IF(MAX(A1:B1)>=60,"AGE","NA")