Math calculation between rows

[Bander]

Hi there

So I am trying to build a chart which shows the net value of income/expense data. How do I create a column which is able to use data from separate rows to calculate against each other?

This is what my sheet looks like currently and I want to use the two values in the Totals column to create a net value in a column that can be referred to by my chart.

Thank you

[Darren_Murphy]

The calculation is Income - Expenses?

Given what you have there, maybe the simplest way is to use that Joined List column with a JavaScript column. You can use the following code:

const income = Number(p1.split(', ')[0]);
const expenses = Number(p1.split(', ')[1]);
return (income - expenses);

And use your Joined List column as a replacement for p1 in the JavaScript column.

---

If you prefer a solution that doesn't use JavaScript, then an alternative would be to create two Single Value columns that target your Totals column. One should take the first value, and the other should take the last. And then use a Math column to subtract one from the other.

[Bander]

Very good, I liked the second solution. Wish the If formulas had the same capabilities as Excel/Sheets. Thanks Darren.

[Darren_Murphy]

The if-then-else column is a lot more powerful than you might think. It sometimes just requires a little bit of creative thinking to get what you need. In fact, you’ll find that there is very little that can be done with Excel formulas that can’t be done with Glide computed columns. I recommend the below tutorial.

Trash your Excel Formulas

Hey Gliders! By request, I created a video that explains how to replace 14 commonly used Google Sheet/Excel Formulas using Glide computed columns and other native Glide functionality. Formulas are timestamped in the description. For good measure, I also threw in how to use the Excel Formula plugin column Enjoy! [Glide: Replace 14 Excel Formulas with Glide Computed Columns]

[Bander]

I had watched that video but I still find the IF formulas to be lacking. For example I want to do another IF formula in the “else” response.

Or that I can’t just enter formulas in cells for example =SUM(A1:C5) but have to do a new column for every component of a formula.

[Darren_Murphy]

@Bander

I had watched that video but I still find the IF formulas to be lacking. For example I want to do another IF formula in the “else” response.

Next time you hit one of those cases, share it here and we’ll see if we can help with a way.
When you have multiple conditions or need to do a combination of AND/OR, generally the way is to work backwards and eliminate all the failing conditions first. One important thing to be aware of is that the if-then-else column will return as soon as it meets a matching condition, and you can take advantage of that.

For example, let’s say you want to express the following logic in an if-then-else column:

• If A is true AND (B > 10 OR C > 10), then true

At first glance you might think the above isn’t possible, but it’s actually quite simple. It’s just a matter of reversing the test for A, and short circuiting the flow if it doesn’t pass. So…

• If A is not checked, then null
• If B is greater than 10, then true
• If C is greater than 10, then true

Which gives:

The above approach won’t work for all cases, but it will work for many.
All that said - yes, it could be better. And you’re not the first one to have mentioned that.

Better and more consistent IF THEN logic

I’ve spent the past few days trying to work out some IF/THEN logic for a side project. The problem is that this is something I could have worked out in a matter of a few minutes with actual code. I’m running into several limitations with the current IF/THEN logic we currently have in Glide. First of all there inconsistencies between what’s possible with an IF/THEN column, and what’s possible with If/Then logic in visibility conditions or actions. Most importantly, an IF/THEN column only all…