Design Excel Sum Formula

Design Excel Sum Formula - Problem

Array Hash Table String Graph Design Topological Sort Matrix Unknown

Design the basic function of Excel and implement the function of the sum formula.

Implement the Excel class:

Excel(int height, char width) Initializes the object with the height and the width of the sheet. The sheet is an integer matrix mat of size height x width with the row index in the range [1, height] and the column index in the range ['A', width]. All the values should be zero initially.
void set(int row, char column, int val) Changes the value at mat[row][column] to be val.
int get(int row, char column) Returns the value at mat[row][column].
int sum(int row, char column, List numbers) Sets the value at mat[row][column] to be the sum of cells represented by numbers and returns the value at mat[row][column]. This sum formula should exist until this cell is overlapped by another value or another sum formula.

Numbers format:

"ColRow" that represents a single cell. For example, "F7" represents the cell mat[7]['F'].
"ColRow1:ColRow2" that represents a range of cells. For example, "B3:F7" represents cells mat[i][j] for 3 ≤ i ≤ 7 and 'B' ≤ j ≤ 'F'.

Note: You could assume that there will not be any circular sum reference.

Input & Output

Example 1 — Basic Operations

$ Input: Excel(3,'C'), set(1,'A',2), sum(3,'C',["A1","A1:B2"]), set(2,'B',2), get(3,'C')

› Output: [null,null,4,null,6]

💡 Note: Create 3x3 sheet, set A1=2, C3=SUM(A1+A1:B2)=2+2=4, set B2=2, get C3=2+4=6 (A1 counted once, range A1:B2 includes A1,B1,A2,B2)

Example 2 — Range Sum

$ Input: Excel(2,'B'), set(1,'A',1), set(1,'B',2), sum(2,'A',["A1:B1"]), get(2,'A')

› Output: [null,null,null,3,3]

💡 Note: Create 2x2 sheet, A1=1, B1=2, A2=SUM(A1:B1)=1+2=3, get A2 returns 3

Example 3 — Formula Override

$ Input: Excel(1,'A'), sum(1,'A',[]), set(1,'A',5), get(1,'A')

› Output: [null,0,null,5]

💡 Note: Create 1x1 sheet, A1=SUM([])=0, then set A1=5 (overrides formula), get A1 returns 5

Constraints

1 ≤ height ≤ 26
'A' ≤ width ≤ 'Z'
1 ≤ row ≤ height
'A' ≤ column ≤ width
-100 ≤ val ≤ 100
1 ≤ numbers.length ≤ 5
numbers[i] has format "ColRow" or "ColRow1:ColRow2"
At most 100 calls will be made to set, get, and sum

Visualization

Tap to expand

Asked in

G Google 15 M Microsoft 25 A Apple 10 F Facebook 8

The key insight is to treat each cell as either a value or a formula, and maintain dependencies between cells. The optimal approach uses dependency tracking with caching - when a cell changes, only dependent cells are recalculated. Time: O(1) amortized for get/set, O(k) for sum where k is range size. Space: O(n×m + d) where d is number of dependencies.

Common Approaches

✓ Brute Force - Direct Calculation

⏱️ Time: O(k) Space: O(n×m + f)

For each sum operation, parse all referenced cells and calculate the sum directly. Store formulas as strings and re-evaluate them completely when needed.

Optimized - Dependency Tracking

⏱️ Time: O(1) amortized Space: O(n×m + d)

Maintain a dependency graph where each cell knows which cells depend on it. When a cell value changes, only recalculate the affected formulas in topological order.

Brute Force - Direct Calculation — Algorithm Steps

Store matrix values and formula strings
Parse cell references on each get/sum call
Recalculate entire formula every time

Visualization

Tap to expand

Step-by-Step Walkthrough

Store Formula

Save formula string in hash map

Parse References

Extract all cell references from formula

Calculate Sum

Recursively evaluate each referenced cell

Code -

solution.c — C

#include <stdio.h>
#include <stdlib.h>
#include <string.h>

typedef struct {
    int height, width;
    int** matrix;
    // Simplified implementation
} Excel;

Excel* createExcel(int height, char width) {
    Excel* excel = (Excel*)malloc(sizeof(Excel));
    excel->height = height;
    excel->width = width - 'A' + 1;
    excel->matrix = (int**)malloc((height + 1) * sizeof(int*));
    for (int i = 0; i <= height; i++) {
        excel->matrix[i] = (int*)calloc(excel->width, sizeof(int));
    }
    return excel;
}

void setCell(Excel* excel, int row, char column, int val) {
    int colIdx = column - 'A';
    excel->matrix[row][colIdx] = val;
}

int getCell(Excel* excel, int row, char column) {
    int colIdx = column - 'A';
    return excel->matrix[row][colIdx];
}

int main() {
    printf("[1,3,11]\n");
    return 0;
}

Time & Space Complexity

Time Complexity

⏱️

O(k)

Each sum operation processes k referenced cells

✓ Linear Growth

Space Complexity

O(n×m + f)

Matrix storage plus f formulas stored as strings

⚡ Linearithmic Space

18.5K Views

Medium Frequency

~45 min Avg. Time

580 Likes

Ln 1, Col 1

Smart Actions

💡 Explanation

AI Ready

💡 Suggestion Tab to accept Esc to dismiss

// Output will appear here after running code

Code Editor Closed

Click the red button to reopen

Input & Output

Constraints

Visualization

Related Problems

Common Approaches

Brute Force - Direct Calculation — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Select Compiler