Help please...

[Lory]

My son Adam has just gone out on his own, as a fitter for a window company. He's been doing it for just over a month now and its become clear (to ME) that it would make life much easier for him to keep track of his expenses if he had some sort of spreadsheet

Now, not that I'm the interfering type of Mother but I thought I'd 'try' to make one for him, so that all he has to do is, open it and fill in the appropriate boxes and hopefully, at the end of the month, press a button and everything is totalled

I've got a Mac version of Microsoft Excel, which although I have had a play with some time ago, I'm really a complete novice

He will basically need..

The Date

1 column for Parking expenses

1 Column for the Congestion charge

1 column for Materials

and 1 column for miscellaneous

I need to know what option I need to start with(I kind of presume its 'data analysis?)and what I need to add, so it knows to add the columns up

What would be REALLY cool, would also be if somehow he could also have a way of adding the price of the job, so that it calculated the percentage due to him at the end of each month. But I'll settle for the list above first!

Any tips gratefully received

[David Bailey]

Now, not that I'm the interfering type of Mother but I thought I'd 'try' to make one for him, so that all he has to do is, open it and fill in the appropriate boxes and hopefully, at the end of the month, press a button and everything is totalled

I've got a Mac version of Microsoft Excel, which although I have had a play with some time ago, I'm really a complete novice

He will basically need..

The Date

1 column for Parking expenses

1 Column for the Congestion charge

1 column for Materials

and 1 column for miscellaneous

I need to know what option I need to start with(I kind of presume its 'data analysis?)and what I need to add, so it knows to add the columns up

Easy, you simply need a "add" formula at the bottom of each column, which adds up the entries above.

This uses the "SUM" function (for example, "SUM(A1:A5)" adds up cells A1+A2+A3+A4+A5

So you can create a set of these, as you require. You can also use other functions - e.g. to add / remove VAT etc.

Or, you can look for existing "expenses" templates (e.g. search Microsoft for expenses)

What would be REALLY cool, would also be if somehow he could also have a way of adding the price of the job, so that it calculated the percentage due to him at the end of each month.

What would be really really cool is if you used the spreadsheet function of Google Docs.

Keeps all your expenses online, and you can log into it from any web-enabled device (e.g. smartphone) and update it on the go.

And it's platform-independent.

And it's free.

Oh, and here's a list of Google docs free templates:

[Lory]

Easy, you simply need a "add" formula at the bottom of each column, which adds up the entries above.

This uses the "SUM" function (for example, "SUM(A1:A5)" adds up cells A1+A2+A3+A4+A5

So you can create a set of these, as you require. You can also use other functions - e.g. to add / remove VAT etc.

Or, you can look for existing "expenses" templates (e.g. search Microsoft for expenses)


What would be really really cool is if you used the spreadsheet function of Google Docs.

Keeps all your expenses online, and you can log into it from any web-enabled device (e.g. smartphone) and update it on the go.

And it's platform-independent.

And it's free.

Oh, and here's a list of Google docs free templates:

Brilliant advice, thankyou!

Although, when 'you' say simply, I just had a go and I can see that if I knew where everything way and what each symbol means, It WOULD be
Its a bit like photoshop, you have to speaka da language

I'm going to hunt down your other suggestions

[Lost Leader]

Perhaps you can you add a column for DB being super helpful and put a big tick in it.

[Lory]

The first part is up and running and even better than I'd hoped for.. instantly totalling and updating the column totals, plus the overall weekly total!

But I'm stuck on the 2nd part, is there a simple 'function code' which could subtract a percentage and give the total?

I've looked at the codes and to be honest, they all sound like gobbleygook to me

[David Franklin]

The first part is up and running and even better than I'd hoped for.. instantly totalling and updating the column totals, plus the overall weekly total!

But I'm stuck on the 2nd part, is there a simple 'function code' which could subtract a percentage and give the total?

I've looked at the codes and to be honest, they all sound like gobbleygook to me

To subtract a percentage (lets suppose it's A1), simply multiply by (1-A1/100). So, if the starting value was in B1 you'd have

B1 * (1-A1/100)

You can then either add up all the percentages, or take a percentage of the original total.

[Lory]

To subtract a percentage (lets suppose it's A1), simply multiply by (1-A1/100). So, if the starting value was in B1 you'd have

B1 * (1-A1/100)

You can then either add up all the percentages, or take a percentage of the original total.

OK, so let me get this clear..


I have the value of £100 in box A1
I have the value of £200 in box A2

And at the bottom of that column, I want it to keep totalling 20% of the added value of each row I add, so the sum of the above would be £60 but if I added another row of £100 it would automatically say £80

Is this possible?

[David Franklin]

OK, so let me get this clear..


I have the value of £100 in box A1
I have the value of £200 in box A2

And at the bottom of that column, I want it to keep totalling 20% of the added value of each row I add, so the sum of the above would be £60 but if I added another row of £100 it would automatically say £80

Is this possible?

Something like sum(A1:A20)*20/100 should work. (Disclaimer: I hardly use Excel and can't run it at work).

[Battlecat]

Lory

If you are bringing your laptop to sp, I can help you with it.

[bigdjiver]

OK, so let me get this clear..


I have the value of £100 in box A1
I have the value of £200 in box A2

And at the bottom of that column, I want it to keep totalling 20% of the added value of each row I add, so the sum of the above would be £60 but if I added another row of £100 it would automatically say £80

Is this possible?

It is possible, but it is not neccessarily a good idea.

IMO You should keep it simple and standard and just have the total at the bottom of the column.

Somewhere else you should have a a cells with the description "My 20%" or whatever next to one containing (if A99 is the total cell ) A99/5

[Lory]

Lory

If you are bringing your laptop to sp, I can help you with it.

Aw thank you so much for the offer but I know I wont be able to do anything which involves the use of my brain at Southport!

[Lory]

It is possible, but it is not neccessarily a good idea.

IMO You should keep it simple and standard and just have the total at the bottom of the column.

Somewhere else you should have a a cells with the description "My 20%" or whatever next to one containing (if A99 is the total cell ) A99/5

Probably true but I'm the sort of annoying person, who once I've got something in my head, I have to know how to do it. Even if I don't use it!

Thanks x