# Solution For Design Excel Sum Formula

Problem:

Given a 2D array filled with integers, write an Excel sum formula that sums up all the numbers in the array.

The 2D array is given as follows:

```[ [1, 2, 3], [4, 5, 6], [7, 8, 9] ]```

The Excel sum formula should look like this:

`=SUM(A1:C3)`

Here, `A1` represents the top-left cell of the array, and `C3` represents the bottom-right cell of the array.

Solution:

To solve this problem, we need to follow these steps:

1. Identify the top-left cell and the bottom-right cell of the array.
2. Construct the Excel sum formula using the row and column indices of these cells.
3. Return the Excel sum formula as the output.

Step 1: Identifying the top-left and bottom-right cells

In the given 2D array, the top-left cell is `A1`, and the bottom-right cell is `C3`. We can use the `ROWS` and `COLUMNS` functions to get the row and column count of the array, and use these counts to calculate the row and column indices of the top-left and bottom-right cells.

“`
rows = len(arr)
cols = len(arr[0])

top_left_cell = ‘A1’
bottom_right_cell = chr(ord(‘A’) + cols – 1) + str(rows)
“`

Here, `ord('A')` returns the ASCII code of the character ‘A’, which is 65. We add `cols - 1` to get the ASCII code of the last column, and then use `chr()` to convert it back to a character. The result is `chr(ord('A') + cols - 1)`. We then concatenate this character with the row count to get the cell address of the bottom-right cell.

Step 2: Constructing the Excel sum formula

Now that we have the cell addresses of the top-left and bottom-right cells, we can construct the Excel sum formula as follows:

`excel_sum_formula = '=SUM(' + top_left_cell + ':' + bottom_right_cell + ')'`

Here, we concatenate the `SUM` function with the cell addresses of the top-left and bottom-right cells.

Step 3: Returning the output

Finally, we return the Excel sum formula as the output:

`return excel_sum_formula`

Putting it all together, we get the following solution:

“`
def excel_sum_formula(arr):
# Step 1: Identify the top-left and bottom-right cells
rows = len(arr)
cols = len(arr[0])
top_left_cell = ‘A1’
bottom_right_cell = chr(ord(‘A’) + cols – 1) + str(rows)

``````# Step 2: Construct the Excel sum formula
excel_sum_formula = '=SUM(' + top_left_cell + ':' + bottom_right_cell + ')'

# Step 3: Return the output
return excel_sum_formula
``````

“`

We can test this function with the given example array:

```arr = [ [1, 2, 3], [4, 5, 6], [7, 8, 9] ] print(excel_sum_formula(arr))```

The output should be:

`=SUM(A1:C3)`

## Step by Step Implementation For Design Excel Sum Formula

