# Selective Ranges...

#### Romi Kishore

##### New Member
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.

### Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
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
ABC
15/15/20135/22/2013YES
25/21/20135/28/2013YES
35/31/20136/4/2013NO
46/5/20136/12/2013NO
Sheet2

See if this works for you....

Excel Workbook
ABCDE
1Project A5/28/20136/15/2013YESTRUE
2Project B5/28/20136/1/2013YESTRUE
3Project C6/26/20137/9/2013NOFALSE
4Project DNOFALSE
5Project E7/17/20138/2/2013NOFALSE
6Project F5/27/20135/27/2013NOFALSE
7Project G4/24/20135/29/2013YESTRUE
Sheet3
Cells with Conditional Formatting
CellConditionCell FormatStop If True
B11. / 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

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

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

Replies
1
Views
344
Replies
3
Views
92
Replies
9
Views
707
Replies
10
Views
436
Replies
8
Views
288

1,203,462
Messages
6,055,563
Members
444,799
Latest member
CraigCrowhurst

### 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.

### Which adblocker are you using?

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

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