offset the formula,1 cell below to start calculation

[ibmy]

Hi Experts,

I been thinking and try change current formula but not succesful to meet my need.

Current Formula : Start from currrent cell

Book1
	G		I	J
1	data		total	Count
2
3	0.6		3	2
4	0.1
5	0.8
6	0.3
Sheet3

Cell Formulas
Range	Formula
J3	J3	=IF(I3="","",COUNTIF(INDEX(G:G,ROW()+I3-1):G3,">0.5"))

The best solution I have tried so far : but the result still not same as row as value in Column J
I enter formula below row of value Column I in Column J, and change the formula from G3 to G4

Book1
	G		I	J
1	data		total	Count
2
3	0.6		3
4	0.1			1
5	0.8
6	0.3
Sheet8

Cell Formulas
Range	Formula
J4	J4	=IF(I3="","",COUNTIF(INDEX(G:G,ROW()+I3-1):G4,">0.5"))

This is my expected result :

Book1
	G		I	J
1	data		total	Expected Result
2
3	0.6		3	1
4	0.1
5	0.8
6	0.3
Sheet7

How to get this result?

Sample taken from 250k-300k row data.

 

[Legacy 143009]

Are you looking for something like this?

=IF(I3="","",COUNTIF(OFFSET(J3,1,-3,I3),">0.5"))

Note that OFFSET is not usually recommended because it is a volatile function. It won't store the values which may cause your computer to spend a noticeable time doing recalculations every time you try to change a cell.

 

[ibmy]

@Flashbond Thanks for the formula, it works.

I have 2 questions.

1) Does the formula you given is not recommended since it has OFFSET function?
If so, is there any other formula better than current formula that not use OFFSET function?

Current formula is fine for 250k-300k row data.

2 ) If my Data store in Column F, Total in Column K and Result in Column N,
Does this a correct formula I edited?

Book1
	F	G	H	I	J	K	L	M	N
1	data					total			Result
2
3	0.6					3			1
4	0.1
5	0.8
6	0.3
7	0
8	0.4
9	1.2
10	0.1					4			3
11	0.1
12	0.6
13	0.6
14	0.6
15	0.6
Sheet12

Cell Formulas
Range	Formula
N3:N15	N3	=IF(K3="","",COUNTIF(OFFSET(N3,1,-8,K3),">0.5"))

 

[Legacy 143009]

Your formula seems to be ok.

I am sure there must be a better solution but I don't know. Maybe someone may comes up with a better solution.