Solved how to progressively calculate averages down an excel sheet

January 9, 2015 at 22:09:54
Specs: Windows 8.1
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)

See More: how to progressively calculate averages down an excel sheet

Report •

January 10, 2015 at 09:18:39
✔ Best Answer
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.

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

Report •

January 10, 2015 at 20:38:56
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


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.

Report •

January 11, 2015 at 20:05:24
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.

Report •

Related Solutions

Ask Question