Excel BI - Excel Challenge 857

🔰 For the given comma separated data, find the total commission for each Salesperson.

Illustration for Excel BI - Excel Challenge 857

Challenge Description

🔰 For the given comma separated data, find the total commission for each Salesperson. You will need to calculate monthly commissions on the basis of Total Sales and then sum the monthly commissions.

Solutions

R Solution

library(tidyverse)
library(readxl)

path <- "Excel/800-899/857/857 Tiered Commission.xlsx"
input <- read_excel(path, range = "A2:A62", col_names = FALSE)
test <- read_excel(path, range = "C2:D13")

tier_commisions = function(amount) {
  case_when(
    amount <= 50000 ~ 0.05 * amount,
    amount <= 100000 ~ 2500 + 0.07 * (amount - 50000),
    amount <= 200000 ~ 6000 + 0.10 * (amount - 100000),
    TRUE ~ 16000 + 0.15 * (amount - 200000)
  )
}

result = input %>%
  separate_wider_delim(cols = 1, delim = ",", names_sep = "_") %>%
  janitor::row_to_names(1) %>%
  select(Salesperson, Month, `Total Sales`) %>%
  mutate(Comission = tier_commisions(as.numeric(`Total Sales`))) %>%
  summarise(Total_Commission = sum(Comission), .by = Salesperson) %>%
  arrange(Salesperson) %>%
  janitor::adorn_totals("row", name = "Grand Total")

all.equal(result, test, check.attributes = FALSE)
# TRUE

R Analysis

• 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.

Python Solution

import pandas as pd

def tier_commissions(amount):
    if amount <= 50000: return int(0.05 * amount)
    if amount <= 100000: return int(2500 + 0.07 * (amount - 50000))
    if amount <= 200000: return int(6000 + 0.10 * (amount - 100000))
    return int(16000 + 0.15 * (amount - 200000))

path = "Excel/800-899/857/857 Tiered Commission.xlsx"
input = pd.read_excel(path, usecols="A", nrows=62, skiprows=1, header=None)
test = pd.read_excel(path, usecols="C:D", nrows=11, skiprows=1)

df = input.iloc[:, 0].str.split(",", expand=True)
df.columns = df.iloc[0]; df = df.drop(index=0)
df["Total Sales"] = df["Total Sales"].astype(int)
df["Commission"] = df["Total Sales"].apply(tier_commissions)

result = (
    df.groupby("Salesperson", as_index=False)["Commission"].sum()
      .rename(columns={"Commission": "Total_Commission"})
      .sort_values("Salesperson").reset_index(drop=True)
)
grand_total = pd.DataFrame({
    "Salesperson": ["Grand Total"],
    "Total_Commission": [result["Total_Commission"].sum()]
})
final_result = pd.concat([result, grand_total], ignore_index=True)

print(final_result['Total_Commission'].equals(test['Total Commission'].astype(int))) # True

Python

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.

Related Challenges

• Excel Challenge 858
• Excel Challenge 859
• Excel Challenge 860