Excel BI - Excel Challenge 656

excel-challenges
excel-formulas
🔰 Find the critical path and its duration for this
Published

March 24, 2026

Illustration for Excel BI - Excel Challenge 656

Challenge Description

🔰 Find the critical path and its duration for this

Solutions

library(tidyverse)
library(readxl)
library(igraph)

path = "Excel/656 Critical Path.xlsx"
input = read_excel(path, range = "A2:C12")
test  = read_excel(path, range = "D2:E3")

r1 = input %>%
  na.omit() %>%
  separate_rows(Predecessor, sep = ", ") %>%
  relocate(Predecessor, .before = Task)
g = graph_from_data_frame(r1, directed = TRUE)

longest_path = get_diameter(g, weights = E(g)$Duration)
lp = tibble(path = V(g)[longest_path]$name) %>%
  left_join(r1, by = c("path" = "Task")) %>%
  summarise(Duration = sum(Duration, na.rm = TRUE),
            `Critical Path` = paste(path, collapse = "-")) %>%
  mutate(`Critical Path` = paste0(`Critical Path`, "-End")) %>%
  select(2, 1)

all.equal(lp, test, check.attributes = FALSE)  
# [1] TRUE
  • Logic: Read the workbook ranges needed for the challenge; Derive the required intermediate columns; Parse the packed text or string structure; Aggregate or rank the data at the required grouping level.
  • Strengths: The code maps the workbook rule into a compact, reproducible pipeline.
  • Areas for Improvement: The solution assumes the workbook layout and selected ranges remain stable, so any structural change in the sheet would require small adjustments.
  • Gem: The elegant part is how little code is needed once the correct intermediate representation is chosen.
import pandas as pd
import networkx as nx

input = pd.read_excel("656 Critical Path.xlsx", usecols="A:C", skiprows=1, nrows=10).dropna(subset=['Predecessor '])
input = input.assign(Predecessor=input['Predecessor '].str.split(',')).explode('Predecessor')[['Task', 'Duration', 'Predecessor']]

G = nx.from_pandas_edgelist(input, 'Predecessor', 'Task', ['Duration'], create_using=nx.DiGraph())
longest_path = nx.dag_longest_path(G, weight='Duration')

lp = pd.DataFrame(longest_path, columns=['path']).merge(input, left_on='path', right_on='Task', how='left')[['Task', 'Duration']].dropna()
summary = pd.DataFrame({'Task': ['Start-' + '-'.join(lp['Task']) + '-End'], 'Duration': [lp['Duration'].sum().astype("int64")]})

test = pd.read_excel("656 Critical Path.xlsx", usecols="D:E", skiprows=1, nrows=1)
test.columns = summary.columns
print(summary.equals(test))  # True

The Python version mirrors the same workbook logic with a concise, direct implementation.

Difficulty Level

Easy / Medium

The business rule is clear, though the workbook still needs a few transformation steps to reach the expected output.