Logic Formulas for Dependency Tables

Alhelor

New Member
Joined
Jun 15, 2017
Messages
29
Hey folks,

Take a look at these three dependency tables:

5SgH16D.png


Let's take Dog as an example. In order to produce a Dog, you need to consume a Car and a House (1st degree dependency, first table). However, in order to produce a Car, you need to consume a Computer, and in order to produce a House, you need to consume a Cake. You can see it in the first table. However, it makes the Dog also dependent on a Computer and a Cake in 2nd degree (2nd table) because without those there's no Car and no House.

Now, Cake has no dependencies in the first table, so it can be assumed given. However, producing a Computer requires Your Mom. Thus, the Dog is dependent on Your Mom in 3rd degree (3rd table).

My question: What would the formulas in table 2 and 3 look like? Which functions to use?

If something is dependent on itself, return "".

Thank you!
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
It's essentially the same formula for both tables:

ABCDEFGH
1st degree dependencies
Green consumes RedCarDogHouseComputerYour MomWizardCake
Carx
Dogxx
Housex
Computerx
Your Mom
Wizardx
Cake
2nd degree dependencies
Green consumes RedCarDogHouseComputerYour MomWizardCake
Carx
Dogxx
House
Computer
Your Mom
Wizard
Cake
3rd degree dependencies
Green consumes RedCarDogHouseComputerYour MomWizardCake
Car
Dogx
House
Computer
Your Mom
Wizard
Cake

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: center"]5[/TD]

[TD="bgcolor: #757171, align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="bgcolor: #757171, align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #757171, align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #757171, align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #757171, align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="bgcolor: #757171, align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #757171, align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]

[TD="align: center"]17[/TD]

[TD="bgcolor: #757171"][/TD]

[TD="align: center"]18[/TD]

[TD="bgcolor: #757171"][/TD]

[TD="align: center"]19[/TD]

[TD="bgcolor: #757171"][/TD]

[TD="align: center"]20[/TD]

[TD="bgcolor: #757171"][/TD]

[TD="align: center"]21[/TD]

[TD="bgcolor: #757171"][/TD]

[TD="align: center"]22[/TD]

[TD="bgcolor: #757171"][/TD]

[TD="align: center"]23[/TD]

[TD="bgcolor: #757171"][/TD]

[TD="align: center"]24[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]25[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]26[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]27[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]28[/TD]

[TD="align: center"]29[/TD]

[TD="bgcolor: #757171"][/TD]

[TD="align: center"]30[/TD]

[TD="bgcolor: #757171"][/TD]

[TD="align: center"]31[/TD]

[TD="bgcolor: #757171"][/TD]

[TD="align: center"]32[/TD]

[TD="bgcolor: #757171"][/TD]

[TD="align: center"]33[/TD]

[TD="bgcolor: #757171"][/TD]

[TD="align: center"]34[/TD]

[TD="bgcolor: #757171"][/TD]

[TD="align: center"]35[/TD]

[TD="bgcolor: #757171"][/TD]

</tbody>
Sheet4

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]B17[/TH]
[TD="align: left"]{=IF(INDEX(MMULT(($B5:$H5="x")+0,($B$5:$H$11="x")+0),COLUMNS($B17:B17)),"x","")}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]



Put the formula in B17, confirm with Control+Shift+Enter. Then copy to B17:H23 and B29:H35.

It does rely on the gap between tables being the same. If you change the ranges, you may need to adjust the formula within each table to match the proper ranges.

Hope this helps!
 
Upvote 0
Glad I could help!

Marcelo, Rada1, and Alhelor, thanks for the likes! I have to admit, I'm pretty happy with how that turned out. After I got it working, it simplified really nice.
 
Upvote 0
Glad I could help!

Marcelo, Rada1, and Alhelor, thanks for the likes! I have to admit, I'm pretty happy with how that turned out. After I got it working, it simplified really nice.

I slightly adjusted your formula to 1. avoid dependencies on oneself and 2. avoid multiple degree dependencies at once. It now looks like this:

[TABLE="width: 1078"]
<tbody>[TR]
[TD][TABLE="width: 1070"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]B17[/TH]
[TD="align: left"]{=IF($A17=B$16,"",(IF(B5="x","",IF(INDEX(MMULT(($B5:$H5="x")+0,($B$5:$H$11="x")+0),COLUMNS($B17:B17)),"x","")))}[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

I posted another thread for a (much more complex) continuation https://www.mrexcel.com/forum/excel...ons-dynamic-dependency-table.html#post4848271. Feel free to check it out! :stickouttounge:
 
Last edited:
Upvote 0

Forum statistics

Threads
1,223,246
Messages
6,170,988
Members
452,373
Latest member
TimReeks

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