Excluding a cell from MINIFS calculation - error when excluded cell is equal to first cell in range

[bec_v]

I have a longitudinal dataset of size over time. Two example datasets are included in screenshot below.

At each timepoint, I am trying to find the minimum value in the range up to the current timepoint. I also need to exclude/ignore the size at Timepoint #1 (value in red) from all calculations.

I've had success using the MINIFS function as seen in the second example dataset below. In this example the formula is =MINIFS($B9:C9,$B9:C9,"<>"&$C$9). As can be seen, the minimum at Timepoint #2 = 50 since the function has correctly ignored the value of 50 in cell C9.

Where I am now coming across a problem is where the value in the cell to be excluded is equal to the value of the cell in the first range. This incorrectly returns a value of ZERO and can be seen in the first example dataset below. The same formula has been used: =MINIFS($B2:C2,$B2:C2,"<>"&$C$2) in this case. As can be seen, the size at Timepoint #0 = the size at Timepoint #1 (i.e. both are = 23). The minimum result = 0 when it should be = 23.

The formula works just fine at all other timepoints in both examples, it is only when the first cell in the range is same value as the cell to be excluded that the miscalculation occurs.

Does anyone know where I might be going wrong?!


View attachment 105116

 

[bec_v]

 

[FormR]

Hi, welcome to the forum!

Your example formulas don't really tie back to the images you posted. But using cells as per the image, perhaps you can try like this:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1
2
3		0	1	2	3	4	5	6	7	8	9	10
4		23	23	18	15	10	12	18	25	20	5	7
5				23	18	15	10	10	10	10	10	5
Sheet1

Cell Formulas
Range	Formula
D5:L5	D5	=MINIFS($B4:C4,$B3:C3,"<>1")