excelnovice05
Board Regular
- Joined
- Jan 4, 2005
- Messages
- 66
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
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