Nested if statement or an array formula

[Mel Smith]

I have to set up a tracking spreadsheet and I'm trying to work out a formula for the following:

In cell P1:

1. If Cell C1 and Cell L1 are blank, then P1 is blank
2. If Cell C1 is not blank and L1 is blank, then P1 displays "Error"
3. If Cell C1 is blank and L1 is not blank, P1 dispalys "Error"

Any help or suggestions would be most appreciated.

Thanks.

 

[jtakw]

Hi,

Thank you for your explanation. I believe I understand your requirements.

If the formula is to be placed in a Sheet OTHER than "Antigens", then use:

Excel 2010
	P
26	N 2 Six
Sheet1

Cell Formulas
Range	Formula
P26	=IF(Antigens!$H26="","",IF(Antigens!$H26))

If the formula is placed in the Sheet "Antigens", the formula won't need Sheet references, then use this:

Excel 2010
	H	I	J	K	L	M	N	O	P
26	10		20			M 2 Six	N 2 Six		N 2 Six
27	30					M 2 Seven	N 2 Seven		M 2 Seven
28			20			M 2 Eight	N 2 Eight
29	20		30			M 2 Nine	N 2 Nine		N 2 Nine
Antigens

Cell Formulas
Range	Formula
P26	=IF($H26="","",IF($H26<$J26,$N26,$M26))

Ignore what's in Columns M and N, that's just sample data to show the result in Column P.
Copy Column P formula up and/or down.

Let me know if this works for you, or if I may have misunderstood what you need.