Swap Array for Non-Contigous range in MIN(IF formula

[JonReyno]

Hi All,

I have the following formula which works, but the ranges that I am using are now broken up. Is there a way to put in a non-contiguous range in their place?


=MINIFS(B5:B16,C5:C16,">"&0)

Basically what I am trying to do is pull through the min date where there is a result next to it. I have a repeat of 3 columns, Date, Result & Comment, which is repeated 12 times. What I'm trying to do may not be possible, but I'm trying to figure out if I can do it.

Any help would be appreciated

Many Thanks
Jon

 

[Eric W]

Working with disjoint ranges is always a pain. The best I came up with is to treat all 3 ranges as a large range, but then to use MOD to ignore the columns that you don't want. Try:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1
2
3	2-Jan
4
5		1-Jan					1-Feb	5				1-Mar
6		2-Jan	1				2-Feb					2-Mar	6
7		3-Jan					3-Feb	5				3-Mar	6
8		4-Jan	3				4-Feb					4-Mar
9		5-Jan					5-Feb					5-Mar
10		6-Jan					6-Feb					6-Mar
11		7-Jan					7-Feb					7-Mar
12		8-Jan	7				8-Feb	7				8-Mar
13		9-Jan					9-Feb					9-Mar	9
14		10-Jan					10-Feb					10-Mar
15		11-Jan					11-Feb					11-Mar
16		12-Jan					12-Feb					12-Mar
Sheet1

Cell Formulas
Range	Formula
A3	A3	=MIN(IF(MOD(COLUMN(C3:M16)-COLUMN(C3),5)=0,IF(C3:M16>0,B3:L16)))
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

This assumes that the columns you want are 5 columns apart. If the actual distance is different, change the 5 in the formula.

 

[JonReyno]

Thank you so much Eric, this has worked a treat!! Sadly my colleagues don't seem to take into account how much of a pain some of things they ask are to actually create! If I had £ for every time someone said 'can you just...' I'd be able to retire!

Thank you for your help, it's truly appreciated

 

[Eric W]

Apparently you keep coming through, or they'd stop asking!

Glad I could help!

 

[JonReyno]

It would appear so! haha... My manager has already said I have made a rod for my own back. Once word got out that I can use Excel. everyone seems to want to be my best friend.... funny that!