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

J

jxsavage

New Member
Joined
Jul 19, 2013
Messages
13
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 ...
 
Sort by date Sort by votes
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))
 
Upvote 0
Apologies, the chart didn't format well... See this:

[TABLE="width: 256"]
<colgroup><col style="width:48pt" span="4" width="64"> </colgroup><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"]0[/TD]
[/TR]
[TR]
[TD="class: xl66"]2[/TD]
[TD="class: xl65"]7
[/TD]
[TD="class: xl65"]TRUE[/TD]
[TD="class: xl65"]5[/TD]
[/TR]
[TR]
[TD="class: xl66"]3[/TD]
[TD="class: xl65"]3[/TD]
[TD="class: xl65"]FALSE[/TD]
[TD="class: xl65"]0[/TD]
[/TR]
[TR]
[TD="class: xl66"]4[/TD]
[TD="class: xl65"]6[/TD]
[TD="class: xl65"]FALSE[/TD]
[TD="class: xl65"]0[/TD]
[/TR]
[TR]
[TD="class: xl66"]5[/TD]
[TD="class: xl65"]9[/TD]
[TD="class: xl65"]TRUE[/TD]
[TD="class: xl65"]1[/TD]
[/TR]
[TR]
[TD="class: xl66"]6[/TD]
[TD="class: xl65"]8[/TD]
[TD="class: xl65"]TRUE[/TD]
[TD="class: xl65"]3[/TD]
[/TR]
[TR]
[TD="class: xl66"]7[/TD]
[TD="class: xl65"]1[/TD]
[TD="class: xl65"]FALSE[/TD]
[TD="class: xl65"]0[/TD]
[/TR]
[TR]
[TD="class: xl66"]8
[/TD]
[TD="class: xl65"]5[/TD]
[TD="class: xl65"]TRUE[/TD]
[TD="class: xl65"]1[/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]
[TABLE="width: 256"]
<tbody>[TR]
[/TR]
[TR]
[/TR]
[TR]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
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.
 
Upvote 0
jxsavage said:
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 ...
Click to expand...
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?
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
jxsavage said:
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.
Click to expand...
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.
 
Upvote 0
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)
 
Upvote 0
[TABLE="width: 256"]
<colgroup><col style="width:48pt" span="4" width="64"> </colgroup><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"]0[/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"]0[/TD]
[/TR]
[TR]
[TD="class: xl66"]4[/TD]
[TD="class: xl65"]6[/TD]
[TD="class: xl65"]FALSE[/TD]
[TD="class: xl65"]0[/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"]0[/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]
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Solved
Text to Columns Question
Replies
2
Views
172
Steve001
S
A
  • Solved
VBA Case Error
Replies
2
Views
189
Ark68
A
I
  • Question Question
Advice for Could Not Find The Specific Object message
2
Replies
12
Views
240
ipbr21054
I
R
  • Question Question
Required Transposed Values instead of Row Items of Duplicate Invoice no with Each GSTIN which is displaying the SUM of 12+18+28
Replies
1
Views
111
rk_rk
R
G
  • Solved
How to Solve Undesirable Alt key Behavior?
2
Replies
12
Views
326
Jaafar Tribak
Jaafar Tribak

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top