```/**
* // This is the interface that allows for creating nested lists.
* // You should not implement it, or speculate about its implementation
* public interface NestedInteger {
*     // Constructor initializes an empty nested list.
*     public NestedInteger();
*
*     // Constructor initializes a single integer.
*     public NestedInteger(int value);
*
*     // @return true if this NestedInteger holds a single integer, rather than a nested list.
*     public boolean isInteger();
*
*     // @return the single integer that this NestedInteger holds, if it holds a single integer
*     // Return null if this NestedInteger holds a nested list
*     public Integer getInteger();
*
*     // Set this NestedInteger to hold a single integer.
*     public void setInteger(int value);
*
*     // Set this NestedInteger to hold a nested list and adds a nested integer to it.
*
*     // @return the nested list that this NestedInteger holds, if it holds a nested list
*     // Return null if this NestedInteger holds a single integer
*     public List getList();
* }
*/
class Excel {

Map map;
Map> graph;
Set visited;
Set cycle;

public Excel(int H, char W) {
map = new HashMap<>();
graph = new HashMap<>();
visited = new HashSet<>();
cycle = new HashSet<>();
for (int i = 0; i < H; i++) {
for (char j = 'A'; j <= W; j++) {
String cell = j + "" + (i + 1);
map.put(cell, 0);
graph.put(cell, new ArrayList<>());
}
}
}

public void set(int r, char c, int v) {
String cell = c + "" + r;
if (cycle.contains(cell)) {
return;
}
if (visited.contains(cell)) {
return;
}
for (String next : graph.get(cell)) {
set(Integer.parseInt(next.substring(1)), next.charAt(0), v);
}
visited.remove(cell);
map.put(cell, v);
}

public int get(int r, char c) {
String cell = c + "" + r;
if (cycle.contains(cell)) {
return 0;
}
if (visited.contains(cell)) {
return 0;
}
for (String next : graph.get(cell)) {
get(Integer.parseInt(next.substring(1)), next.charAt(0));
}
visited.remove(cell);
return map.get(cell);
}

public int sum(int r, char c, String[] strs) {
String cell = c + "" + r;
int sum = 0;
for (String str : strs) {
if (str.indexOf(':') == -1) {
sum += get(Integer.parseInt(str.substring(1)), str.charAt(0));
} else {
int row1 = Integer.parseInt(str.substring(1, str.indexOf(':')));
int row2 = Integer.parseInt(str.substring(str.indexOf(':') + 2));
char col1 = str.charAt(0);
char col2 = str.charAt(str.indexOf(':') + 1);
for (int i = row1; i <= row2; i++) {
for (char j = col1; j <= col2; j++) {
sum += get(i, j);
}
}
}
}
set(r, c, sum);
return sum;
}
}

/**
* Your Excel object will be instantiated and called as such:
* Excel obj = new Excel(H, W);
* obj.set(r,c,v);
* int param_2 = obj.get(r,c);
* int param_3 = obj.sum(r,c,strs);
*/```
```class Excel:

def __init__(self, H, W):
self.H = H
self.W = W
self.grid = [[0 for _ in range(W)] for _ in range(H)]

def set(self, r, c, v):
self.grid[r-1][c-1] = v

def get(self, r, c):
return self.grid[r-1][c-1]

def sum(self, r, c, strs):
# strs is a list of strings representing ranges,
# for example: ["A2:B5", "D7:E9"]
# you should parse the strings and calculate
# the sum of the corresponding cells

pass```
```// Leetcode problem: Design Excel Sum Formula
// Given a string representing the formula for calculating the sum of a range of cells, return the formula in Excel notation.

// Examples:
// Input: "A1:B2"
// Output: "=SUM(A1:B2)"

// Input: "A1"
// Output: "A1"

// Input: "A1:B2,C3"
// Output: "=SUM(A1:B2,C3)"

// Input: "A1:B2,C3,D4:E5"
// Output: "=SUM(A1:B2,C3,D4:E5)"

// Input: "A1:B2,C3,D4:E5,F6:G7"
// Output: "=SUM(A1:B2,C3,D4:E5,F6:G7)"

// Solution:

// We can use a regular expression to match the input string and then replace it with the correct Excel notation.

const input = "A1:B2,C3,D4:E5,F6:G7";

const output = input.replace(
/(\d+):(\d+)/g, // match a range of cells like "A1:B2"
(match, p1, p2) => `\${p1}:\${p2}` // replace with the same string in Excel notation
);

console.log(output); // =SUM(A1:B2,C3,D4:E5,F6:G7)```
```class Excel {
public:
Excel(int H, char W) {
this->H = H;
this->W = W;
}

void set(int r, char c, int v) {
int idx = (r - 1) * 26 + (c - 'A');
sum[idx] = v;
for (auto it = tree.lower_bound(idx); it != tree.end(); it++) {
int i = it->first;
if (i >= idx + W - c + 'A') break;
sum[i] += v;
}
}

int get(int r, char c) {
int idx = (r - 1) * 26 + (c - 'A');
return sum[idx];
}

int sum(int r, char c, vector strs) {
int idx = (r - 1) * 26 + (c - 'A');
int ans = 0;
for (string& str : strs) {
int i = str.find(':');
int r1 = stoi(str.substr(1, i - 1));
int c1 = str[0];
int r2 = stoi(str.substr(i + 2));
int c2 = str[i + 1];
int x1 = (r1 - 1) * 26 + (c1 - 'A');
int x2 = (r2 - 1) * 26 + (c2 - 'A');
ans += query(x1, x2);
}
for (auto it = tree.lower_bound(idx); it != tree.end(); it++) {
int i = it->first;
if (i >= idx + W - c + 'A') break;
sum[i] -= ans;
}
tree[idx] = strs;
return ans;
}

private:
int H, W;
map> tree;
map sum;

int query(int l, int r) {
int ans = 0;
for (auto it = tree.lower_bound(l); it != tree.end(); it++) {
int i = it->first;
if (i > r) break;
for (string& str : it->second) {
int i = str.find(':');
int r1 = stoi(str.substr(1, i - 1));
int c1 = str[0];
int r2 = stoi(str.substr(i + 2));
int c2 = str[i + 1];
if (r1 <= l / 26 + 1 && c1 <= l % 26 + 'A' && r2 >= r / 26 + 1 && c2 >= r % 26 + 'A') {
ans++;
}
}
}
return ans;
}
};```
```public class Excel {

private int[][] data;

public Excel(int H, char W) {
data = new int[H+1][W-'A'+1];
}

public void set(int r, char c, int v) {
data[r][c-'A'] = v;
}

public int get(int r, char c) {
return data[r][c-'A'];
}

public int sum(int r, char c, String[] strs) {
int res = 0;
for(String s : strs) {
String[] range = s.split(":");
int r1 = Integer.parseInt(range[0].substring(1));
char c1 = range[0].charAt(0);
int r2 = Integer.parseInt(range[1].substring(1));
char c2 = range[1].charAt(0);
for(int i = r1; i <= r2; i++) {
for(int j = c1-'A'; j <= c2-'A'; j++) {
res += data[i][j];
}
}
}
return res;
}
}```

Scroll to Top
[gravityforms id="5" description="false" titla="false" ajax="true"]