Subtraction
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?
Answers

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

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

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.

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!

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.

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

This is what my full sheet looks like

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

How do I find this?

any word on this?

You can find the window with the formula spelled out by doubleclicking 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())

@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.

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!

hey, I appreciate both of your help, but this is still not working for me.
I keep getting the UNPARSEABLE error message

Are you able to provide a screen capture of the sheet, and the formula open like Paul has above?
Help Article Resources
Categories
Check out the Formula Handbook template!