Help with IF formula using a date created by a formula

[danhendo888]

Book1
	S
1	PO 2 Pay Date
2	00-Jan-00
Burnard

Cell Formulas
Range	Formula
S2	=LARGE(IF(Sheet2!L:L=P2,Sheet2!EE:EE),1)

So this formula is producing 00-Jan-00.
I wish to create a formula on top of it so that whenever it produces 00-Jan-00, I can just replace it with ""
Thus, creating a blank cell. Not sure how to do it.

Any ideas?

 

[frabulator]

Why not add an else statement?

If statements are structured like this, IF(SomeThing=SomethingElse, THEN TRUE, ELSE FALSE). The way you have it set up there is only a true statement, which if I am reading it correctly, your code is producing a FALSE statement as 00 Jan 00.

Try this and see if it works:

=LARGE([COLOR=Blue]IF([COLOR=Red]Sheet2!L:L=P2,Sheet2!EE:EE[/COLOR][/COLOR][B][COLOR=#ff0000],""[/COLOR][/B][COLOR=Blue]),1[/COLOR])

The other option is an IF statement inside of an IF statement.

 

[danhendo888]

Why not add an else statement?

If statements are structured like this, IF(SomeThing=SomethingElse, THEN TRUE, ELSE FALSE). The way you have it set up there is only a true statement, which if I am reading it correctly, your code is producing a FALSE statement as 00 Jan 00.

Try this and see if it works:

=LARGE([COLOR=blue]IF([COLOR=red]Sheet2!L:L=P2,Sheet2!EE:EE[/COLOR][/COLOR][B][COLOR=#ff0000],""[/COLOR][/B][COLOR=blue]),1[/COLOR])

The other option is an IF statement inside of an IF statement.

Hmm. I am still getting 00-Jan-00 which is weird because the column is only meant to have dates.
But if the cell is blank, then the formula just spits out 00-Jan-00.

 

[CANCER MAN]

Hello,

How about finding "Large" first and applying "IF" function. I am sorry if i am wrong. Your IF function seems incomplete. Could you please provide some sample data.

 

[danhendo888]

Hello,

How about finding "Large" first and applying "IF" function. I am sorry if i am wrong. Your IF function seems incomplete. Could you please provide some sample data.

Yes, you are right.
I tried it and it worked. But I also changed to AGGREGATE function.
My original formula was incorrect.
FWIW this is what I used:

=IF(AGGREGATE(14,6,$M$1:$M$20/(L1:L20=P2),1)=0,"",AGGREGATE(14,6,$M$1:$M$20/(L1:L20=P2),1))

L2: M1001
M2 1-July-19
P2: M1001