Can someone help find the Excel formula

[ashliiii]

Suppose I have the cells A1 to A7 filled with the following data:

A1 = 4500
A2 = 5150
A3 = 3120
A4 = 8990
A5 = 6450
A6 = 7770
A7 = 2145

What is the formula I should have in cell B1 when copied to B7, should display as follows:

B1 = 0
B2 = 1
B3 = 0
B4 = 1
B5 = 2
B6 = 3
B7 = 0

That is in when a value in column A cell is more than 5000, column B should add the count by 1 but reset to zero each time value drop below 5000.

 

[awoohaw]

There will probably be many solutions posted for this. But, here is one. It does require starting in Row2, though.

Book2
	A	B	C
1
2	4500	0	0
3	5150	1	1
4	3120	0	0
5	8990	1	1
6	6450	2	2
7	7770	3	3
8	2145	0	0
Sheet1

Cell Formulas
Range	Formula
C2:C8	C2	=N(C1)+IF(A2>5000,1,-N(C1))

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[ashliiii]

There will probably be many solutions posted for this. But, here is one. It does require starting in Row2, though.

Book2
	A	B	C
1
2	4500	0	0
3	5150	1	1
4	3120	0	0
5	8990	1	1
6	6450	2	2
7	7770	3	3
8	2145	0	0
Sheet1

Cell Formulas
Range	Formula
C2:C8	C2	=N(C1)+IF(A2>5000,1,-N(C1))

Thank you for your time.

 

[awoohaw]

Thank you for your time.

You are welcome. But, I'm not sure what that reply means in the context of a solution to your question.

 

[Cubist]

Some variations.

Book1
	A	B	C
1
2	4500	0	0
3	5150	1	1
4	3120	0	0
5	8990	1	1
6	6450	2	2
7	7770	3	3
8	2145	0	0
Sheet1

Cell Formulas
Range	Formula
C2:C8	C2	=SCAN(0,A2:A8,LAMBDA(a,b,(a+1)*(b>=5000)))
B2:B8	B2	=(B1+1)*(A2>=5000)
Dynamic array formulas.