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 ...

 

[jxsavage]

Sorry, first one should be A2-A5

 

[Rick Rothstein]

[TABLE="width: 256"]
<TBODY>[TR]
[TD="class: xl65, width: 64"][/TD]
[TD="class: xl66, width: 64"]A
[/TD]
[TD="class: xl66, width: 64"]B
[/TD]
[TD="class: xl66, width: 64"]C
[/TD]
[/TR]
[TR]
[TD="class: xl66"]1
[/TD]
[TD="class: xl65"]5
[/TD]
[TD="class: xl65"]FALSE
[/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl66"]2
[/TD]
[TD="class: xl65"]7
[/TD]
[TD="class: xl65"]TRUE
[/TD]
[TD="class: xl65"]A1-A5
[/TD]
[/TR]
[TR]
[TD="class: xl66"]3
[/TD]
[TD="class: xl65"]3
[/TD]
[TD="class: xl65"]FALSE
[/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl66"]4
[/TD]
[TD="class: xl65"]6
[/TD]
[TD="class: xl65"]FALSE
[/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl66"]5
[/TD]
[TD="class: xl65"]9
[/TD]
[TD="class: xl65"]TRUE
[/TD]
[TD="class: xl65"]A5-A6
[/TD]
[/TR]
[TR]
[TD="class: xl66"]6
[/TD]
[TD="class: xl65"]8
[/TD]
[TD="class: xl65"]TRUE
[/TD]
[TD="class: xl65"]A6-A8
[/TD]
[/TR]
[TR]
[TD="class: xl66"]7
[/TD]
[TD="class: xl65"]1
[/TD]
[TD="class: xl65"]FALSE
[/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl66"]8
[/TD]
[TD="class: xl65"]5
[/TD]
[TD="class: xl65"]TRUE
[/TD]
[TD="class: xl65"]A8-A9
[/TD]
[/TR]
[TR]
[TD="class: xl66"]9
[/TD]
[TD="class: xl65"]4
[/TD]
[TD="class: xl65"]TRUE
[/TD]
[TD="class: xl65"]…
[/TD]
[/TR]
</TBODY>[/TABLE]

Edit Change: Sorry, first one should be A2-A5

Okay, I think you can do what you want using a formula. Place this formula in C1 and copy it down to the end of your data...

=IF(B1=FALSE,0,IF(B2="","...",A1-INDEX(A:A,ROW(B1)+MATCH(TRUE,B2:B$1000,0))))

Note: I guessed that you will not have more than 1000 rows of data... if that is wrong, then change the highlighted number to a row number large enough to always handle you full set of data.

 

[jxsavage]

Not sure if this will help, but this is the concept I'm thinking of in VBA, but no results display when the first Boolean = True. The result is always returning 0.


Function SubtractMatch(boolean1, boolean2, value1, value2)
' variables will be, for example, (B1,B2,A1,A2)

If boolean1 = False Then
SubtractMatch = 0
Else
If boolean2 = True Then
SubtractMatch = value1 - value2
Else:
Do
boolean2 = boolean2 + 1 And value2 = value2 + 1 'This is where I want script to start stepping through rows.
Exit Do
Loop Until boolean2 = True
Subractmatch = value1 - value2
End If
End If
End Function

 

[Rick Rothstein]

Not sure if this will help, but this is the concept I'm thinking of in VBA,

Are you saying you only want a VBA solution? Or did you not see the formula I posted in Message #12 yet?

 

[jxsavage]

Are you saying you only want a VBA solution? Or did you not see the formula I posted in Message #12 yet?

I hadn't seen Message 12 yet, but I'm open to anything.

I tried plugging in that formula, but received an error in both cases of true or false.