Hi
I have a problem with an array formula, where the amount in the “SALDO” column is not true.
I want the correct amount as in the “SALDO YANG BENAR” column
the formula I use in the “SALDO” column is =ArrayFormula(IF(J3:J="";"";M2+K3:K-L3:L))
the formula I use in the column “SALDO YANG BENAR” is =N2+K3-L3
Is there a solution for the problem I’m facing?
I attached a screenshot
Shouldn’t you be using M2:M instead of M2?
thank you for your response.I’ve tried it, but when I try it, I get a pop up “Interdependence is detected. To finish with iterative calculations, see File > Spreadsheet Settings.”
I attached a screenshot…
Shouldn’t it be…
=arrayformula(if(J3:J="",,M2:M+K3:K-L3:L))
I guess the semicolons are because you’re in a different country? But, just wondering why you have another set of double quotes. I always put double commas after the one set of double quotes.
Arrayformulas in google sheets get complicated when you start referring to values in different rows. I’ve done it before, but you need to add some extra checks to make sure you are not referring to a row that does not exist at the beginning or end of your data. Personally I would approach this much differently and just build all logic in Glide. For something like this, I would either use rollups to sum credits and debits and use a math column to get a final total, or for each new transaction, I would get the last balance prior to adding the new transaction, and pass that value through the form.
Do you really need a running balance on each row? Would you ever edit an existing transaction?
@Jeff_Hager
I have made a rollup to get the final amount of each transaction made, how do I pass the final value through the form and print it on my google sheet…?
You should be able to do that using a screen column in your form, write that to the column you want in the destination sheet.
@ThinhDinh
I named the final sum result in Glide with the name “SUM SALDO”
but i don’t find the screen column with the name “SUM SALDO”
how do i add to the form?
screen column 
If your form is located on the profile, then you will need that rollup and sum on the profile table so you can pull it into the form. The screen values components in a form come from whichever table is the source of the tab or details screen that contains the form button.
@Jeff_Hager
because my rollup data is in “JURNAL” I tried to create a new form and put it in “JURNAL”, but I still can’t find it…
You need to add a component on the left hand side. Your column will be one of the available components.
@Jeff_Hager
Is it like this?
I’ve tried it, but the sum is not what I expected,
the final result should be IDR 5,483,900 + IDR 1,000,000 = IDR 6,483,900
but the data printed on my google sheet is IDR 5,483,900
First of all, I would think you would want to only pass the previous sum through the form. It should be passed to a ‘previous sum’ column. You shouldn’t be passing the rollup columns because you are only using them to calculate the sum from all entries prior to the form submit, and besides, it appears that you are requiring entry of a debit or credit…so there is no reason to pass any debits or credits from the rollup columns through the form.
You would still need to do some math to add or subtract the new credit or debit from the previous sum to get the new sum. It’s getting confusing because you want the final sum to be in the sheet. I would just calculate it in glide, but I’m not sure if there is a reason that you need that total in the sheet instead bof a glide computed column.
Just to be clear, a form will not perform any math. The math can only happen once the data is written to a table or a sheet.
Well…
Thanks for all the help…
