Formula to take from another cell if total is a negative

[dan76n]

Hi all,

I have a formula that i need to add to.
when the cell total is a negitive number id like the cell to take the difference from another particular cell so the cell total is Zero.
The below is the current formula I have in the cell that id like to add the above to.
EG: the Cell is D23. Id like D23 to never be a negative. if the current formula total = -300 then id like the cell to take 300 from cell B2 on sheet 1 (D23 is on sheet 2).

=$D$23-(300-$D$17)

[Shareez Saleem]

Can you please attach a sample sheet?

[dan76n]

Appologies for taking so long to post this.

Ive attache my spreadsheet and added some data to try and give an example.
the the worksheet has 4 sheets in total.
Sheet 1 (POS Balances) is a summary page with all totals.
Sheets 2,3 and 4 (POS1, POS2 and POS3) are each individual register.

Each register has a base of $400 and at the end of the day we count the registers and leave $400 in each float.
Cells D8:D13 are the coin totals with D14 being coin subtotal D15 being the adjustment required to make the coins total equal a $5 increment.
L16:L21 is formulated to calculate the coins needed to be removed from the register float so the total equals a $5 increment.

D18:D22 are for the note totals with D23 the Subtotal, D24 the required Adjustment to have the Register total equal $400.

All works well as you will see on POS2 as the end of day total for the register is over $400.
Where i am stuck is if the register total is less than $400. What i would like to happen in this case is to take money out of the final banking of all registers and put it into the register that is below $400.
The total for all registers goes into cell B22 on the POS balances sheet.
I would also need to know the amaount that is being taken so migh need a new Cell on each POS sheet.

I hope all that makes some sense.


Just had a brain snap.
If I was able to get Cell D24 on each POS sheet to keep its current formula but have it equal Zero when there is no data for it to calculate instead of -$400 this might do the trick as I can have the total Banking cell on sheet 1 (B22) formulated from adding cells D16 and D24 from each POS sheet. (This would help if the register wasnt counted due to not being used. at worst we could just put $400 in cell D22 of any not being used).

[dan76n]

Not sure what the rules are on bumping but im still stuck on this one.
Tried a few things but cant get the result i need.

[AliGW]

We ask you bump no more than once within a 24-hour period. For you, no problem - it's been months.

Going to take a look.