Adding RoundUp Back Into Formula

**brentjohn** · 04-12-2023, 06:31 PM

Greetings,
I have a worksheet that has a Sub-Total, Contingency (10% of the Sub-Total), and Total (adds the Sub-Total and Contingency). I would like to round-up the Total to the nearest 5,000. However, I am needing to add the difference of the Total and the roundup Total back into the Contingency. But this creates a circular references. Any suggestions without using a Macro. *I need for the Workbook to remain a non Macro-Enabled Workbook

**MrShorty** · 04-12-2023, 11:11 PM

If I correctly understand the algorithm, here's what I did:

1) In B3, I have my subtotal (I used =RANDBETWEEN(10000,100000) because I have no idea where your subtotals are coming from)
2) In B4, I calculate the 10% of subtotal =A3*0.1
3) In B5, I calculate a "temporary" total =SUM(B3:B4).
4) In B6, I round that up to the nearest 5000 =CEILING(B5,5000)
5) IN B7, I calculate the additional contingency =B6-B5
6) In B8, I calculate the total contingency =B7+B4
7) In B9, I recalculate the total as a check =B3+B8

If/when it is working correctly, B6 and B9 should be the same.

If you decide that is the correct logic/algorithm, then I expect one could carefully combine steps/cells B5:B8 into a single cell formula.