# Math calculation between rows

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

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.
1 Like

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

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.

2 Likes

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.

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.

3 Likes