Excel BI - Excel Challenge 870

excel-challenges
excel-formulas
🔰 Work out the Amount column which is Answer Expected.
Published

March 24, 2026

Illustration for Excel BI - Excel Challenge 870

Challenge Description

🔰 Work out the Amount column which is Answer Expected.

Solutions

library(tidyverse)
library(readxl)

path <- "Excel/800-899/870/870 Doctor Fee.xlsx"
input <- read_excel(path, range = "A1:C51")
test <- read_excel(path, range = "D1:D51")

result = input %>%
  group_by(PatientID, DiseaseID) %>%
  mutate(interval = as.numeric(ymd(Date) - lag(ymd(Date)))) %>%
  mutate(
    `Answer Expected` = case_when(
      is.na(interval) ~ 100,
      interval <= 14 ~ 0,
      TRUE ~ 100
    )
  )

result$`Answer Expected` == test$`Answer Expected`
# one position provided wrong in the test data
  • 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 transformation is organized around the correct grouping level, which keeps the business logic clear.
  • 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 key move is solving the problem at the right grain before shaping the final output.
import pandas as pd

path = "Excel/800-899/870/870 Doctor Fee.xlsx"
input = pd.read_excel(path, usecols="A:C", nrows=51)
test = pd.read_excel(path, usecols="D", nrows=51)

result = (input.assign(Date=pd.to_datetime(input["Date"]))
          .assign(interval=lambda d: d.groupby(["PatientID","DiseaseID"])["Date"].diff().dt.days,
                  Answer_Expected=lambda d: (d["interval"].isna() | (d["interval"]>14)).mul(100))
         )

print(result["Answer_Expected"].to_numpy() == test["Answer Expected"].to_numpy())
# one result is not correct

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

Difficulty Level

Medium

The individual steps are manageable, but the correct transformation pattern is not obvious from the raw data.