Rent file query.....

[Dave Proctor]

Hi folks - a query which will probably seem a little bit too banal,
but you never know.

I work in hospitality, and therefore receive a lot of tips. I want to
pay all of my tips into the bank account of my landlord to allow me to
get ahead with my rent, I will be doing this via direct payment and
this is acceptable to her.

We both need some way of keeping track of these excess payments, and
reconciling it with the weekly rental payments. I was thinking an
Excel spreadsheet - which we both share - would be the way to go.

My rent is AUD150 per week.

I need some way of entering details such as the following:

Mon - $150 (paid one weeks rent, therefore the "paid to" date is
automatically incremented by one week)
Tues - $23.50 paid
Wed - $67.20 paid
Thurs - $37.90 paid
Fri - $32.20 paid
(equals $160.20 paid, therefore the "paid to" date is automatically
incremented by one week, with $10.20 added to the next lot of
payments)

Any thoughts here?

=====================

Dave

There are 10 types of people, those who understand binary and those who don't.

 

[Bernard Liengme]

I will assume we will start this on Nov 6 (first Monday in Nov)
Row 1 has headers: A1=Date, B1=Payment, C1=Total, D1=Week,E1=Week of,
F1=Paid
A2 date Nov 6 in local date format,
B2 how much you paid in on Nov 6 (might be zero),
C2 formula =SUM($B$2:B2) and copied down the column,
D220, numbers 1,2,3.....
E2 =A2+7*(D2-1) and copied down column
F2 =IF(SUM(B:B)>D2*150,150,IF(SUM(B:B)-150*D1>0,SUM(B:B)-150*D1,"")) and
copied down column

You could copy the formals just 5 rows down and let Excel 2003 automatically
extend them as needed
Enter new data in A and B cells as you make payments; columns E and F tell
what is needed
I will send file to your private email
best wishes