 SOLVED

# NEED HELP WITH A FORMULA IN EXCEL 2019

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

9 Replies

# Re: NEED HELP WITH A FORMULA IN EXCEL 2019

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.

# Re: NEED HELP WITH A FORMULA IN EXCEL 2019

Thanks for the help ...
One last thing , what about column total
It should reflect actual total , the formula should be other than a sum

# Re: NEED HELP WITH A FORMULA IN EXCEL 2019

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

# Re: NEED HELP WITH A FORMULA IN EXCEL 2019

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)

# Re: NEED HELP WITH A FORMULA IN EXCEL 2019

A million thanks

I try all formulas and use the most applicable

# Re: NEED HELP WITH A FORMULA IN EXCEL 2019

Dear @mtarler

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

best response confirmed by allyreckerman (Microsoft)
Solution

# Re: NEED HELP WITH A FORMULA IN EXCEL 2019

@Yousef70  see attached

# Re: NEED HELP WITH A FORMULA IN EXCEL 2019

Many thanks

You have been very helpful