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