Check Range for Value (2)

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
 

Some videos you may like

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

Dan Waterloo

Well-known Member
Joined
Jan 4, 2007
Messages
874
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

Well-known Member
Joined
Jan 4, 2007
Messages
874
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

Board Regular
Joined
Oct 13, 2004
Messages
69
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

Board Regular
Joined
Oct 13, 2004
Messages
69

ADVERTISEMENT

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

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

Well-known Member
Joined
Jan 4, 2007
Messages
874

ADVERTISEMENT

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

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

tafigueroa

Board Regular
Joined
Oct 13, 2004
Messages
69
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"))
 

Watch MrExcel Video

Forum statistics

Threads
1,123,333
Messages
5,601,008
Members
414,421
Latest member
tonybear1994

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top