Need help with formula

[Russk68]

Hi all,

I have 2 columns (A&B) by 8 rows. Values entered in column A can only contain a number between 1 & 8. Column B will show a 0 or 1 depending on a cell value based on column A. An empty cell or value of 1 in column A, will always show a value of 0 in the same row (ColB). A value of 2 in a cell in column A will show a 1 in the same row (ColB) and also the one below it. A value of 3 will show a 1 in the same row and also the next 2 rows and so on.
So, a value greater than 1 in column A will result a 1 in the same row (ColB) and as many rows down as the value entered in column A.

Basically, the 1 in column B is reserving rows based on the value entered in column A.

Consider yourself a genius if you even understand what I am saying.

Example:

.....A....B
1...1....0
2...2....1
3.........1
4.........0
5...3....1
6.........1
7.........1
8.........0

Thank you!

Russ

 

[jtakw]

Hi,

See how this works for you, need an Empty or Header row on top of your data, formula copied down:

Book1
	A	B
1
2	1	0
3	2	1
4		1
5		0
6	3	1
7		1
8		1
9		0
Sheet594

Cell Formulas
Range	Formula
B2	=IF(LOOKUP(9,A$2:A2)=1,0,IF(COUNTIF(B$1:B1,1)A$2:A2)-SUMIF(A$2:A2,1),1,0))

 

[Russk68]

Hi genius!
This works exactly as I explained. however, I discovered a bug in my example.

I can enter a number in every row. For example, if I enter 2 in the first 2 rows, it will result with a 1 in B1:B4. In this example, I would need B1:B3 with a 1.

Basically, the number in the lower row would take priority.

Desired result
....A...B
1.........
2...2...1
3...2...1
4........1
5........0
6........0
7........0
8........0
9........0

Would you be able to tweak you formula to get this result?

Thank you!

 

[jtakw]

Updated formula per your requirements, No longer needs a Blank or Header row:

Book1
	A	B
1		0
2	2	1
3	2	1
4		1
5		0
6		0
7		0
8		0
Sheet594

Cell Formulas
Range	Formula
B1	=IF(IFNA(LOOKUP(9,A$1:A1)=1,1),0,IF(ROW()-LOOKUP(9,A$1:A1,ROW(A$1:A1))9,A$1:A1),1,0))

Formula copied down.