SOLVED

NEED HELP WITH A FORMULA IN EXCEL 2019

Contributor

Dears ...

 

Kindly , find below attached a sheet for bank deposits , however , when I have expenses that exceeds cash the total will be in minus as in May 15th , I need a formula to take from the following days cash till we are even (0.000) for me to resume bank deposits 

 

The formula only functions when we have a minus 

 

Thanks in advance  

9 Replies
How about making that Total Cash column be a Net Cash:
=F5-G5-I5+H4
So before you make the deposit it will show how much cash you have.
After you make the deposit (assuming cash is + and you deposit it all) it will show 0.
If the prior day Cash is still negative then that is accounted for in that day's cash.
If you don't make a deposit then next day also adds that cash in.
Thanks for the help ...
One last thing , what about column total
It should reflect actual total , the formula should be other than a sum
IF the deposit for all non-negative cash days is for the full amount (i.e. Total Cash is either 0 or negative) then it will always reflect that day total or that day total - the negative cash from prior day as you requested. But if you want you can use:
=F5-G5-I5+IF(H4<0,H4,0)
or
=F5-G5-I5+MIN(H4,0)

Many thanks
wait I think I read your reply wrong. I thought you didn't like the first suggestion. If you stay with the first suggestion (i.e. a running total) then you don't need a total at the bottom since the last entry will always reflect that Net Cash. But if you need something you can use something like:
=INDEX(H4:H36,COUNT(H4:H36))
to grab the last cell with a value in it.
If you go with either option in the second response the total at the should be:
=SUMIF(H4:H36,">0")+MAX(INDEX(H4:H36,COUNT(H4:H36)),0)
which will add all positive cash values and then add the last value in the column if it is negative (and only the last because all the previous negative values get carried to the next row)
A million thanks

I try all formulas and use the most applicable

Dear @mtarler 

 

Kindly note that non of the formulas worked , if possible , can you implement the formulas in attached sheet ? 

 

Thanks in advance

 

best response confirmed by allyreckerman (Microsoft)
Solution

@Yousef70  see attached

Many thanks

You have been very helpful