# Excel formula required by novice

Forum Editor 13:25 12 Feb 2005
Locked

As many of you will know, Excel isn't my strong point, but I have to use it in the course of my working life. Most of the time I'm working with files that have been created by others, so I don't need to delve into the (to me) mysterious world of formulae.

Now I have to create a workbook from scratch however, and I want to cheat by getting one of you to give me the formula.

We're talking about a finance control sheet - it has the following column headings ( I know it's not necessary for you to know them all, but I'll list them anyway:

A Item

B Details

C Cost

D Credit

E Approved?

F Approved date

G Contractor

H Comment

I Amount paid

J Date

K Balance

What I want is a file that will look at C, then deduct anything in D, then deduct anything in I, and then put a running balance in K. I need it to do that row by row, and enter totals in each currency column in the cell below the last entry, so I have a constantly updated set of totals.

Piece of cake for an Excel expert no doubt, but not to me. Any time you want advice on networking, Desktop publishing, Word docs or web design I'm your man, but spreadsheets leave me cold I'm afraid.

All contributions gratefully accepted.

VoG II 13:30 12 Feb 2005

You will need an extra column L to hold the running balance.

I will assume that your data starts in Row 2.

In K2 enter the formula

=C2-D2-I2

In L2 enter the formula

=SUM(L\$2:L2)

Hover the cursor over the bottom right of L2 until the cursor changes to a +

Hold down the left mouse button and drag down to copy the formula down as many rows as required.

Forum Editor 13:56 12 Feb 2005

what now happens is that the cells in K show the individual totals as they are entered in C, whereas I would like K to show a constantly updated total of the individual entries in C.

Also, the new row L, with your formula applied, simply shows zero in all cells - I need it to show the total from K, minus any enries in D and/or I

I'm sure it's my fault - it's quite difficult to explain this kind of thing in text form isn't it?

VoG II 14:01 12 Feb 2005

In K2

=SUM(C\$2:C2)-SUM(D\$2:D2)-SUM(I\$2:I2)

Then copy down as before.

Forum Editor 14:13 12 Feb 2005

When applying your formula Excel tells me that:

"The formula you entered contains an error"

Any thoughts?

Forum Editor 14:37 12 Feb 2005

I've arrived at this in K3:

=K2+C3-D3-I3

Bearing in mind that my data starts in row 2

It seems to work OK?

VoG II 14:41 12 Feb 2005

I may have completely lost the plot on this but I have e-mailed you a mock-up spreadsheet which works using the =SUM(C\$2:C2)-SUM(D\$2:D2)-SUM(I\$2:I2) formula.

mattyc_92 14:42 12 Feb 2005

Hi FE.... I haven't used Excel in years, so I can't remember the formulae structure... However a quick google search later, I got these links that you may be interested in.... Hope it helps...

Please bare in mind, that I haven't really looked through these sites, so sorry if they don't help...

pc moron 14:47 12 Feb 2005

I'm assuming Credit and Amount Paid are credits to the account, and Cost is a debit to the account.

To get a running balance, as in a bank account, I used these formula:

In cell K2, I entered: =D2+I2-C2

In cell K3, I entered: =D3+I3-C3+K2

and then dragged this formula down to the end of the list.

In the Balance column I selected Format Cells> Currency and chose to show a negative balance in red.

Forum Editor 14:50 12 Feb 2005

VoGâ„˘ - You haven't lost the plot at all, your sample file worked perfectly of course,and did exactly what I wanted it to do so something's obviously wrong in my own file.

Meanwhile - the formula I gave in my last post also seems to work, and I'm wondering what the difference is?

Forum Editor 14:56 12 Feb 2005

Many thanks.

There's a project cost, which is constantly being added to in Column C

There are also components being removed from the project as it evolves, and the costs for these are shown in column D

Amounts paid on account are entered into column I and the overall situation (the running balance) is reflected in column K

By looking at the individual column totals the clients can see a snapshot of the financial state of the project as it evolves. There are some pretty hefty sums of money involved here,and the project has a reasonably long life-cycle so I need to get it right.

This thread is now locked and can not be replied to.

Elsewhere on IDG sites