[Solved] Managing a PayPal account

[Pinky44]

Here's a nifty problem for all you Calc Gurus out there.
You will probably already know this but for clarification I will briefly detail it.
Most of my online purchases are by PayPal. No problem there as Paypal almost instantly email me with the value of my purchase. However, I also receive payments 'into' my Paypal account and again receive an email detailing the amount and from whom.
If I log into my PayPal account after I receive the 'You've got money' email I can see 'completed' purchases and my PayPal balance.
So far so good but here's the problem.
I'm sent £20:00 to my Paypal account and I can see it if I log in.
Sometime later I make an online purchase of £50:00 by PayPal and logging in it's shown as '-£50:00' Correct and of course my PayPal balance now shows £0:00.
The next quarter I get my bank statement and go through it trying to reconcile it against spending and income and I come across a Payment to PalPal of £30:00 which I can't identify, (2 months after I'd forgotten the £20:00 payment to my PayPal account and PayPal no longer show I had that credit at the time of my purchase).
I've spent a few days trying to set up a spreadsheet to handle this but seem to be running around in circles.
So far I've got the following:
Column A: Date
Column B: Reference (Text)
Column C: Monies received
Column D: Purchase amount
Column E: DDR from current account
Column F: Available PayPal credit
Column G: Transfer out
As you can see from the entry on 06 May my available PayPal credit has gone into the red by -£10. This will only get worse as I make more purchases before getting credits. Ideally this should show as £0:00 if I have no PayPal credit. I'm sure this could be solved by an 'If, Then, Else' formula but can't see how to implement it or where to put it. I would like the 'DDR from current account' column to show the actual amount PayPal are going to request from my current account after deducting my PayPal credit from my purchase and the cell should be empty if the row is a credit or my PayPal purchase is less than my credit.

[RusselB]

There are several ways of handling this. One option is to use a formula like

Code: Select all

=max(0;F2-D3+C3-G3)

in F3 and then copy that to the rest of the cells where you need the formula.
You are correct that this could be handled with an IF statement... which would look like

Code: Select all

=if(F2-D3+C3-G3<0;0;F2-D3+C3-G3)

Both codes will provide the results you are looking for, but I prefer to use the first code as it is simpler and only requires the F..G calculation to be done once, rather than twice.

As to the DDR column, I'd suggest using a formula like

Code: Select all

=if(F2-D3+c3-G3<0;F2-D3+C3-G3;"")

If any of these formulas give you a return that is unexpected, please let us know which formula, what you got as a result and what you were expecting.

[RPG]

I think the best way is to store all your data in a databaserange. Do not calculate your data in your databaserange. As in normal bookkeeping add result for the periode as lines in the databaserange. See it for the computer as a database.

You can filter the databaserange for each period you want to an other sheet. In that sheet do the calculation.
Start with summing up the two columns: received money and Purchase money.
You can substract the two value and you have the result. In our country we use the word: Saldo. When we insert the Saldo in the database we use also two characters: PM. As far I know it means: Pro Memory( keep in mind). When you have work with it the people new it is for starting and beginning. I have add the words: Start en End.

When you like it you can do the same with a registrated database.

Romke

[Pinky44]

If any of these formulas give you a return that is unexpected, please let us know which formula, what you got as a result and what you were expecting.

Brilliant. Problem solved. I now get the results I expected.
At first I couldn't get your formula for the DDR column to work properly but that had to be down to me! I'm off to the tutorials where I can use your formula as a real life example and try to pick it apart and understand it.
Many thanks.

[Pinky44]

I think the best way is to store all your data in a databaserange. Do not calculate your data in your databaserange. As in normal bookkeeping add result for the periode as lines in the databaserange. See it for the computer as a database.
<Snip>
When you like it you can do the same with a registrated database.

Romke

Thank you, quite a lot to get my head round but I will look at your example and try to understand it but for now the solution from RusselB is working fine and I'm only just starting to understand spreadsheets - so much to learn!!

[robleyd]

If you are new to spreadsheets, you may find the following to be useful resources.

[Tutorial] Ten concepts that every Calc user should know

OpenOffice Spreadsheet Tutorial for Beginners with Examples

[Pinky44]

If you are new to spreadsheets, you may find the following to be useful resources.

[Tutorial] Ten concepts that every Calc user should know

OpenOffice Spreadsheet Tutorial for Beginners with Examples

Thank you I'll certainly go and look.
How do I mark my post as solved or is that down to the administrators?

[robleyd]

I've marked your post solved, but you can do it in future:

How to tag a topic as solved.

In your first post, click the Edit button. Go to the subject line and add [Solved] at the beginning of the subject.

Then select the green tick as the Post icon (just below the subject line). Preview if you wish to see how it looks.
Finally, hit Submit - done!