CODEWITHSUNDEEP
excelexcel-formula
vxpoisongas
vxpoisongas

Reputation: 67

Excel formula in counting distance between a specific value in rows

I am dealing with unlimited data that keeps adding a combination of rows every-time. I wanted a formula that would count the row distance between the First Lower limit to the Second Lower Limit. Please no VBA i want a formula only or array formula perhaps. I would truly appreciate it if someone could help. I have been trying to figure this out for months now.

Example:

Upvotes: 0

Views: 2834

Answers (2)

Marco Vos
Marco Vos

Reputation: 2968

This should work. Paste in cell I2 and commit as an array-formula [control-shift-enter]. Then pull down.

=IF(LARGE(IF((A$1:F$1000=H2),ROW(A$1:A$1000),0),2)=0,0,LARGE(IF((A$1:F$1000=H2),ROW(A$1:A$1000),0),1)-LARGE(IF((A$1:F$1000=H2),ROW(A$1:A$1000),0),2))+1

Upvotes: 1

Rosetta
Rosetta

Reputation: 2725

array formula:

{=SMALL(IF((C:C=E7),ROW(A:A),2^20),1)-SMALL(IF((B:B=E7),ROW(A:A),2^20),1)+1}

fill up and down.

enter image description here

Upvotes: 0

Related Questions