cell formulas
 

I am creating a spreadsheet for my acount payables and recievables. I want to
designate a cell to keep a total.
I need one cell whenever I add a figure it add to the total and another cell
whenever I subtract a figure it will subtract from that same cell.

I don't know if I understand exactly what you're asking.

Might this be it?

Enter this is B1:
=SUM(A1:A100,C1,-D1)

Where your total cell (B1), which is adding Column A, will increase by the
number you enter in C1, and decrease by the number you enter in D1.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"Patrick" wrote in message
...
I am creating a spreadsheet for my acount payables and recievables. I want
to
designate a cell to keep a total.
I need one cell whenever I add a figure it add to the total and another
cell
whenever I subtract a figure it will subtract from that same cell.

Ok I want A1 to keep a sum of B1 and -C1. Every time I enter a figure in B1 I
want A1 to add that figure to the total. So if I enter 40 this time the total
in A1 will equal 40. Now I want to enter 20 in B1 tommorow and I want A1 to
equal 60. How I have it set up now is that when I enter 20 in B1 A1 will say
20. I want A1 to keep a running total of the entries in B1. Everytime I make
an entry in B1 I want A1 to add this entry to its total. I put 20 in B1 today
A1 reads 20. I put 20 in B1 tommorow A1 reads 40, I put 20 in B1 the next day
A1 reads 60. Then the same for C1 only I want C1 to subtract. Lets say A1
reads 60 and I enter 20 in C1 now A1 will read 40
the next time I enter 10 in C1 now I want A1 to read 30. Running totals is
what I need.
Thanks alot for you help by the way I hope I explained this well enough

"Patrick" wrote:

I am creating a spreadsheet for my acount payables and recievables. I want to
designate a cell to keep a total.
I need one cell whenever I add a figure it add to the total and another cell
whenever I subtract a figure it will subtract from that same cell.

Hi

It is possible with worksheets change event. But I myself consider this as
very bad idea - when you enter some wrong number, it will be practically
impossible to track down the error later.

Instead I advice to use the design, where all transactions are gathered into
single table, and running total is calculated from this table. As bonus, you
can design reort sheets, where p.e. all transactions for selected period,
along with totals for period start and end dates, are displayed.

An example of transaction table:
Sheet: Transactions
Date, Operation, Sum, Comment

The column Operation contains validation list with 2 selections: "payd" and
"recieved";
Sum is always positive.

At start you enter the start value for your account (as "recieved"). In
Comment field you can enter some explanation, so you don't get confused
later.
The table will be simplest to calculate, when you never don't have any
transactions earlier than start transaction, and you have only one start
transaction at any time.

You define dynamic named ranges (p.e. Date, Operation and Sum) for tables
datarange, using Date column as check range (so that all 3 named ranges are
always of same dimension)

The total will be:
=SUMIF(Operation,"recieved",Sum)-SUMIF(Operation,"payd",Sum)

When the number of rows in table grows, and you want to remove some elder
transactions, then you have to calculate the total for date, which will be
new start date (date0), enter the new start value, and delete all rows with
dates earlier as new start date (you can write a procedure for this).

The worksheet formula to calculate total for some fixed date (FixedDate) is:
=SUMPRODUCT(--(Date<FixedDate),--(Operation="recieved"),Sum)-SUMPRODUCT(--(D
ate<FixedDate),--(Operation="payd"),Sum)


Arvi Laanemets


"Patrick" wrote in message
...
I am creating a spreadsheet for my acount payables and recievables. I want
to
designate a cell to keep a total.
I need one cell whenever I add a figure it add to the total and another
cell
whenever I subtract a figure it will subtract from that same cell.

This is not a good idea, since you really have no record of what was keyed
in to the cell.
How would you track mistakes?

BUT ... If you really want to do something like this, check out this link to
J.E.McGimpsey's web page on the subject:

http://www.mcgimpsey.com/excel/accumulator.html
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"patrick" wrote in message
...
Ok I want A1 to keep a sum of B1 and -C1. Every time I enter a figure in
B1 I
want A1 to add that figure to the total. So if I enter 40 this time the
total
in A1 will equal 40. Now I want to enter 20 in B1 tommorow and I want A1
to
equal 60. How I have it set up now is that when I enter 20 in B1 A1 will
say
20. I want A1 to keep a running total of the entries in B1. Everytime I
make
an entry in B1 I want A1 to add this entry to its total. I put 20 in B1
today
A1 reads 20. I put 20 in B1 tommorow A1 reads 40, I put 20 in B1 the next
day
A1 reads 60. Then the same for C1 only I want C1 to subtract. Lets say A1
reads 60 and I enter 20 in C1 now A1 will read 40
the next time I enter 10 in C1 now I want A1 to read 30. Running totals is
what I need.
Thanks alot for you help by the way I hope I explained this well enough

"Patrick" wrote:

I am creating a spreadsheet for my acount payables and recievables. I
want to
designate a cell to keep a total.
I need one cell whenever I add a figure it add to the total and another
cell
whenever I subtract a figure it will subtract from that same cell.

Ok can you walk me through this I am very new to this.
I have sheet 1. I have a starting balance on cell H1.
Also on that sheet I have a Total on cell D3.
Sheet 2 Column E will be my Recieved and Column G will be my paid.
I dont know what the sum column is for.
I need to have any number entries on Column E sheet 2 added to D3 sheet 1,
and subtract from H1 sheet 1.
Example: I have starting balance of $1000 in H1. And a Total on D3 of $500.
I add $20 to any cell in Column E. We will say E2. Now H1 equals $980, E2
equals $20, and D3 equals $520.

