Subtraction

Gabriella Silvagnoli · 02/24/21

Hi I have a total budget at the top of my column that I am tryin to periodically subtract from, every time I incur an expense.

Have been doing -= and entering each row click by click

Is there an easier formula to do this and that automatically subtracts every time I add in a new row?

Paul Newcome · 02/24/21

Are you able to provide a screenshot of how your data is laid out?

Gabriella Silvagnoli · 02/24/21

2021-02-24 15_28_36-Video Expenses and Budget Tracker 2021 - Smartsheet.com.png

Yes, please see attached. I am trying to automate this so that every time I add an expense to this column, it will subtract it from the 500k in bold at the top and give me a total at the bottom

Paul Newcome · 02/24/21

Are you rows indented underneath of that row? If so, you should be able to use something along the lines of...

=500000 - CHILDREN()

If they are not indented, you should be able to use a single circular reference within a column as long as you reference the entire column.

=500000 - SUM([Corporate Cost]:[Corporate Cost])

It should ignore the cell that the formula is in and pull all other values into the SUM function.

Gabriella Silvagnoli · 02/24/21

It is indented, so I willput =500000 - CHILDREN()

But what goes in the parenthesis? every cost in the column?

is there a way to automate this, I am constantly adding in additional fees to the bottom of the row

Thanks!

Paul Newcome · 02/24/21

Nothing goes in the parenthesis in this case. Leaving the parenthesis empty in the CHILDREN function indicates to use the CHILDREN of the cell the formula is in. AS long as the new rows being added are also indented underneath of this row, they will automatically be included.

Gabriella Silvagnoli · 02/24/21

Hmm not sure what I am doing wrong I get the error message UNPARSEABLE.

Please note that all the columns in this row are indented under the same indent header, but this is not the main indent column

Gabriella Silvagnoli · 02/24/21

This is what my full sheet looks like

Paul Newcome · 02/24/21

Can you provide a screenshot of the formula within the sheet similar to the screenshot below?

Gabriella Silvagnoli · 02/24/21

How do I find this?

Gabriella Silvagnoli · 02/24/21

any word on this?

Genevieve P. · 02/25/21

https://community.smartsheet.com/discussion/comment/281025#Comment_281025

Hi @Gabriella Silvagnoli

You can find the window with the formula spelled out by double-clicking on the cell that has the formula... the one that's currently returning an error.

You could also try wrapping the SUM function around the CHILDREN function:

=500000 - SUM(CHILDREN())

Paul Newcome · 02/25/21

@Genevieve P Thank you. Apparently I was trying to do too many things at once.

@Gabriella Silvagnoli Genevieve is correct. There should be the SUM function wrapped around the CHILDREN function.

Genevieve P. · 02/25/21

Haha I was wondering if there was a new way to reference Children rows that I was missing... thanks for verifying! 🙂

@Gabriella Silvagnoli Let us know if this works for you!

Gabriella Silvagnoli · 02/25/21

hey, I appreciate both of your help, but this is still not working for me.

I keep getting the UNPARSEABLE error message

Genevieve P. · 02/25/21

Hi @Gabriella Silvagnoli

Are you able to provide a screen capture of the sheet, and the formula open like Paul has above?

Subtraction

Answers

Help Article Resources

Categories