Cell Referencing

Tacomanator

New Member
Joined
Oct 22, 2002
Messages
15
Hi, I am trying to create a ledger in excel that looks like this:

Date | Type | Description | Amount | Balance

The first four fields should be entered manually, but I would like the balance to be calculated automatically using the sum of the previous balance and the amount entered. Follow?

1. So far from my messing around it seems like you have to have a function tied to a cell, therefore, how do I tell it that this entire column needs to do the same thing?
2. How do I reference cells such as, the cell directly above me and the cell directly to the left of me?

TIA
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
Welcome,

I wasn't sure if this was a template for accounts payable or receivable, but really it doesn't matter, payments are positive expenses, and receipts are positive balance entries.

Anyways, for the balance to calculate correctly, copy and paste it down the column, the cell references will update accordingly:
Book2
ABCDE
1DateTypeDescriptionAmountBalance
21/1/2002CashCreditCard100100
31/2/200250150
41/3/2002200350
51/4/200275425
61/5/2002 
Sheet1


HTH,
Corticus
 
Upvote 0
Well, that will work, thank you! Now, how about this:

I would like to add a field at the bottom that totals, for example, the 'amount' field for every row that has a type of 'debit'. If I was writing code I would say:

foreach my $row (@rows) {
$total = $total + $row->{amount} if $row->{type} == 'debit';
}

(for those of you who know PERL, I know the above is not quite right, but I wanted to make it more readable for those who do not).

TIA
 
Upvote 0
use a sumif formula, for example:

if B1:B5 houses your 'types', and E1:E5 houses your amounts, you would use the following formula in the cell where you want this total:

=sumif(B1:B5,"Debit",E1:E5)

hth
kevin
This message was edited by kskinne on 2002-10-23 17:51
 
Upvote 0
Corticus, could you please explain why the LEN component in the IF statement, with thanks.
 
Upvote 0
Thanks Kevin, that helped very much, I put it in an it worked, but it didnt update it self when I added a new row, so I experimented and I came up with this (better version, I think) instead:

=SUMIF(B:B,"Debit",D:D)

That way, it is not dependant on having a set amount of rows. Thanks again to all who have helped, I have added some additional features on top of that based on your help!
 
Upvote 0
Regarding the original question about a formula for a running balance, an alternative to Corticus's suggestion is to put the following in E2 and fill down :-

=SUM(D$2:D2)
 
Upvote 0
Hey,

Sorry for the delayed response, this **** work thing...

Anyways, I used len() to test for blanks, so the balance doesn't keep displaying all the way down the column. Len() tests for length, so if the cell's length is zero(blank) the formula displays nothing.

You could say If(A1="") instead of If(Len(A1)=0) but Aladin said to use Len(), so I do.

Peace,
Corticus
 
Upvote 0

Forum statistics

Threads
1,213,483
Messages
6,113,919
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top