Setting up a spreadsheet

[ffutures]

This is probably simple but I'm drawing a blank on how to do it.

When I get the revised web site up I want to pass on some of the takings from the tip jar (if any) to Cancer Research UK. My current plan is 10% until I've covered the annual cost of the site, then 25%. To avoid micropayments the way this will work is I'll wait until £50-100 accumulates, then pay it to the charity, plus interest, as I did when it was shareware.

What I want to do is create an Excel spreadsheet where I enter the takings and it puts 10% into the charity column until I pass the annual site costs - currently about £85 including domain registration fees - then puts in 25% of each subsequent transaction. At the end of a year I want to reset the counter for costs and pass on 10% of subsequent transactions until my takings are over the site costs again. And so forth.

Is this possible, or would it be easier to change the ratio manually? This will of course be academic if I make less than the site costs, but I might as well think positively...

Comments

[bugshaw.livejournal.com]

Put your annual costs in a cell somewhere.
If running total > annual costs, 25%, else 10%
And on the running total field if year of this row's date > year of previous row's date, set to this row's value, else do running total

Or something like that?

[ffutures.livejournal.com]

That sounds like the way I thought it would work - I'm more used to using spreadsheets for things like RPG starship design but lookup cells are probably the way to go.

[draconin.livejournal.com]

I've created a sheet that may be along the lines you're talking about. It has a column to record your tip jar takings, a running total and a column that calculates the tip at either 10% or 25% (values which can be changed) depending on whether the running total is less than or more than the running costs (which can be changed). At the end of each year the running totals reset to zero since I assume you're going to be starting a fresh year of costs for the website.

It's probably not right yet because you don't specify what time interval you want the recording to be done at. I've guessed monthly but it's easily adapted to be weekly. I also had no idea what your website cost was. I also didn't incorporate your comment about not wanting to make micropayments since the whole idea is heavily dependent on time between recordings and other things I was missing info on.

Anyway, have a look here (https://dl.dropboxusercontent.com/u/57345361/Misc/TheTipJarSheet.xlsx) and tell me if it's what you had in mind. I'm happy to adapt it if you want.

[ffutures.livejournal.com]

Many thanks - I'll download it tonight when I'm using my work computer.

[ffutures.livejournal.com]

Sorry for the delay replying - I think that's exactly what I want, with a few minor tweaks such as pounds instead of dollars which I can easily do myself. Many thanks!

[ffutures.livejournal.com]

I forgot to say that my financial year for this one is the real year - web site renewal is paid at the end of December - so this one works very nicely.

[draconin.livejournal.com]

No problem. Yell if you need help altering any of the formulas. Some are a little more complex perhaps than they need to be since I have a habit of setting them up so that a cell that's based on calculations from another cell that's currently empty comes up blank rather than containing an erroneous value. If you'd not figured it out already, that's why a lot of them have IF(cell<>"", calculated value,"")

[ffutures.livejournal.com]

Someone actually donated some money today, so I got to use it in anger. Seems to work perfectly!

[draconin.livejournal.com]

Glad to hear it. :-) Let me know if you need it customised further; I have a lot of experience working with spreadsheets, including using Visual Basic to customise via macros. The one I sent you only took 10 minutes to set up.