How to stop the figure going below 0?

AceyWolf · October 18, 2023, 12:10pm

Hello,

I’ve got my spreadsheet setup to where figures are calculated automatically and once one value hits 0, it will then calculate using the other figures, without the 0 figure, however, when I try to work this out, it keep giving me a “circular reference” error, which I partially understand why, I just don’t know how to fix it.

The figures in E2, F2, G2 & H2 all pull from the N8 figure, which is correct as a starting point, however further numbers E3/4, F3/4, G3/4, H3/4 etc need to be pulled from the “remaining figures” in cells O8, O9, O10 & O11 so any further calculations can be made by the formula.

I was given a template by another user, however that display the remaining figures multiple times among many rows, which I don’t want, I just need one figure.

Any help would be greatly appreciated.

Steph1 · October 18, 2023, 4:13pm

Hello @AceyWolf,

On each row, you need to recalculate the amount of money available for each banknote, therefore, I have modified the formulas for the cells E3, F3, G3 and H3 to deduct from $N$8 the sum of the previous rows. This sum is calculated as, in E3 for example:
-SUM(E$2:E2)
(Note the $ that blocks the starting cell, so on each row, the range will grow, so you just have to drag down the formula vertically)
Manual Handpyaments V1.4.xlsx (54.9 KB)

AceyWolf · October 18, 2023, 4:46pm

This is perfect, thank you!

I’ve been analysing the formula you’ve used and can see what you have done, I tried something similar to that but not quite what you have done.

Thank you for the help, it’s gonna be a huge relief