# Need help with formula

#### Russk68

##### Active Member
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

### Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

#### jtakw

##### Well-known Member
Hi,

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

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="text-align: right;;">1</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: right;;">2</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: right;;"></td><td style="text-align: right;;">1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="text-align: right;;"></td><td style="text-align: right;;">0</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="text-align: right;;">3</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="text-align: right;;"></td><td style="text-align: right;;">1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style="text-align: right;;"></td><td style="text-align: right;;">1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style="text-align: right;;"></td><td style="text-align: right;;">0</td></tr></tbody></table><p style="width:6.4em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet594</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B2</th><td style="text-align:left">=IF(<font color="Blue">LOOKUP(<font color="Red">9,A\$2:A2</font>)=1,0,IF(<font color="Red">COUNTIF(<font color="Green">B\$1:B1,1</font>)<SUM(<font color="Green">A\$2:A2</font>)-SUMIF(<font color="Green">A\$2:A2,1</font>),1,0</font>)</font>)</td></tr></tbody></table></td></tr></table><br />

#### Russk68

##### Active Member
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

##### Well-known Member
Updated formula per your requirements, No longer needs a Blank or Header row:

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;">0</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="text-align: right;;">2</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: right;;">2</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: right;;"></td><td style="text-align: right;;">1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="text-align: right;;"></td><td style="text-align: right;;">0</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="text-align: right;;"></td><td style="text-align: right;;">0</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="text-align: right;;"></td><td style="text-align: right;;">0</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style="text-align: right;;"></td><td style="text-align: right;;">0</td></tr></tbody></table><p style="width:6.4em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet594</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B1</th><td style="text-align:left">=IF(<font color="Blue">IFNA(<font color="Red">LOOKUP(<font color="Green">9,A\$1:A1</font>)=1,1</font>),0,IF(<font color="Red">ROW(<font color="Green"></font>)-LOOKUP(<font color="Green">9,A\$1:A1,ROW(<font color="Purple">A\$1:A1</font>)</font>)<LOOKUP(<font color="Green">9,A\$1:A1</font>),1,0</font>)</font>)</td></tr></tbody></table></td></tr></table><br />

Formula copied down.

Replies
1
Views
24
Replies
3
Views
94
Replies
3
Views
86
Replies
7
Views
92
Replies
8
Views
111

Threads
1,108,990
Messages
5,526,096
Members
409,685
Latest member
Bellybb