tracking excel sheet

peterk99_2

Hi all

I am trying to put together a spread sheet to keep track of attendance and fees owing as a group of my friends and I have started playing badminton recently and as organiser it is falling to me to book the courts. I don't mind doing this but of course I don't want to be out of pocket.

What I would like to be able to do as follows:
Enter the date the court is booked
Enter the cost of the court
Mark who attended (there are only 5 of us at the moment but Might need to add
a 6th player so Ideally that would need to be easy to do)

This would then allow the spread sheet to count the total number of players on that date, then divide the cost of the court between that number of players and add that cost to the total owing for each of the people that played on that date (the total owing would probably be best on a second sheet?)

I think then on the second sheet I would like to be able to record payments made by each individual player and have the spread sheet deduct that from the total owed.

I can sort of see in my mind how to do this but I don't know how to go about putting it together in excel, might anyone where be able to help me out with this?

Alternatively does anyone know of any such software (ideally free) that has this functionality??

Thanks

Nile

Hello peterk99

Welcome to the MSE Forum.:hello:

It isn't fair of your friends to expect you to organise the sessions and worry about covering costs. If it were me, I'd be asking everyone to pay a sum up front (£10 per player for example) and then they pay a smaller amount each session/game and keep an eye on the subs total. That should avoid a situation where you're out of pocket.

At the end of the season, you should have subs left and you can decide as a group what to do with the money.

We move threads if we think they’ll get more help elsewhere (please read the forum rule) so this post/thread has been moved to another board. If you have any questions about this policy please email [EMAIL="forumteam@moneysavingexpert.com"]forumteam@moneysavingexpert.com[/EMAIL].

I’ve moved your thread from the ‘I wanna’ board to the ‘Techie Stuff’ board, where it is better suited.

Regards

Nile

Consumerist

peterk99 wrote: »

. . . I can sort of see in my mind how to do this but I don't know how to go about putting it together in excel, might anyone where be able to help me out with this?

When I start projects of this sort, I first think about how I would do it manually on paper then set up the spreadsheet, in similar fashion to the paper sheet, to do the calculations I would otherwise need to do manually then enter on paper.

peterk99_2

Thanks for the replies. I was hoping someone must be already have something similar.

Le_Kirk

Open a new spread sheet, across the cells type in the names of your friends, say C2 to G2, down the rows, type date A3 and cost B3. In each cell corresponding to friends name if they attended put an "x" without the quotes. In cell H3 use the formula =B3/COUNTA(C2..G2). This gives the cost each only of those attending. If I could find a way of pasting/posting an excel spreadsheet, it would be easier.

tacpot12

Have send a private message.

peterk99_2

Thanks to tacpot12, this is now resolved. Thanks to all.

debitcardmayhem

peterk99 wrote: »

Thanks to tacpot12, this is now resolved. Thanks to all.

Share it perhaps? Ask a Q share the A or is this just me?

Consumerist

debitcardmayhem wrote: »

Share it perhaps? Ask a Q share the A or is this just me?

I agree, it didn't exactly provide anyone except, perhaps, the OP with any informative discussion.

Remember - be kind to newbies.

System

Hi

Well as this Money Saving I thought about Calc in LibreOffice.

AAND this is what i typed

Cell B11 =ROUNDUP(A4/COUNT(B5,B6,B7,B8,B9,B10) )

A4 being the cost of the session
/ Divided by
COUNT the value from b5 to b10, if they play enter 1 else Blank

So Column A is essentially player names, Row 4 is the Date.
Copy B11 across for every date wanted.

The roundup is make sure that sufficient is collected.

A little touch would be to multiply the cost by attendance to show who paid what.


So I kept the same size grid, same player names and B20 player 1 had this formula... =IF(B5,B$11,0)

If they played at B5 then they paid B$11 otherwise ZERO.
Copy down for each player, then across.

Also I counted the number of dates and multiplied by the cost, getting £335.40

I summed the rows of player costs, and the final column total and got £340.00, so that covers that then.

Obviously it is a little dynamic in that adding or deleting an attendance alters the average per session but the total cost should be static, and the amount collected still above that.