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
 

Some videos you may like

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

Corticus

Well-known Member
Joined
Apr 30, 2002
Messages
1,579
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
 

Tacomanator

New Member
Joined
Oct 22, 2002
Messages
15
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
 

kskinne

Well-known Member
Joined
Feb 17, 2002
Messages
1,267
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
 

SamS

Well-known Member
Joined
Feb 17, 2002
Messages
542
Corticus, could you please explain why the LEN component in the IF statement, with thanks.
 

Tacomanator

New Member
Joined
Oct 22, 2002
Messages
15

ADVERTISEMENT

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!
 
L

Legacy 11273

Guest
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)
 

Corticus

Well-known Member
Joined
Apr 30, 2002
Messages
1,579
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,672
Messages
5,597,488
Members
414,146
Latest member
marginmakerb

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
Top