Selective Ranges...

[Romi Kishore]

I am trying to find out Minimum or Maximum of a column on each of the rows where the particular number on the row is excluded from the calculation. The actual theme of my endeavor is the following...

A Non-Null Column FROM Date TO Date Conditionally Format if either FROM or TO Date
falls In Any Other Range
------------------ -------------- ---------- ----------------------------------------------
Project A 05/28/2013 06/15/2013 Yes
Project B 05/28/2013 06/01/2013 Yes
Project C 06/26/2013 07/09/2013 No
Project D (blank) (blank) No
Project E 07/17/2013 08/02/2013 No
Project F 05/27/2013 05/27/2013 No
Project G 04/24/2013 05/29/2013 Yes




Can anybody please suggest some ideas? I came up with a formula to give me non-contiguous ranges that I can put in Indirect and can find the Minimum and Maximums for each row; but it apparently doesn't work in Conditional Formatting and it's too long. I may also add that I did try Min(IF...) thing but it doesn't work ... fails for that blank date. Changed the formula to Array formula but the comparison with each row doesn't seem to work either. Min(IF($B:$B<>$B2,$B:$B)) ... $B2 stays stagnant if I apply this as an array formula.

'Textual Ranges - JUST to obtain POC (Proof of Concept)...
=IF(OR(ROW()=2,ROW()=COUNTA($A:$A)),IF(ROW()=2,IF(COUNTA($A:$A)>1,"$B3:$B"&COUNTA($A:$A),"$B2:$B2"),IF(COUNTA($A:$A)>1,"$B2:$B"&COUNTA($A:$A)-1,"$B2")),IF(COUNTA($A:$A)>1,"$B2:$B"&ROW()-1&",$B$"&ROW()+1&":$B$"&COUNTA($A:$A),"$B2"))

'Actual formula for finding Minimum...
=MIN(IF(OR(ROW()=2,ROW()=COUNTA($A:$A)),INDIRECT(IF(ROW()=2,IF(COUNTA($A:$A)>1,"$B3:$B"&COUNTA($A:$A),"$B2:$B2"),IF(COUNTA($A:$A)>1,"$B2:$B"&COUNTA($A:$A)-1,"$B2"))),IF(COUNTA($A:$A)>1,(INDIRECT("$B2:$B"&ROW()-1),INDIRECT("$B$"&ROW()+1&":$B$"&COUNTA($A:$A))),INDIRECT("$B2"))))

'Actual formula for finding Maximum...
=MAX(IF(OR(ROW()=2,ROW()=COUNTA($A:$A)),INDIRECT(IF(ROW()=2,IF(COUNTA($A:$A)>1,"$C3:$C"&COUNTA($A:$A),"$C2:$C2"),IF(COUNTA($A:$A)>1,"$C2:$C"&COUNTA($A:$A)-1,"$C2"))),IF(COUNTA($A:$A)>1,(INDIRECT("$C2:$C"&ROW()-1),INDIRECT("$C$"&ROW()+1&":$C$"&COUNTA($A:$A))),INDIRECT("$C2"))))

A non-conventional out of the box formula is the need of the hour, as I can imagine. Please help.

 

[Stan_C]

Just to make sure I have this right. You want to highlight any cell that falls between any 2-dates within your range?

Excel Workbook
	A	B	C
1	5/15/2013	5/22/2013	YES
2	5/21/2013	5/28/2013	YES
3	5/31/2013	6/4/2013	NO
4	6/5/2013	6/12/2013	NO
Sheet2

 

[Stan_C]

See if this works for you....

Excel Workbook
	A	B	C	D	E
1	Project A	5/28/2013	6/15/2013	YES	TRUE
2	Project B	5/28/2013	6/1/2013	YES	TRUE
3	Project C	6/26/2013	7/9/2013	NO	FALSE
4	Project D			NO	FALSE
5	Project E	7/17/2013	8/2/2013	NO	FALSE
6	Project F	5/27/2013	5/27/2013	NO	FALSE
7	Project G	4/24/2013	5/29/2013	YES	TRUE
Sheet3

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B1	1. / Formula is =OR(IF($B1=$C1,FALSE,SUM(($B1>=$B$1:$B$7)*($B1<=$C$1:$C$7))>1),IF($B1=$C1,FALSE,SUM(($C1>=$B$1:$B$7)*($C1<=$C$1:$C$7))>1))	Abc

 

[Romi Kishore]

This is just amazing ... I have never used such kind of formulae before and will try to understand them ... can you possibly also please point me to some resource where I can learn about creating such formulae ... or is it just years of practice with them? THANKS A BUNCH!

Regards,
Romi

 

[Stan_C]

Glad it works for you!

I started using Excel when I signed up with this forum. Learned by reading post, asking questions, trying to solve issues for others and LOTS of research.

Stuff starts to click when you understand how excel calculates data and how you can combine functions to get the desired outcome. The evaluate formula is a great way to see this, it helps to make sense of what is going on.

All in all..... tons of research and playing around with things.

I have also watched days of video on YouTube by a member mgirvin called Excel Magic Tricks and others. excelisfun -- Excel How To Videos - YouTube