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 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())
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
@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!
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
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?
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!