Formula for rows

Tags: #<Tag:0x00007efc65608a70>
#1

Hi @aleksandra_budnik

I want the same formula to be applied for each and every row.
data: [
[“item1”, “fun1”, “fm1”,“eff1”,2,“cau1”,4,“prev1”,“det1”, “=E1* G1*I1”],
],
If there are infinite rows, how do i get this formula work for all the rows?

#2

Hey @rashmiravi29

If you want to get “=E1* G1*I1” in all the cells in the last column you can populate them like here https://handsontable.com/docs/7.1.1/demo-pre-populating.html

#3

@aleksandra_budnik i want it for the each and every row of the last column.

#4

You can use the sample example but here

 if (args[5] === null && isEmptyRow(instance, row)) {
    args[5] = tpl[col];
    td.style.color = '#999';
  }

you’d need to pass an array tpl = ['one', 'two', 'three'],
as something more like this

[null, null, '=E1* G1*I1']

where the number of null's is the number of columns in the table - 1. You can use https://handsontable.com/docs/7.1.1/Core.html#countCols

#5

@aleksandra_budnik Heyy, I am getting '#VALUE! ’ in the place of the result. How can i remove this?

#6

Can you share the curret progress via JSFiddle? It’s hard to tell what causes the issue without debugging.

#7

@aleksandra_budnik

Below is the JSfiddle

https://jsfiddle.net/4hf73zke/

Thank you

#8

I’m sorry but I guess that formulas added via populating do not work https://jsfiddle.net/handsoncode/f3rux67d/

You may want to add them via setDataAtCell instead.

#9

@aleksandra_budnik Can i assign formulas in setDataAtCell ? Can you please help me with an example.

#10

Sure, here’s a basic example https://jsfiddle.net/handsoncode/jg4a3pcw/

#11

@aleksandra_budnik In the example, the value of A1 is considered for all other rows to be calculated. Can’t we consider the value of that particular row to be calculated while inserting a new row.
Ex: B1+2 for second row, C1+2 for third row…

#12

Yes, the formula is only a string here so you can modify it. Here https://jsfiddle.net/handsoncode/wh2egd0a/ is an updated example

#13

Thank you for the reply @aleksandra_budnik .
I want to increment the column A, B and C, without interval . Is the below code correct.
var a = 1, b =1, c=1;
computeFormula()
{
hot.setDataAtCell(1, 2, ‘=A* B* C’ + a + b + c +) ;
a++;
b++;
c++;
},

#14

The formulas won’t understand the =A* B* C.
It would be very intuitive to pass A * B but at this point, the plugin cannot translate it. We need to pass a coordinate, like A1, A2, A3. And if you want to use variables then
='A' + a + '*B' + b + '*C' + c
Where a, b, c are digits.

ps. If you’re not sure if the formula is correct it is recommended to log() it.

#15

Hey @rashmiravi29

Have you tried to rewrite the formula? If so, did that work?

#16

@aleksandra_budnik yes i tried to fix it. But its not working as the way i wanted it. I wanted the formula to be applied for every row of a column.

#17

Have you tried the last proposed pattern?

if you want to use variables then
='A' + a + '*B' + b + '*C' + c
Where a, b, c are digits.

#18

@aleksandra_budnik i tried it, but the problem here is the row number has to be changed every time. The example which you shared shows the rows incrementing when the insertion of new row happens automatically.

#19

Can you share a detailed example of use? We’ll be able to adjust the calculations.

#20

@aleksandra_budnik If suppose i have a formula like a1 * b1 * c1 and this must be incremented for each row like a2 * b2 * c2…and so on without inserting a new row. Can we make this work for the entire column?