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:
- Identify the top-left cell and the bottom-right cell of the array.
- Construct the Excel sum formula using the row and column indices of these cells.
- 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.
* public void add(NestedInteger ni);
*
* // @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)) {
cycle.addAll(visited);
return;
}
visited.add(cell);
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)) {
cycle.addAll(visited);
return 0;
}
visited.add(cell);
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);
graph.get(cell).addAll(Arrays.asList(strs));
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
# YOUR CODE HERE
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;
}
}