Excel BI - Excel Challenge 925

🔰 Enumerate valid menu combinations and choose the most expensive affordable order for each person.

Illustration for Excel BI - Excel Challenge 925

Challenge Description

🔰 Table 1 contains menu items and Table 2 contains people and their budgets. Each person must order exactly one item from Mains and may optionally order one item from either Drinks or Dessert, for a maximum of two items in total. Return the item combination that spends the maximum amount without exceeding each person’s budget.

Solutions

R Solution

library(tidyverse)
library(readxl)

path <- "900-999/925/925 Max Order.xlsx"
input1 <- read_excel(path, range = "A1:C7")
input2 <- read_excel(path, range = "E1:F4")
test <- read_excel(path, range = "G1:G4")

i1 <- input1 %>%
  fill(everything(), .direction = "down")
mains <- i1 %>% filter(Category == "Mains")
dnd <- i1 %>%
  filter(Category != "Mains") %>%
  add_row(Category = "DND", Item = "", Price = 0)

deal <- expand.grid(mains$Item, dnd$Item) %>%
  left_join(i1, by = c("Var1" = "Item")) %>%
  left_join(i1, by = c("Var2" = "Item")) %>%
  rowwise() %>%
  transmute(
    deal = paste0(c(Var1, Var2), collapse = ", ") %>%
      str_replace_all(", $", "") %>%
      str_replace_all("^, ", ""),
    price = Price.x + coalesce(Price.y, 0)
  )

result <- input2 %>%
  cross_join(deal) %>%
  filter(price <= Amount) %>%
  slice_max(price, n = 1, by = Name) %>%
  select(`Answer Expected` = deal)

all.equal(result$`Answer Expected`, test$`Answer Expected`)
# [1] TRUE

R Analysis

• Logic: Generate all legal main-plus-optional deals, cross them with budgets, filter affordable combinations, and keep the most expensive one per person.
• Strengths: Adding a zero-cost blank option removes the need for separate handling of “main only” cases.
• Areas for Improvement: Cartesian-product solutions are perfect here, but they scale only while the menu remains small.
• Gem: Modeling “no optional item” as a normal option with price zero makes the whole optimization cleaner.

Python Solution

import pandas as pd

path = "900-999/925/925 Max Order.xlsx"
input1 = pd.read_excel(path, usecols="A:C", nrows=7)
input2 = pd.read_excel(path, usecols="E:F", nrows=3)
test = pd.read_excel(path, usecols="G", nrows=3)

i1 = input1.ffill()
mains = i1[i1["Category"] == "Mains"]
dnd = i1[i1["Category"] != "Mains"]
dnd = pd.concat(
    [dnd, pd.DataFrame([{"Category": "DND", "Item": "", "Price": 0}])],
    ignore_index=True
)
deal = pd.MultiIndex.from_product([mains["Item"], dnd["Item"]], names=["Var1", "Var2"]).to_frame(index=False)
deal = (
    deal.merge(i1, left_on="Var1", right_on="Item", how="left")
        .merge(i1, left_on="Var2", right_on="Item", how="left", suffixes=(".x", ".y"))
)
deal["deal"] = (
    deal["Var1"].fillna("") + ", " + deal["Var2"].fillna("")
).str.replace(", $", "", regex=True).str.replace("^, ", "", regex=True)
deal["price"] = deal["Price.x"] + deal["Price.y"].fillna(0)
deal = deal[["deal", "price"]]

result = (
    input2.merge(deal, how="cross")
          .query("price <= Amount")
          .sort_values("price")
          .groupby("Name")
          .tail(1)[["deal"]]
          .rename(columns={"deal": "Answer Expected"})
          .sort_index()
          .reset_index(drop=True)
)

print(result.equals(test))
# True

Python

The Python solution makes the combinatorics explicit with a Cartesian product over mains and optional items. Once that candidate set exists, the budget optimization is just filtering plus “take the max affordable price” within each person.

Difficulty Level

Medium

The challenge is less about formulas and more about modeling the valid choice space correctly before optimizing it.

Related Challenges

• Excel Challenge 929
• Excel Challenge 930
• Excel Challenge 905