Function to replicate nested If-Then-Else for unlimited cases

[jxsavage]

Hi,

New to this forum, so I apologize in advance if this is being placed in the wrong section.

I'm working on replicating a formula that I currently have created without VBA, but excel limits its functionality. The context of this formula is as follows

For C1
if B1 = false then C1 = 0, else
if B2 = false then check B3, B4,... until Bx = true
When Bx = true then A1-Ax

As noted in the title,I need to step through unlimited records, but with excel only allows for 7 nested if functions. Unfortunately, the data I'm working with may have a dozen records in a row where the B field shows a false statement. Below is a better visual representation of the spread sheet and the desired outcomes in column C:

A B C
1| 5 False 0
2| 7 True 5
3| 3 False 0
4| 6 False 0
5| 9 True 1
6| 8 True 3
7| 1 False 0
8| 5 True 1
9| 4 True ...

 

[njimack]

Welcome to the Board.

I don't entirely follow what you're trying to do, but if it's simply a case of retrieving the value from column A when column B = TRUE, you can use =INDEX(A1:A9,MATCH(TRUE,B1:B9,0))

 

[jxsavage]

Apologies, the chart didn't format well... See this:

	A	B	C
1	5	FALSE	0
2	7	TRUE	5
3	3	FALSE	0
4	6	FALSE	0
5	9	TRUE	1
6	8	TRUE	3
7	1	FALSE	0
8	5	TRUE	1
9	4	TRUE	…

<colgroup><col style="width:48pt" span="4" width="64"> </colgroup><tbody>
</tbody>

<tbody>
</tbody>

 

[Scott Huish]

You probably don't need that many IFs. Why is your result 5 in line 2? You said it would be A1-Ax, assuming x is the row number it found True on, shouldn't that be -2 from 5-7. How do you get the other TRUE results? Please clarify.

 

[Rick Rothstein]

Hi,

New to this forum, so I apologize in advance if this is being placed in the wrong section.

I'm working on replicating a formula that I currently have created without VBA, but excel limits its functionality. The context of this formula is as follows

For C1
if B1 = false then C1 = 0, else
if B2 = false then check B3, B4,... until Bx = true
When Bx = true then A1-Ax

As noted in the title,I need to step through unlimited records, but with excel only allows for 7 nested if functions. Unfortunately, the data I'm working with may have a dozen records in a row where the B field shows a false statement. Below is a better visual representation of the spread sheet and the desired outcomes in column C:

A B C
1| 5 False 0
2| 7 True 5
3| 3 False 0
4| 6 False 0
5| 9 True 1
6| 8 True 3
7| 1 False 0
8| 5 True 1
9| 4 True ...

Two things are unclear to me from your description...

1) The part I highlighted in red... what does "then A1-Ax" mean?

2) The numbers I highlighted in blue (end Bx is True)... where do they come from or how are they calculated?

 

[jxsavage]

No, I'm trying to find the difference from between A1 and the corresponding A value to the next "true" column B value. Which could be any number of rows after.

 

[jxsavage]

Sorry, I made an math error in my example, the value should be -2 in that cell. But to phrase differently, looking at row 1, I simply want to subtract A1 from the corresonding A value for the next B value that is "True". As noted before, I can only search 7 rows with the nested if function.

 

[Rick Rothstein]

No, I'm trying to find the difference from between A1 and the corresponding A value to the next "true" column B value. Which could be any number of rows after.

Okay, I see that A1-Ax is meant to be a calculated subtraction, but I don't see how all the numbers I marked in blue come about from that subtraction. Can you repeat the chart, but instead of showing numbers when Bx is True, show us which two cells (addresses) you are subtracting to get those numbers.

 

[jxsavage]

The true value is arbitrary. It's just a condition. When the next B value in the range equals a set of values considered true, then subract A1 from that corresponding A value (Ax could be A2, A5, A100)

 

[jxsavage]

	A	B	C
1	5	FALSE	0
2	7	TRUE	A1-A5
3	3	FALSE	0
4	6	FALSE	0
5	9	TRUE	A5-A6
6	8	TRUE	A6-A8
7	1	FALSE	0
8	5	TRUE	A8-A9
9	4	TRUE	…

<colgroup><col style="width:48pt" span="4" width="64"> </colgroup><tbody>
</tbody>