Excel formula to calculate a progressive average down a sheet

a 5+5=10

b 7+7=14 average = 24/2 = 12

c 8+8=16 average = 40/3 =13.33333

d 9+9=18 average = 58/4 = 14.5

What Excel formula can be made to generate the average section of the page without having to write each line with a progressive number?

Line a =sum(5:5)

b =sum(a+(7+7)/2)

c=sum(a:b+(8+8)/3)

d=sum(a:c+(9+9)/4)

One way: If your values are in column A and required results in column B:

First entry in Column B: =AVERAGE(A$1:A1)

Then copy/paste the above to all following entries in column B.

The next one will automatically become =AVERAGE(A$1:A2) and so on.This will produce a running average in column B.

EDIT:

Sorry, on re-reading your post I'm not sure that is quite what you wanted to do - might give some ideas tho.

message edited by Derek

First, this sounds very much like a homework problem. We don't solve homework problems directly, but we will offer suggestions to help guide you to a solution. Second, Excel doesn't have "lines", it has Rows and Columns. Rows are designated by numbers, columns are typically designated by letters. By "line a, line b, etc." I assume you mean Row 1, Row 2, etc.

Third, your examples don't make sense.

You posted 2 ways to calculate "b"

b 7+7=14 average = 24/2 = 12

And

b = sum(a+(7+7)/2)

If a = 10, then your second example translates to SUM(10 + 14/2) = SUM(10 + 7) = 17, not 12.

Perhaps you meant =SUM(10+7+7)/2 which equals 12. The order of operations is very important.

In any case, if you want to automatically generate sequential numbers as you drag a formula down a columns, take a look at the ROW() function.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Thanks so much for your help. It has worked! I keep a record of the fuel that I put into my vehicle and enter each time I refuel - amount, cost, Ltrs per 100 Km. and miles per gallon. As there is quite a varience in usage depending on local running, long distance running and if we are pulling the caravan, I decided to try and get an average over a longer period of time. I knew there should be a way of using a formula but doing it using =sum( and increasing the multiplier each time was a rather long winded way of doing things! so thankyou you save a lot of time and frustration.

Ask Your Question

Weekly Poll

Do you think manufacturers should do more to reduce phone and tablet usage among kids?

Discuss in The Lounge

Poll History