Excel BI - Excel Challenge 769

excel-challenges

excel-formulas

🔰 Populate the sum of the groups in first and last cell of that group.

Published

March 24, 2026

Illustration for Excel BI - Excel Challenge 769

Challenge Description

🔰 Populate the sum of the groups in first and last cell of that group. Groups are separated by blank rows.

Solutions

library(tidyverse)
library(readxl)

path = "Excel/700-799/769/769 Sum in First and Last Cells of Groups.xlsx"
input = read_excel(path, range = "A1:B21")
test  = read_excel(path, range = "C1:C21")

result = input %>%
  mutate(group = cumsum(is.na(Name))) %>%
  mutate(group = ifelse(is.na(Name), NA, group)) %>%
  mutate(sum = sum(Number, na.rm = TRUE), .by = group) %>%
  mutate(is_first_or_last = ifelse(row_number() == 1 | row_number() == n(), TRUE, FALSE), .by = group) %>%
  mutate(sum = ifelse(!is_first_or_last|sum==0, NA, sum)) %>%
  select(-is_first_or_last, -group)

all.equal(result$sum, test$`Answer Expected`)
# > [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; Apply the business rule conditions explicitly.
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

path = "700-799/769/769 Sum in First and Last Cells of Groups.xlsx"
input = pd.read_excel(path, usecols="A:B", nrows=21)
test = pd.read_excel(path, usecols="C", nrows=21)

input['group'] = input['Name'].isna().cumsum().mask(input['Name'].isna())
input['sum'] = input.groupby('group', dropna=True)['Number'].transform('sum')

def is_first_or_last(s):
    mask = [False] * len(s)
    if len(s) > 0:
        mask[0] = True
        mask[-1] = True
    return mask

mask = input.groupby('group', dropna=True)['Name'].transform(lambda x: x.index.isin([x.index[0], x.index[-1]]))
input['sum'] = input['sum'].where(mask & (input['sum'] != 0))
result = input

print(result['sum'].equals(test['Answer Expected']))

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.