Check Range for Value (2)

[excelnovice05]

Hi Excel Gurus,

I'm looking for a formula that can do the following:

look @ a range of dates
see if the number below any of the ranges cells equals 2
if one does then return 'yes'
if any of the cells in the range are blank and one equals 2, then it returns 'yes'
if none of the cells equals '2' and at least one is blank, then (nothing)
if all of the cells have a value, but none equal 2, then 'no'


10/1/2007 11/1/2007 12/1/2007 1/1/2008
1 1 1 1

cell output = "no"

10/1/2007 11/1/2007 12/1/2007 1/1/2008
1 1 2

cell output = "yes"

10/1/2007 11/1/2007 12/1/2007 1/1/2008
1 1

cell output = (blank)

Thanks in advance for your insights and I look forward to seeing what you can come up with.

=IF(OR(HLOOKUP(C3,G2:J4,2,FALSE)=2,(HLOOKUP((DATE(YEAR(C3),MONTH(C3)+1,DAY(C3))),G2:J4,2,FALSE)=2),(HLOOKUP((DATE(YEAR(C3),MONTH(C3)+2,DAY(C3))),G2:J4,2,FALSE)=2),(HLOOKUP((DATE(YEAR(C3),MONTH(C3)+3,DAY(C3))),G2:J4,2,FALSE)=2)),"On Time","")

I've been trying to figure this one out for hours. Only arrived at the following, but couldn't result in blank and the condition of the range which allows for a response only once either a 2 exists or a value is included in the entire range.

Thanks,
The Novice

 

[Dan Waterloo]

How about

=IF(OR(A2=2,B2=2, C2=2,D2=2),"Yes",IF(OR(ISBLANK(A2), ISBLANK(B2), ISBLANK(C2), ISBLANK(D2)),"","No"))

There may be briefer way of writing this.

 

[Dan Waterloo]

I just realized that your formula includes a possible result of "On Time" whereas your description of the problem didn't include this, so my version may not be complete. I'm assuming the 1's, 2's , and blanks are in cells A2:D2.

 

[tafigueroa]

Assuming the range of 1s, 2s and blanks is in G3 to J3 you can use this....

=IF(MAX(G3:J3)=2,"On Time",IF((COLUMN(J3)-COLUMN(G3)+1)>SUM(G3:J3),"","Late"))

This is assuming any 2 in the sequence is equal to on time and all 1s is equal to late, but you get the idea if you need to use different output text.

Always subtract the furthest to the left column from the furthest from the right column and add 1 to see the number of columns in the series even if there are blanks.

The Count and Counta formulas will not count cells with no value or no formula in them which is why I didn't use either in this case.

 

[tafigueroa]

Sorry...bug in the previous posting use the below instead.

=IF((COLUMN(J3)-COLUMN(G3)+1)>COUNT(G3:J3),"",IF(MAX(G3:J3)=2,"On Time","Late"))

 

[excelnovice05]

Thanks for all of the great feedback. It's truly helpful.

Dan,

I tried your formula and modified it a little as follows:

=IF(OR(HLOOKUP(D3,$H$2:$AJ$17,2,FALSE)=2,(HLOOKUP((DATE(YEAR(D3),MONTH(D3)+1,DAY(D3))),$H$2:$AJ$17,2,FALSE)=2),(HLOOKUP((DATE(YEAR(D3),MONTH(D3)+2,DAY(D3))),$H$2:$AJ$17,2,FALSE)=2),(HLOOKUP((DATE(YEAR(D3),MONTH(D3)+3,DAY(D3))),$H$2:$AJ$17,2,FALSE)=2)),"On Time",IF(OR(ISBLANK(HLOOKUP(D3,$H$2:$AJ$17,2,FALSE)),ISBLANK((HLOOKUP((DATE(YEAR(D3),MONTH(D3)+1,DAY(D3))),$H$2:$AJ$17,2,FALSE))),ISBLANK((HLOOKUP((DATE(YEAR(D3),MONTH(D3)+2,DAY(D3))),$H$2:$AJ$17,2,FALSE))),ISBLANK((HLOOKUP((DATE(YEAR(D3),MONTH(D3)+3,DAY(D3))),$H$2:$AJ$17,2,FALSE)))),"","No"))

Only one catch...when using the isblank statement it notices there are formulas in the referenced cells and therefore returns 'no'...even though they are blank to the naked eye.

Any thoughts on how to overcome this issue?

Thanks again... I will try the next formula and see where I get.

- The Novice

 

[Dan Waterloo]

Hi excelnovice05,

Maybe this version will work:
=IF(OR(A2=2,B2=2, C2=2,D2=2),"Yes",IF(OR(A2="", B2="", C2="", D2=""),"","No"))

 

[excelnovice05]

Excellent. It works. One last question. Is there a formula that will return the row a cell lies within? For example, F5, will return only 5?

Below is the formula I have so far...will share the final one once I have it all worked out.

=IF(OR(HLOOKUP(D3,$H$2:$AJ$17,2,FALSE)=2,(HLOOKUP((DATE(YEAR(D3),MONTH(D3)+1,DAY(D3))),$H$2:$AJ$17,2,FALSE)=2),(HLOOKUP((DATE(YEAR(D3),MONTH(D3)+2,DAY(D3))),$H$2:$AJ$17,2,FALSE)=2),(HLOOKUP((DATE(YEAR(D3),MONTH(D3)+3,DAY(D3))),$H$2:$AJ$17,2,FALSE)=2)),"On Time",IF(OR(HLOOKUP(D3,$H$2:$AJ$17,2,FALSE)="",(HLOOKUP((DATE(YEAR(D3),MONTH(D3)+1,DAY(D3))),$H$2:$AJ$17,2,FALSE)=""), (HLOOKUP((DATE(YEAR(D3),MONTH(D3)+2,DAY(D3))),$H$2:$AJ$17,2,FALSE)=""),(HLOOKUP((DATE(YEAR(D3),MONTH(D3)+3,DAY(D3))),$H$2:$AJ$17,2,FALSE)="")),"","Over Due"))


Thanks again. This has been very helpful in getting me over the hurdle I was stuck on.

- The Novice

 

[excelnovice05]

Sorry, figured it out. Used "row()". Works like a charm.

-The Novice

 

[tafigueroa]

Seems like a lot of typing to get the same result as you would using the simpler formula of:

=IF((COLUMN(J3)-COLUMN(G3)+1)>COUNT(G3:J3),"",IF(MAX(G3:J3)=2,"On Time","Over Due"))