# Gantt Chart on Dependencies with Recursion in Google Sheets. Part 1

Our goal is a GANTT chart in Sheets based on dependencies.

Here is a sample for pizza making:

We know how much it takes for each operation:

`| Action | Duration, min. |`

|---------------------------------|----------------|

| Prepare Pizza dough | 15 |

| Wait for dough to 2x in size | 90 |

| Preheat the oven | 30 |

| Assemble the ingredients | 10 |

| Allow dough to rest | 7 |

| Create an 10-inch dough circle | 10 |

| Put ingredients on the pizza | 10 |

| Cook | 8 |

And we have dependencies, what comes before what and what comes next:

`| Action before | Action after |`

|---------------------------------|-------------------------------|

| Prepare Pizza dough | Wait for dough to 2x in size |

| Wait for dough to 2x in size | Preheat the oven |

| Wait for dough to 2x in size | Assemble the ingredients |

| Wait for dough to 2x in size | Allow dough to rest |

| Allow dough to rest | Create an 10-inch dough circle|

| Create an 10-inch dough circle | Put ingredients on the pizza |

| Put ingredients on the pizza | Cook |

| Preheat the oven | Cook |

The question is:

When will it take to make a pizza?

# Critical Chain

A Chain is:

A set of tasks you need to accomplish where you can’t start the next task until you finish the dependent one.

A simple analogy:

We are meant to make pizza tonight.

We need to understand the chain's time.

A “chain” here is:

- Prepare Pizza dough
- Wait for dough to 2x in size
- Preheat the oven
- Cook

Now let’s add another question:

How do we get all chains and calculate their total time?

# Recursion

Recursion is one of the nerdiest concepts in computer science.

- A task or function calls itself. And calls itself again. And again.
**Forever.** - Recursion
**stops when it reaches some condition**specified by the writer of the function.

> Why do nerds like recursive tasks?

>> Because they can do the same complex calculation a NORMAL task would… but much faster, with much less logic. PLUS it’s cool. 😎

Recursion is the only way (I see) to cook our pizza.

# Recursion + Google Sheets

🚨 REALLY SPECIAL THANKS goes to the following functions:

- LAMBDA and

- LET

These Google sheet functions allow us define and use RECURSIVE functions 🔄

Take this λ function:

`=λ(n, FACT, IF(n=0, 1, n* FACT(n-1, FACT)))`

If you’re willing to set it up in your sheet, you can use the following:

`=LET(FACT, λ(n, FACT, IF(n=0, 1, n * FACT(n-1, FACT))), FACT(5, FACT))`

🚀

Explanation:

`λ(n, FACT, ...)`

: This defines a λ function that takes`n`

(the number for which we want to calculate the factorial) and`FACT`

(a reference to the λ function itself for recursion.)`IF(n=0, 1, n * FACT(n-1, FACT))`

: This checks if n is 0. If it is, it returns 1 (base case). Otherwise, it multiplies n by the factorial of`n-1`

.`LET(FACT, λ(...), FACT(5, FACT))`

: This part defines the`FACT`

function using`LET`

and then calls`FACT`

with 5 as the initial value.

# The formula for getting all chains and their time

The formula is…

— — LONG DRUM ROLLS — —

The formula is…

No fear. Let’s spend the night with this formula explanation.

Drum rolls…

♪└|∵|┐♪└|∵|┐♪└|∵|┐

`=LET(_durations,B4:C11, _dependencies,E4:F11,c_0,`

"Toolset ↓",

mapVlookup_,LAMBDA(dataToFind, keyValuePairs, INDEX(VLOOKUP(dataToFind, keyValuePairs, 2, ))),

i_,LAMBDA(element, indx, INDEX(element,indx)),

last_,LAMBDA(element, INDEX(element, ROWS(FLATTEN(element)))),

col_,LAMBDA(array, columnIndex, INDEX(array,,columnIndex)),

find_,LAMBDA(array, value, columnIndex, IFNA(FILTER({array, SEQUENCE(ROWS(array))},

col_(array, columnIndex) = value))), c_1,

"Critical chain with recursion ↓",

chain_,LAMBDA(chain, dependencies, timesMap, chainTimes, self_,

LET(

firstElement, i_(chain, 1),

foundDependencies, find_(dependencies, firstElement, 2),

if (foundDependencies = "", if(1=0,

"Base case",

{ JOIN(","&char(10), chain),

JOIN(",", chainTimes),

SUM(chainTimes)}),if(1=0,

"Recursion in a loop, where `self_` calls chain_ from itself",

BYROW(foundDependencies, LAMBDA(foundLine,

self_(

FLATTEN(i_(foundLine, 1), chain),

dependencies,

timesMap,

FLATTEN( i_(timesMap, last_(foundLine)), chainTimes), self_)))) ) )),c_3,

"Calculating logics. Mapping time and launching chain_ in a MAP ↓",

timesMap,mapVlookup_(_dependencies, _durations),

MAP(SEQUENCE(ROWS(_dependencies)),LAMBDA(r,

chain_(

i_(_dependencies, r),

_dependencies,

timesMap,

i_(timesMap, r),

chain_)) ) )

♪┌|∵|┘♪┌|∵|┘♪┌|∵|┘

This formula gives us 3 important columns for the next calculations:

- Chain with all steps listed
- Each step (one action) length from this chain
- Total time needed to accomplish all steps in a chain

By letting Google Sheets do its black magic, we can understand:

🍕Each chain tasks

- How long does each one go through different steps
- How long the whole process takes → the longest chain!

Now — maybe you have a critical business decision to make. Like

When can you send the email newsletter for the 🏖️SUMMER mega-sale offer? (hint: after you eat the pizza)

# Conclusion

Welcome to“Pizza-making” ultimate quiz! This was part one. Stay tuned!