Excel BI - Excel Challenge 704

🔰 Challenge: Find the Subgrid with the Maximum Sum Your task is to find the subgrid with the maximum total sum For each rectangle formed, calculate its total sum.

Illustration for Excel BI - Excel Challenge 704

Challenge Description

🔰 Challenge: Find the Subgrid with the Maximum Sum Your task is to find the subgrid with the maximum total sum For each rectangle formed, calculate its total sum. A subgrid of width 2 and height 2 (i.e., B3:C4) has a sum of -71 A subgrid of width 3 and height 3 (B3:D5) has a sum of -252 The best sum based at cell B1 might be 266 Find the subgrid with the

Solutions

R Solution

library(tidyverse)
library(readxl)
library(matrixStats)

path = "Excel/704 WhereMaxBlock.xlsx"
input = read_excel(path, range = "A3:J16", col_names = FALSE) %>% as.matrix()
test = read_excel(path, range = "L2:L3") %>% pull()

nr = nrow(input)
nc = ncol(input)

submatrices = expand.grid(
  i = 1:(nr - 1),
  j = 1:(nc - 1),
  k = 2:nr,
  l = 2:nc
) %>%
  filter(k > i, l > j) %>%
  pmap(function(i, j, k, l) input[i:k, j:l])


submatrices_df = tibble(
  sum = map_dbl(submatrices, ~ sum(.x, na.rm = TRUE)),
  dims = map_chr(submatrices, ~ paste(dim(.x), collapse = " x ")),
  start_cell = map_chr(
    submatrices,
    ~ paste(which(input == .x[1], arr.ind = TRUE)[1, ], collapse = " x ")
  )
) %>%
  slice_max(order_by = sum, n = 1)

result = glue::glue(
  "({submatrices_df$dims}), {submatrices_df$sum}, [{submatrices_df$start_cell}]"
)

R Analysis

• Logic: Read the workbook ranges needed for the challenge.
• 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
import numpy as np
from itertools import product

path = "704 WhereMaxBlock.xlsx"
input_data = pd.read_excel(path, sheet_name=0, header=None, skiprows=2, usecols="A:J").to_numpy()
test = pd.read_excel(path, sheet_name=0, usecols="L", nrows=2).squeeze().to_list()

nr, nc = input_data.shape
submatrices = []
for i, j, k, l in product(range(nr), range(nc), range(1, nr + 1), range(1, nc + 1)):
    if k > i and l > j:
        submatrices.append(input_data[i:k, j:l])
submatrices_df = pd.DataFrame({
    "sum": [np.nansum(submatrix) for submatrix in submatrices],
    "dims": [f"{submatrix.shape[0]} x {submatrix.shape[1]}" for submatrix in submatrices],
    "start_cell": [
        f"{np.where(input_data == submatrix[0, 0])[0][0] + 1} x {np.where(input_data == submatrix[0, 0])[1][0] + 1}"
        for submatrix in submatrices
    ]
})

max_submatrix = submatrices_df.loc[submatrices_df["sum"].idxmax()]
result = f"({max_submatrix['dims']}), {max_submatrix['sum']}, [{max_submatrix['start_cell']}]"
print(result)

Python

The Python version keeps the algorithm explicit, which helps when the challenge depends on a greedy or iterative rule.

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 705
• Excel Challenge 706
• Excel Challenge 707