Visit our Popular Forums

MarkJ · 19-12-2009, 17:59

Hi Folks of the Brainy, Financial types

Before I ask this question can we get one thing straight: I DID NOT COME TOP OF MY MATH CLASS!!!! In fact the teacher is still laughing 30 years later

I am trying to make up an Excel spreadsheet for my cruising expenses.

I just need a SIMPLE one but I can't do one calculation:

I can't make an input field where I put in what I have spent today and it adds it to the previous total. I seem to have to add it to the catagory total myself and then it adds it to the monthly or yearly total.

When I come back to the boat with a wallet full of receipts I just want to type the figures in to the correct catagories and it adds it to the running total.

For example I want:

Maintenance: <Todays amount> <Total>
Food: <Todays amount> <Total>

Yearly Total <total>

The attached XLS file (I would prefer XLSX but can't upload here) tries to show what I am trying to do.

I havent been able to find any ready made spreadsheet for what I want, and when I try to do a Sum of C + D = D I get a circle error.

Woe is idiot me.

I really want a nicer fat spreadsheet with months and years and all the rest but can't find one with the cumulative input

Thanks everly for the help!

Mark

Grasshopper · 19-12-2009, 18:11

I see two solutions, to maintain the format you have now would likely require a macro that copies and pastes todays data over into a table. The monthly total could then sum up the figures from that ever growing table. The second solution would be to abandon the data entry format you have and go straight to the tables. You could have a summary sheet that then reports the sum of the monthly expenses. Excel is fun...

**Ex-Calif** · 19-12-2009, 19:20

Hi Mark - Creating a spreadsheet for what you want is completely doable but requires considerable time effort.

I would recommend you download and use one of the thousands of free software that exist for tracking expenses.

I use Quickbooks to track the expenses on our boat. Quickbooks, of course is not free. However I recently bought a PC magazine that had a free software disc attached. One of the free programs is called Express Accounts - I read the reviews and it seems to be able to do full accounting.

Cnet.com is a great source of free software.

Here is a link to a free downloadable version of Express Accounts.

Express Accounts - Free software downloads and software reviews - CNET Download.com

I haven't used it and it may have more features than you want.

sabray · 19-12-2009, 20:12

If I understand you have columns with labels like mntc grocery etc...
Start with mntc. First cell below mntc is blank that is a,2. now put your entries below a2 after you have some entry's click on a2 click the formula box select some and highliight the entry's not a2 though. That will give you the total. You could highlight down 40 rows. That's the easiest way in excel maybe not the best.

ozmike · 19-12-2009, 20:17

Hi Mark,
Is this what your after, you can have daily spend totals in right hand side and each expence total at bottom, with grand total bottom right corner, hope the formulas paste as well.

**Boracay** · 19-12-2009, 22:37

Express Accounts looks to be for small business.

I did a search for home finance software and came up with :-
Personal Finances.

I had a quick look at the program and it looks like it might do what you're after. You'd need to create new expense categories for different boat expenses. Looks simple and straightforward.

Please keep in mind that I have no knowledge of the company or the website behind this software so use with caution.

There should be heaps of personal finance software out there.

Morganministry · 19-12-2009, 23:45

Quote:

Originally Posted by MarkJ

I havent been able to find any ready made spreadsheet for what I want, and when I try to do a Sum of C + D = D I get a circle error.

Woe is idiot me.

Mark

Since you have been up front about your math abilities I will not pick on you to much.

First of all the example that you made can not be calculated. Unless c=0.
otherwise anything plus D will be somthing other than D.

Secondly, I use Microsoft Works Database. When I want a total or some other amount to appear in a field, I enter the formula starting with =.
So to get the total price on some thing I would write a formula like this
=price*quantity

The total price for that Item would appear.

I hope that helps,
Scott

Anders · 20-12-2009, 00:16

Quote:

Originally Posted by MarkJ

I havent been able to find any ready made spreadsheet for what I want, and when I try to do a Sum of C + D = D I get a circle error.
Mark

To add upp all the marina expenses write 0 in the c3 field, write =c3+d3 in the d3 field. You get an circular error. Ignore it and go to tools->options->calculation. Click the Iteration check box and change maximum number of iterations to 1.
Anytime you write a number in the c3 field it will be added to what is already in the d3 field.

EDIT:
Not actually the best idea since you would have to put a 0 in the c3 field before you can put a number in the c4 field or it would add the number in the c3 field again.

MarkJ · 20-12-2009, 02:56

Quote:

Originally Posted by Anders

You get an circular error. Ignore it and go to tools->options->calculation. Click the Iteration check box and change maximum number of iterations to 1.
.

Winner!!!!!!!!!!!! Thats just what I was after!

I love the cruisers methods... don't take "error" just ignore it

Thanks for everyones help and particularly to OxMikes spreadsheet!

Mark

MarkJ · 20-12-2009, 03:45

Quote:

Originally Posted by Anders

EDIT:
Not actually the best idea since you would have to put a 0 in the c3 field before you can put a number in the c4 field or it would add the number in the c3 field again.

OK just found a problem with it.

LOL

Extemporaneous · 20-12-2009, 07:38

GnuCash Portable | PortableApps.com - Portable software for USB drives

I'm not sure if this program would do a good job of what you want, but because it's a PortableApp (and it's free) it's where I try to go first for my software requirements. I can also tell you that any of the PortableApp's that I have are FULL featured and work Great.

Good Luck,
Extemp.

donradcliffe · 20-12-2009, 08:38

Excel is much more flexible than the accounting programs, and there are several ways to do what you want. Without going into macro's, the best way to enter you data is like a checkbook log--one line for each entry, with date, $$, category, and description fields. Just enter each expense as it occurs.

Then use Excel's database tools to mine the raw data for what you want. First use the edit function to copy the sheet with the raw data to a new sheet, so you don't mess up your data. Then in the new sheet, highlight all your data (including the column headings) and use the data/sort function to rearrange your data. For example, if you want to see how much you spent on each category, you would sort first by the category and then by the date. Then you highlight the data again and use the data/subtotal to get a subtotal for each category (the subtotal function gives you a subtotal each time the data in the category column changes).

Getting monthly subtotals is a bit more complicated, but I would use the Month and Year function to extract the month from the dates you entered. Say you put the date in column A. Make a column E called "Month" and make cell E2 =MONTH(A2). Copy that formula all the way down your data and do the same with F2 = YEAR(A2). Now select your data and sort it by first year, then month, then use the subtotal function for year and month to get annual and monthly expenses.

You can do other fun things like putting in a country column, entering local currency and converting, etc. The type ahead function in Excel makes entering data a lot easier--for instance, if your category is "Fuel",
once you have entered it once, the next time you type "F", it will suggest "Fuel".

OTOH, why would you want to waste your valuable cruising time tracking your expenses???

**Ex-Calif** · 20-12-2009, 19:12

I don't disagree with the fact that excel can do anything you might need. The advantage of a freeware program is that the heavy lifting is already done for you.

Entering the data in excel is pretty easy. Setting it up correctly in the beginning is critical to the real purpose - getting the data out again for meaningful use.

An accounting program is not for everyone but I wouldn't do it any other way. The advantage is that you can easily get useful output like the attached...