babyjwhale
New Member
- Joined
- Nov 6, 2024
- Messages
- 6
- Office Version
- 365
- Platform
- Windows
I have tried a number of formulas to get this, and I keep getting circular references. My use case is a construction project budget, where I have a hierarchy of categories. In a single table, I want to make the inputs AND see the rollup (I am familiar with PivotTables, and they do not fit this use because I need the rollup in the same table as the inputs). I want this to be an actual Table so that the automatic formulas populate when I add rows, as the individual project estimator may add unique detail rows to create the estimate.
Two examples:
1) Contractors charge a % fee on cost for their work. The % fee will be a top line item above their costs. As you'll see in the sheet, I reference their rolled up cost cell and multiply by a % to get the calculated value. This use works because this is a topline item referencing cells BELOW it that would cause a circular reference. I desire to do this with a bottom line item as described below.
2) After figuring all costs on the project, we apply a contingency factor based on the cost. Most often, this too is a % calculation. We show contingency at the bottom of the estimate, not at the top, so ideally this stays at bottom of my sheet. The issue I'm having is referencing a cell ABOVE this, though I'm using FILTER to cut the items that don't apply. I have tried using SUMIFS and SUMPRODUCT as well.
I intend to use LET and only show helper fields (next line level, kids check, and level check) to help visualize.
Look at the attached sheet and let me know if you have any ideas or solutions.
Using Excel 365 and ok using new formulas only available to subscribers.
Test File.xlsx is available for download *First post; forgive if it's preferred to use XL2BB instead of full files.
Two examples:
1) Contractors charge a % fee on cost for their work. The % fee will be a top line item above their costs. As you'll see in the sheet, I reference their rolled up cost cell and multiply by a % to get the calculated value. This use works because this is a topline item referencing cells BELOW it that would cause a circular reference. I desire to do this with a bottom line item as described below.
2) After figuring all costs on the project, we apply a contingency factor based on the cost. Most often, this too is a % calculation. We show contingency at the bottom of the estimate, not at the top, so ideally this stays at bottom of my sheet. The issue I'm having is referencing a cell ABOVE this, though I'm using FILTER to cut the items that don't apply. I have tried using SUMIFS and SUMPRODUCT as well.
I intend to use LET and only show helper fields (next line level, kids check, and level check) to help visualize.
Look at the attached sheet and let me know if you have any ideas or solutions.
Using Excel 365 and ok using new formulas only available to subscribers.
Test File.xlsx is available for download *First post; forgive if it's preferred to use XL2BB instead of full files.
