Need help with formula

Russk68

Active Member
Joined
May 1, 2006
Messages
473
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
 

Some videos you may like

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
Joined
Jun 29, 2014
Messages
5,146
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
Joined
May 1, 2006
Messages
473
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
Joined
Jun 29, 2014
Messages
5,146
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.
 

Watch MrExcel Video

Forum statistics

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

This Week's Hot Topics

Top