I barely know where to start so please be patient and make your explanations
detailed Thank you so much.

"Arvi Laanemets" wrote:

Hi

It is possible with worksheets change event. But I myself consider this as
very bad idea - when you enter some wrong number, it will be practically
impossible to track down the error later.

Instead I advice to use the design, where all transactions are gathered into
single table, and running total is calculated from this table. As bonus, you
can design reort sheets, where p.e. all transactions for selected period,
along with totals for period start and end dates, are displayed.

An example of transaction table:
Sheet: Transactions
Date, Operation, Sum, Comment

The column Operation contains validation list with 2 selections: "payd" and
"recieved";
Sum is always positive.

At start you enter the start value for your account (as "recieved"). In
Comment field you can enter some explanation, so you don't get confused
later.
The table will be simplest to calculate, when you never don't have any
transactions earlier than start transaction, and you have only one start
transaction at any time.

You define dynamic named ranges (p.e. Date, Operation and Sum) for tables
datarange, using Date column as check range (so that all 3 named ranges are
always of same dimension)

The total will be:
=SUMIF(Operation,"recieved",Sum)-SUMIF(Operation,"payd",Sum)

When the number of rows in table grows, and you want to remove some elder
transactions, then you have to calculate the total for date, which will be
new start date (date0), enter the new start value, and delete all rows with
dates earlier as new start date (you can write a procedure for this).

The worksheet formula to calculate total for some fixed date (FixedDate) is:
=SUMPRODUCT(--(Date<FixedDate),--(Operation="recieved"),Sum)-SUMPRODUCT(--(D
ate<FixedDate),--(Operation="payd"),Sum)


Arvi Laanemets


"Patrick" wrote in message
...
I am creating a spreadsheet for my acount payables and recievables. I want
to
designate a cell to keep a total.
I need one cell whenever I add a figure it add to the total and another
cell
whenever I subtract a figure it will subtract from that same cell.

Hi

You must have a fixed starting value - all other are calculated based on
this startin value.

P.e. you started your database from 1st January 2005, with starting balance
$1000.
On Sheet1, into H1 you enter 1000;
On Sheet1, in D3 the total of income is calculated
=SUM(Sheet2!E:E)
On Sheet1, in D4 the total of payments is calculated
=SUM(Sheet2!G:G)
On Sheet1, in D5 the running balance is calculated
=SUM(H1,D3,-D4)

When there are no transactions on Sheet2, both D3 and D4 dispaly $0, D5
displays $1000
When you enter on Seet2 p.e. into cell E2 the value $100, on Sheet1 in D3
$100, in D4 $0, and in D5 $1100 are displayed
When you then enter on sheet into G3 the value $500, on Sheet1 in D3 $100,
in D4 $500, and in D5 $600 are displayed
etc.

This design is very like to one I sketched for you yesterday - only you have
income and outcome amounts in separate columns, when I had both of them in
same column (you hardly are with same bill getting money and paying it out),
and had a column with selectable transaction type to differ them. And I had
starting balance as ordinary transaction in same table.

--
When sending mail, use address arvil<attarkon.ee
Arvi Laanemets


"patrick" wrote in message
...
Ok can you walk me through this I am very new to this.
I have sheet 1. I have a starting balance on cell H1.
Also on that sheet I have a Total on cell D3.
Sheet 2 Column E will be my Recieved and Column G will be my paid.
I dont know what the sum column is for.
I need to have any number entries on Column E sheet 2 added to D3 sheet
1,
and subtract from H1 sheet 1.
Example: I have starting balance of $1000 in H1. And a Total on D3 of
$500.
I add $20 to any cell in Column E. We will say E2. Now H1 equals $980, E2
equals $20, and D3 equals $520.

I barely know where to start so please be patient and make your
explanations
detailed Thank you so much.

"Arvi Laanemets" wrote:

Hi

It is possible with worksheets change event. But I myself consider this
as
very bad idea - when you enter some wrong number, it will be practically
impossible to track down the error later.

Instead I advice to use the design, where all transactions are gathered
into
single table, and running total is calculated from this table. As bonus,
you
can design reort sheets, where p.e. all transactions for selected
period,
along with totals for period start and end dates, are displayed.

An example of transaction table:
Sheet: Transactions
Date, Operation, Sum, Comment

The column Operation contains validation list with 2 selections: "payd"
and
"recieved";
Sum is always positive.

At start you enter the start value for your account (as "recieved"). In
Comment field you can enter some explanation, so you don't get confused
later.
The table will be simplest to calculate, when you never don't have any
transactions earlier than start transaction, and you have only one start
transaction at any time.

You define dynamic named ranges (p.e. Date, Operation and Sum) for
tables
datarange, using Date column as check range (so that all 3 named ranges
are
always of same dimension)

The total will be:
=SUMIF(Operation,"recieved",Sum)-SUMIF(Operation,"payd",Sum)

When the number of rows in table grows, and you want to remove some
elder
transactions, then you have to calculate the total for date, which will
be
new start date (date0), enter the new start value, and delete all rows
with
dates earlier as new start date (you can write a procedure for this).

The worksheet formula to calculate total for some fixed date (FixedDate)
is:

=SUMPRODUCT(--(Date<FixedDate),--(Operation="recieved"),Sum)-SUMPRODUCT(--(D
ate<FixedDate),--(Operation="payd"),Sum)


Arvi Laanemets


"Patrick" wrote in message
...
I am creating a spreadsheet for my acount payables and recievables. I
want
to
designate a cell to keep a total.
I need one cell whenever I add a figure it add to the total and
another
cell
whenever I subtract a figure it will subtract from that same cell.