Formula help with circular reference

  • Thread starter Thread starter Ross
  • Start date Start date
R

Ross

Hi, I am new to Excel and need to create a formula which
will calculate a running total of "hours worked" when
user enters "Daily Hours"

eg user enter 8 for daily hours and this is the first day
then total hours = 8. Next day user enters 12 in daily
hours cell and then formula auto calculates total hours as
20 and displays this total and so on each day.


Thanks for the help
Ross
 
Hi Ross

you didn't explain how your workbook was laid out and how many days you want
to add together
BUT assuming the people's names run along row 1 starting at B1
and the dates run down column A starting at A2
and the hours that are entered are entered starting at B2 for the first
person
and the total hours cell (for the first person) is B32
then in B32 type
=SUM(B1:B31)
and press enter
you can then autofill this across to the other people by holding the mouse
over the bottom right hand corner of B32 until you see a little black + then
hold the left mouse down and drag across the columns

Hope this helps
Regards
julieD
 
Oh Ross

i forgot to say that a single cell can not have a formula and a value in it
so if you were asking how to have someone type 8 in cell B2 and then the
next day type 12 in cell B2 and have B2 change to 20 this is not possible
without using code.

Regards
JulieD
 
Thanks for the extremely quick reply, Julie

I guess I am not very good at explaining what I am trying
to achieve. I will try again

I want to have this work for anyone and have it set up so
that it only uses about 3 cells like this.

Enter daily hours: " Cell A1"
Hold Previous hours " Cell F1" Hidden
Display Total hours: "Cell C1"

Using a formula something like this"
=A1+F1=C1
but then I need some way to update the info in cell F1 to
the current total of C1

Hope this makes more sence
 
Ross

Simple question with complex results.

You can have a cumulative total in a cell if you have a
separate source cell for adding a new total to the original.

Use at your own risk. I am Posting this just to show you how it can
be done, not as a good solution. You would be much better off to
have another column so you can keep track of past entries.

Goes like this: =IF(CELL("address")="$C$4",C4+D4,D4)

Enter this in cell D4 and then in Tools>Options>Calculation check
Iterations and set to 1.

Now when you change the number in C4, D4 will accumulate.

Note 1. If C4 is selected and a calculation takes place anywhere in
the Application D4 will update even if no new number is entered in
C4. NOT GOOD.

Note 2. This operation is not recommended because you will have no
"paper trail" to follow. Any mistake in entering a new number in C4
cannot be corrected. NOT GOOD.

To clear out the accumulated total in D4 and start over, select D4
and Edit>Enter.

Check out Laurent Longre's MoreFunc.xla. Has a Function RECALL
which does what you want without the re-calculation problem, but
again there is no "paper trail" for back-checking in case of errors
in data input.

http://longre.free.fr/english/func_cats.htm

Having said that, check out J.E. McGimpsey's site for VBA methods.

http://mcgimpsey.com/excel/accumulator.html


Gord Dibben Excel MVP
 
Ross,

I've pasted my standard blurb on running totals here. Hope it helps:

Running total in a cell using a circular reference.

It's possible to have a cell keep a running total of numbers entered into
another cell, but not a good idea.

You do it like this:
A1: a number
A2: =A1+A2

Any number entered into A1 gets immediately summed with the current total in
A2.

You must use Tools - Options - Calculation - Iterations: 1

The reason this is not a good solution is that you have no audit trail of
the numbers that have been summed. The formula cell just eats them up. How
do you know if a particular entry was ever made or not? It's much better to
list the numbers, and total with with a SUM() formula.
 
Hi Gord

have you tried using undo with the formula you posted ... it actually
increases the total when you "undo" an entry in cell C4 ... i hope the OP
will approach the question from a different direction ...

Cheers
JulieD
 
I suspect Gord knows that, which is one reason he suggested that the
downsides were "NOT GOOD".

OTOH, anyone using Excel for an accumulator probably isn't particularly
worried about Undo...there are so many other things that can go wrong.
 
Hi Jim

i guess i was just trying to "add" to his list of reasons why not to use
this approach ...

Cheers
JulieD
 
Julie.

When you "undo" it forces a re-calculation.

Since the undo changes C4, it is the same as selecting C4 and having a re-calc
done.

I will try to remember to add that caveat to my standard blurb.

Thanks, Gord
 
Back
Top