Excel or microsoft works spreadsheet question

  • Thread starter Thread starter Mo5
  • Start date Start date
M

Mo5

I need the correct formula for this spreadsheet. I've used microsoft works
spreadsheet but can change it to Excell. We have at work a spreadsheet that
we have date(A), numbered day 1,2,etc. (B), day sales(C), running
total(D) and average(E), five columns in all ABCDE all running down 30
days or so depending. We did the formulas for the two (running total and
average) but we did something wrong because the running total and average
totals at the bottom of the column keep running down the column. So if the
running total was 4000 , then 4000 would run down all 30 cells. Same with
Average. It would display better if it would end on what ever day your
entering. Exm: In column D the formula is =sum(C6) next is=sum(D6+C7)next
=sum(D7+C8)etc. For Average column its
=sum(D6/1)next=sum(D7+C8)next=sum(D8+C9)etc. I know this is confusing
without seeing it but hopefully someone can help. Thanks!
 
To get a running sum, you need to fix your reference to the cell at the top
of the column. Do this using the $ symbol.

In cell D3, the formula for running sum of column C is: =SUM(C$2,C3)
When copied and pasted to D4, the above formula pastes as =SUM(C$2,C4) and
so on down the column.

A running average would work in a similar manner.

To find out more visit this website and start reading after "Copying
formulas: "
http://www.cob.sjsu.edu/splane_m/ExcelFormulas.htm

You may also learn a little here:
http://www.cpearson.com/excel/relative.aspx

Hint
Learn to use your F4 key to change fixed and relative references:
- click on a cell reference in the formula edit bar.
- repeatedly press F4, watching the result


HTH
 
Hi,

First I don't use Works so my comments may be incorrect, if so I appologize.

Formulas such as =SUM(D7+C8) a bad for a number of reasons, they are more
complicated that necessary, they are longer and therefore make the
spreadsheet larger, they probably are slower than the correct formulas, and
to build complex formulas using this style would be a nightmare.

Here are correct examples of the four basic calculations:
=SUM(A1+B1) should be =A1+B1
=SUM(A1-B1) should be =A1-B1
=SUM(A1*B1) should be =A1*B1
=SUM(A1/B1) should be =A1/B1

SUM is used to simplify formulas, for example
=SUM(A1:A100) would sum all the cells form A1 to A100 something that
would not be fun using the =A1+A2+A3... method.

=SUM(D1/1) is double overkill since anything divide by 1 is itself and then
you sum it. Instead =D1
For averages, I suspect Works has an AVERAGE function which would use a
structure identical to that of SUM
=AVERAGE(A1:A100)

Now more to your problem
A running sum is usually done like this
=SUM(D$1:D1) then copy this down it becomes =SUM(D$1:D2) and so on.
A running average might be done in a similar manner but its not clear how
you data is laid out
=AVERAGE(D$1:D1) copied down.
 
Back
Top