I’ve started using Handsontable recently. And I run into issues sometime. I may bug you for some time. Please bear with me.
I want to do a sum of sums in a column. Please help me to achieve it. Following is the URL to my fiddle.
http://jsfiddle.net/RishabhRanjanKesarwani/gpuo6es4/19/
P.S.: I want the sum of sums in the 13th row.
Hi @rishabhbhs
I’m happy that you gave Handsontable a try. I’ll do my best to help.
If the number or rows is stable you can just use a SUM formula. Have you already tried it?
Hi @aleksandra_budnik
I’ve used the SUM formula for the 13th row. SUM is not working on the cells which already have the formula enabled.
Is this example http://jsfiddle.net/h410qcsx/ meet your requirements?
This will work fine when the number of rows will be static. In my use case, neither number of rows nor number of columns will be fixed. I can only provide the the arrays I’ve provided in my fiddle.
There is one more issue in your fiddle: when you filter data, the cells which have SUM formula enabled get hidden automatically. And it is not a desired feature.
A formula cell also is an option to choose when user open a filter window. In the future we should consider an API that will allow developers to choose if any row should get excluded from the list an remain always visible but this is a plan for further future.
If the amount of rows change you can alter the SUM calculation via afterCreateRow and afterRemoveRow hooks. They return an index and amount for rows that has been added or deleted. The same operation should be possible if you allow to move rows.
I didn’t understand what you wanted to say. But I found a workaround for it. I calculate the sum of all the numbers again.
While further exploring, I found that the data in a formula cell should not be used as an input in another formula cell. Otherwise, the succeeding formula cell uses the older value of the preceding formula cell. I wanted to understand the reason why is there such a functionality. Can you tell me how formula cells function and when do they take values?
P.S.:
In the following fiddle, the A14 has a custom function which calculates (value at A13)/10.
Analyse the value by changing values in other cells. You’ll notice that:
(value at A14) = (older value at A13)/10
http://jsfiddle.net/RishabhRanjanKesarwani/quxnmb04/40/
P.S.: This is what I am trying to say in the last post.
Oh, I see. Thank you for an update.
I am happy that you have found a way to achieve the goal.
Did you go through that fiddle? I want to know how does handsontable populate its data and at what point of time are the formulae (if any) executed?
It depends on the amount of data, the code runs asynchronously. That it why you may end up with a formula being called and done too quick. Then you can call instance.render() or recall the same formula via setDataAtCell.
Okay. Thanks!
In the above fiddle, I want to add the values present in the even rows only. How can I achieve it?
Can you create a draft? The A2 and A6 are also SUM.
If you do not refer to a contiguous range of cell you need to do a normal sum like A1+A3+A5..
There’s another way to achieve that. I found it while experimenting.
We can mention it as ‘=SUM(A1, C1:E1)’
It will be equal to A1+C1+D1+E1
You can pass multiple arguments, seperated by comma, to SUM function and It’ll add all of them.
I got into another problem. When I change value in a cell, values in the formula cells change accordingly. But, the ‘afterChange’ function returns an array only with the details of the cell I had changed. it doesn’t return the details of the other cells which were automatically changed. I need to capture all the cells which have changed. Please help.
Refer to the following fiddle: http://jsfiddle.net/RishabhRanjanKesarwani/k4cdp9b3/32/
P.S.: I have logged the array in the ‘afterChange’ function
Hi @rishabhbhs
the afterChange return data that is changed, and formula result doesn’t change - only it displays other value. Change has to occur for the value and in formula case it occur for the renderer.
If you collect the coordinates of changed cells you’d need to pass the formula coordinates manually.
Okay. Got it. Is there any other way or if I can help building it. Please tell me if there is.
The first thing that comes to my mind is to store coordinates of formulas and their range. For example http://jsfiddle.net/1xt6zw04/1/
Let’s add an array to hold changes,
var arr = []
Setting up the variables
Formulas are on those cells
var formulaField = [
{row: 2, col: 4},
{row: 2, col: 5},
{row: 4, col: 1},
{row: 4, col: 3}
]
And the ranges of the formulas are
var formulaRanges = [
{row: 0, col: 1, formulasAttached: [formulaField[1]]},
{row: 1, col: 0, formulasAttached: [formulaField[2], formulaField[3]]},
{row: 1, col: 1, formulasAttached: [formulaField[3]]},
{row: 2, col: 0, formulasAttached: [formulaField[2]]},
{row: 3, col: 0, formulasAttached: [formulaField[0], formulaField[2]]},
{row: 4, col: 0, formulasAttached: [formulaField[1]]
]
Adding changes with formulas to the arr variable
So, when you get a message from afterChange that cell 1,1 has been changed, you look for the row: 1, col: 1 in the formulaRanges array and push corresponding changes from formulasAttached array. In this case only one formula holds this coordinates.
so you can add it to the arr array.
arr.push({row: formulaRanges[2].row, col: formulaRanges[2].col})
Okay. Thanks!
