Excel or microsoft works spreadsheet question


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

Advertisements

A

AltaEgo

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
 
Ad

Advertisements

S

ShaneDevenshire

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top