Income - Expenses - Balance calculations

1

Hello! I am quite new to CODA so it might be a very simple question.

I have created several tables for incomes and expenses. At the end I managed to make a sum for the columns I need to accumulate. Now I would like to create a table where I can bring all the sums together and then make calculations with them.

E.g.
Income 1 = The sum of the Income 1 table / Total fees row
Income 2 = The sum of the Income 2 table / Total amount row
Expenses 1 = The sum of the Expenses 1 table / Total costs row

and then I want Income 1 + Income 2 - Expenses 1 = Balance

What is the simplest way to use the Sums from the bottom of the tables for calculations? I can’t seem to find how to refer to them.

Thanks a lot in advance!

Tamas

2

i think the formula you need is like this

Income 1 Table.Fees.Sum()

this formula collects all the ‘Fees’ values from Income 1 Table and sums them to yield a single result

a similar formula for the other values

this generates a sum for ALL the rows in the table
if you need to sum only certain rows, then you will need to use a Filter() as well

if that is the case, let us know and i can show how that works as well

max

3

Thank you very much! It works in a way, but still I’m looking for a better option.

Is there any ways to have different formulas I’m each row of a column?

E.g. the total amount of the row or other way around, the total amount in the last row of the items of a column. (I know I can display the sum but it’s not good for me)

Or is it possible just to “draw” a table where I can add my own formulas different in each row/cells?

In general I’m looking for a good solution for making calculations with different sums of expenses, incomes, tracking budgets. This kind of things.

Thanks a lot!

4

Hi @Tamas_Mahner :blush: !

As far as I know, there’s no such thing as a “cell formula” in Coda (contrary to what’s feasible in Sheets or Excel) :blush: .

You could have a conditional formula (e.g.: a SwitchIf()) which could calculate a sum based on “something” in some rows …

For example:
This is the formula in my column called Sum
(See the sample doc below :blush: )

SwitchIf(
  thisRow.Number <= 5,
    [Money Table].Filter(Number <= 5).Money.Sum(),
  thisRow.Number = 6,
    [Money Table].Filter(Number = 6).Money.Sum(),
  thisRow.Number > 6,
    [Money Table].Filter(Number > 6).Money.Sum()
)

It sums the values in the column Money based on the value in the column Number :blush: .

If the value of thisRow.Number <= 5:

It takes the [Money Table], looks through the whole column Number to find the rows where CurrentValue.Number <= 5, access the values in Money for those rows and sums them.

It then does the same for thisRow.Number = 6 and thisRow.Number > 6

Something similar could be done in your doc (not for each and every row though) but without knowing more about it, it’s a little hard to suggest you an appropriate solution or paths you could explore :innocent: .

Could you share here, with us, an anonymised sample doc of your actual setup ? (i.e.: what you’ve already build, what you have in mind for this doc…) :blush:

5

Thank you so much for this insight!

Basically what I am looking for is the simplest solution to create an overview table of different sums from different tables and then do math with them.

Project the sum of expenses of project 1-2-3-4 and accumulate them. So in this way each row should be different.

But, I think the best is if I just embed a GoogleSheet and work with that one inside Coda. Especially because the different tables are getting the data copy-pasting from an excel file sent by the accountant. And pasting to CODA is not so great. And creating a specific import formula where I can match columns of excel and CODA tables I am not sure if possible.

Another solution could be to use simple formulas and just put it in a “grid” but I am also not sure if it is possible to do in CODA just kind of drawing a table and put different formulas in the cells. Is it even possible to divide a row within CODA? Like adding text or tables next to each other within the same row?

6

You can place your formulas at the canvas, just below your table (or anywhere you like, out of a table).
Just type:
Income 1: =Table1.Fees.Sum() / Table1.Fees.Count()
Income 2: = Table2.Fees.Sum() / Table2.Fees.Count()
…
You can write anything you want at the left of the = sign. When you type the = sign, the formula editor is invoked. The text after the = sign is a formula. Besides, each formula can be named, so you can use it in other formulas or even in tables.

7

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.