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.
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.
First post; I don't see that my attachment loaded, so I'm trying again. Forgive duplication.
I don't think there is a way to avoid circular references here as you attempt to calculate a contingency as a percentage of a total that, in turn, should include that contingency.
furthermore, you are 'abusing' the purpose of structured tables by introducing totals and sub-totals and empty columns.
Are you sure a pivot table can't deal with the issue? The blog post in the link below describes some advance techniques that you may not be aware of. Obviously, you would need to restructure the raw data for the report.
https://www.myonlinetraininghub.com/advanced-excel-pivottable-techniques
Thank you, and I expect this may be the case. However, the Contingency lines should not be circular, in that they do not calculate off of themselves. Contingency, Acquisition only references the Acquisitions total. The Filter helper columns show what the Contingency, Acquisition line calculates off of. The circular reference is coming in through the Filter "looking" through all of the values below it, I think.
Agree this is not the intended use of a table and that I'm modifying to fit my desired use. I desire to keep the totals/subtotals/inputs all in one because it's much more practical for the estimator to see the impact of inputs on the totals all at once, rather than having individual lines that sum to a pivot in another location. I'd try to work around this with a Pivot Table, but given the multi-tier hierarchy, I believe I'd have to use PowerPivot to combine this table with a hierarchy table. This would require a manual refresh to see the results, which is not practical in this use.
Alternatively, I would try the PivotBy functions (not familiar with these), but my expectation is that I'd still have two tables: data input and hierarchy, and that my data input table would have several XLOOKUPs in helper columns to get the structure correct without using PowerPivot. This is not ideal because the hierarchy table is not a completely standard hierarchy... Everything rolls up to a certain structure, but the estimator may desire to add "unique" lines beneath a standard rollup. I don't want the estimator to have to go input that unique line into the hierarchy table first to be able to make the input in the data input table. I hope that makes sense.
It is possible that a UDF could get the desired result. If using a UDF is acceptable by you then let me know and I will create one for you.
"However, the Contingency lines should not be circular, in that they do not calculate off of themselves. Contingency, Acquisition only references the Acquisitions total."
But as soon as you enter =L6 in L20 the calculation in L6 becomes circular and Excel doesn't like that, even though it's not affected by L20.
Consider to 'Enable iterative calculation' under file, Options, Formulas. Then the calculations work as you desire.
Charles, I've never used a UDF, but I'm open if you think it will work!
Riny, I appreciate that. I've shyed away from that in the past for fear of messing something up and not knowing, but you're correct that it should be fine in this use. Does the enabling of iterative calculations 1) stay with the workbook (if I send to someone else via email or access through shared link within organization, do they have to turn this feature on) and 2) does it apply to all of my Excel books/account, or just the one workbook?
Thanks guys!
This will give you the results you need.
In the formula bar type
=Contingency
then select the range of costs that the contingency needs to be applied to, then a comma, then select the required % in table column 6.
For example for "Contingency, Acquisition" the formula would be
=Contingency(K7:K8,I20)
Note that because a UDF is being used the file has to be saved as .xlsm not .xlsx
If you want to go with your original formulas then it is possible to enable interactive calculation just for this workbook by using the following VBA in the ThisWorkbook Object Module
Private Sub Workbook_Activate()
Application.Iteration = True
End Sub
Private Sub Workbook_Deactivate()
Application.Iteration = False
End Sub
Again the workbook must be saved as .xlsm not .xksx
Edit:
Note that the above will enable interactive calculation for all sheets in the workbook, so it is possible that other calculations on other sheets may be affected.
To overcome that you could try and enable interactive calculation for just the sheet with the Budget Table. I have not tried this on an individual sheet before, but see no reason why it wouldn't work.
In the Worksheet Object Module for the sheet with the Budget Table paste in this code, and remove the code from the ThisWorkbook Object Module
Private Sub Worksheet_Activate()
Application.Iteration = True
End Sub
Private Sub Worksheet_Deactivate()
Application.Iteration = False
End Sub
