Excel BI - Excel Challenge 670

excel-challenges
excel-formulas
🔰 Answer Expected Org Classification Detail Year Revenue Cost Profit A B
Published

March 24, 2026

Illustration for Excel BI - Excel Challenge 670

Challenge Description

🔰 Answer Expected Org Classification Detail Year Revenue Cost Profit A B

Solutions

library(tidyverse)
library(readxl)

path = "Excel/670 Transpose.xlsx"
input = read_excel(path, range = "A2:C11")
test  = read_excel(path, range = "E2:I5")

result = input %>%
  mutate(Year = ifelse(Classification == "Year", Detail, NA)) %>%
  fill(Year) %>%
  filter(Classification != "Year") %>%
  pivot_wider(names_from = Classification, values_from = Detail) %>%
  mutate(Profit = Revenue - Cost)

all.equal(result, test, check.attributes = FALSE)
#> [1] TRUE
  • Logic: Read the workbook ranges needed for the challenge; Derive the required intermediate columns; Reshape the result into the workbook output format; Apply the business rule conditions explicitly.
  • Strengths: The reshaping step mirrors the workbook output closely instead of forcing extra post-processing.
  • 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 last reshape turns a raw transformation into something that already looks like a report.
import pandas as pd

path = "670 Transpose.xlsx"
input = pd.read_excel(path, sheet_name=0, usecols="A:C", skiprows=1, nrows=10)
test = pd.read_excel(path, sheet_name=0, usecols="E:I", skiprows=1, nrows=3).rename(columns=lambda x: x.split('.')[0])

input['Year'] = input.apply(lambda row: row['Detail'] if row['Classification'] == 'Year' else None, axis=1)
input['Year'] = input['Year'].ffill().astype('int64')
input = input[input['Classification'] != 'Year']
result = input.pivot(index=['Org','Year'], columns='Classification', values='Detail').reset_index()
result['Profit'] = result['Revenue'] - result['Cost']
result.columns.name = None
result = result[['Org', 'Year', 'Revenue', 'Cost', 'Profit']]

print(result.equals(test)) # True

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

Difficulty Level

Medium

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