# 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 sheeta 5+5=10b 7+7=14 average = 24/2 = 12c 8+8=16 average = 40/3 =13.33333d 9+9=18 average = 58/4 = 14.5What 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)

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

#1
January 10, 2015 at 09:18:39
 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

Report •

#2
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 = 12Andb = 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.

Report •

#3
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