Excel BI - Excel Challenge 703

excel-challenges
excel-formulas
🔰 It is required to calculate the total for each employee over consecutive (or separate) days and take the maximum value.
Published

March 24, 2026

Illustration for Excel BI - Excel Challenge 703

Challenge Description

🔰 It is required to calculate the total for each employee over consecutive (or separate) days and take the maximum value. For example, Thomas has consecutive days 1, 2, 3, 4, 5 with a total of 1790, and days 8, 9, 10 with a total of 1990. Therefore, the correct result will be Thomas - 1990.

Solutions

library(tidyverse)
library(readxl)

path = "Excel/703 Max_Total_By_Days.xlsx.xlsx"
input = read_excel(path, range = "A1:C31")
test = read_excel(path, range = "E2:F6") %>% arrange(Names)

result = input %>%
  mutate(consecutive_id = cumsum(c(1, diff(Date) > 1)), .by = Names) %>%
  summarise(Quantity = sum(Quantity), .by = c("Names", "consecutive_id")) %>%
  filter(Quantity == max(Quantity), .by = Names) %>%
  select(-consecutive_id) %>%
  arrange(Names)

all.equal(result, test)
#> [1] TRUE
  • Logic: Read the workbook ranges needed for the challenge; Derive the required intermediate columns; 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
from datetime import timedelta

path = "703 Max_Total_By_Days.xlsx.xlsx"
input = pd.read_excel(path, usecols="A:C", nrows=31)
test = pd.read_excel(path, usecols="E:F", skiprows=1, nrows=4).sort_values(by="Names").reset_index(drop=True)

input = input.sort_values(by=["Names", "Date"])
summary = (input.groupby('Names', group_keys=False)
           .apply(lambda group: group.assign(Group=group['Date'].diff().gt(timedelta(days=1)).cumsum()))
           .groupby(['Names', 'Group'], as_index=False)['Quantity'].sum()
           .loc[lambda df: df.groupby('Names')['Quantity'].idxmax()]
           .drop(columns=['Group'])
           .reset_index(drop=True))

print(summary.equals(test)) # True

The Python version follows the same grouped logic and keeps the transformation explicit in a dataframe pipeline.

Difficulty Level

Easy / Medium

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