IF formula

[klm]

Working on restaurant tip daily deposit reconciliation. Scenarios: Cash
received one day is 2,000 and cash used to pay out tips is 1,500, therefore
remaining cash to deposit is 500, but cash received from registers is 600 so
calculation goes in "over" column in the amt of 100 but if cash received
from registers is 400 then 100 goes in the short column

Now what if the cash received from customers is 2,000 and the cash used to
pay out tips is 2100 because they had 100 left over from the previous day?

I'm trying to figure out the calculation for over and short and the total
cash on hand to deposit at the end of the day.

 

[smartin]

Working on restaurant tip daily deposit reconciliation. Scenarios: Cash
received one day is 2,000 and cash used to pay out tips is 1,500, therefore
remaining cash to deposit is 500, but cash received from registers is 600 so
calculation goes in "over" column in the amt of 100 but if cash received
from registers is 400 then 100 goes in the short column

Now what if the cash received from customers is 2,000 and the cash used to
pay out tips is 2100 because they had 100 left over from the previous day?

I'm trying to figure out the calculation for over and short and the total
cash on hand to deposit at the end of the day.

I'm not totally sure where this is going, see if this helps...

Over := MAX(Register-(CashIn-TipOut),0)
Short := -MIN(Register-(CashIn-TipOut),0)

On the next day,
TipOut := [Current TipOut] + [Prior Over] - [Prior Short]