Forums Home / Spread / SpreadJS
Posted by: cangod05 on 2 February 2023, 9:48 pm EST
cangod05
Posted 2 February 2023, 9:48 pm EST
Hi admin,
Currently I had a problem with formula when I set many items in formula such as: SUM(A1, A2, A3, … A1000) will throw an error (Invalid formula).
How can I solve the problem or solution for that.
Best regards.
avinash.pathak
Posted 3 February 2023, 6:08 am EST
Hello,
It seems you want to sum the values of cells from A1, A2 to A1000. For that you are using =SUM(A1, A2, …A1000) formula. Please note that the maximum number of arguments the “SUM” function can is 255. Since you are providing more than 255 arguments, it throws an error. You can get the SUM function using GC.Spread.CalcEngine.findGlobalFunction(‘SUM’) method and check its maxArgs property which shows the maximum number of parameter a function can take.
Instead of providing reference of each individual cell in the SUM function arguments, you can simply specify the cell range A1:A1000, i.e. =SUM(A1:A1000). In the arguments of SUM function, you can provide individual cell reference as well as reference to a cell range(e.g. A1:A1000).
Please refer to the code snippet and attached sample which explains the same.
// sets the formula to cell range A1:A1000
sheet.setFormula(2, 3, “=SUM(A1:A1000)”);
Sample: https://codesandbox.io/s/solution-forked-thv6nn?file=/src/index.js
Please let us know if you still face any problem.
Doc reference
cell references in formula: https://www.grapecity.com/spreadjs/docs/formulareference/formulaoverview/cellreferences
GC.Spread.CalcEngine.findGlobalFunction(): https://www.grapecity.com/spreadjs/api/modules/GC.Spread.CalcEngine.Functions#findglobalfunction
cangod05
Posted 5 February 2023, 10:42 pm EST
Hi admin,
But some cases formula like : A1, A3, A10, … with 1000 → 10000 record. It’s impossible to set formula with max argurments. Do you have any params or functions for deleting maximum arguments. Or solution like that.
Best regards.
cangod05
Posted 5 February 2023, 10:42 pm EST
Hi admin,
But some cases formula like : A1, A3, A10, … with 1000 → 10000 record. It’s impossible to set formula with max argurments. Do you have any params or functions for deleting maximum arguments. Or solution like that.
Best regards.
ankit.kumar
Posted 6 February 2023, 1:29 pm EST - Updated 6 February 2023, 1:34 pm EST
Hello Can,
The SUM function in SpreadJS (same as Microsoft Excel) only allows for up to 255 arguments. Instead of referencing individual cells, you can use a range to reference multiple cells.
In the example below, cells A1, A2, A3,…A10 can be referenced using the range A1:A10. This range format (A1:A10) represents a group of cells.
For your use case, you can represent the cells from A1, A2,…,A1000 with the range A1:A1000. You can use the SUM function as “=SUM(A1:A1000)”.
Note that the format A1:A10 is relative. If you want to use absolute references, add the “$” symbol, such as “$A$1:$A$10”.
Please see the attached sample for further clarification.
Sample: https://codesandbox.io/s/solution-forked-r4ig5l?file=/src/index.js
Additionally, you could create your own custom function to suit your needs. The following link provides a demo on how to create a custom function in SpreadJS: https://www.grapecity.com/spreadjs/demos/features/calculation/custom-functions/purejs
References:
cell reference notations: https://www.grapecity.com/spreadjs/docs/formulareference/formulaoverview/cellreferences#a1-notation
relative and absolute references: https://www.grapecity.com/spreadjs/docs/formulareference/formulaoverview/cellreferences#relative-and-absolute
Regards,
Ankit
william.chang
Posted 16 February 2023, 12:25 am EST
Hi all,
I have a solution by adding a helper column and using SUMPRODUCT or SUM+IF function.
E.g. =SUMPRODUCT(A:A,B:B) or =SUM(IF(B:B=1,A:A,0)) result is sum of A1, A3,A10 when B1, B3, B10 values are 1.
Regards,
William
