Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula filling????
Hi, ive been asked to work out the average temperature for every year from
1880-2007. The problem i have is when i put in the normal =sum(a1:a12)/12 i need it to fill the formula so the next one is =sum(a13:a25)/12 and then the next one is =sum(a26:38)/12 etc etc ,when i try it excel doesnt remember the adding together the next set of 12 bit. If anyone could help it would save me loads of time Cheers! Year Month Temp Precipitation 1880 1 -5.2 17.7 1880 2 1.7 31.1 1880 3 7.6 14.5 1880 4 10.1 32.8 1880 5 13.2 11.5 1880 6 15.2 53.1 1880 7 19.1 42.1 1880 8 17.2 65.9 1880 9 14.6 56.8 1880 10 9.2 38.7 1880 11 2.9 64.5 1880 12 1.1 63.9 1881 1 -3.7 79.7 1881 2 1.6 33.8 1881 3 6 55.3 1881 4 9.8 16.7 1881 5 13.4 20.1 1881 6 16.8 16.1 1881 7 21 43.3 1881 8 17.9 129.8 1881 9 12.4 34.5 1881 10 5.7 34.4 1881 11 4.2 13.1 1881 12 -1.5 13.5 1882 1 -1.9 2.8 1882 2 0.9 6.4 1882 3 6.8 7.2 1882 4 9.4 18.1 1882 5 13.9 16.1 1882 6 15.8 33.7 1882 7 16.6 55.3 1882 8 16 91.4 1882 9 11.7 147.3 1882 10 8.9 77 1882 11 2.4 136.7 1882 12 0.1 103.1 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula filling????
In a separate column (F?) fill the years from 1880 to 2007, and then in G1
put in this array* formula: =AVERAGE(IF(F1=A$1:A$1524,C$1:C$1524)) * As this is an array formula, then once you have typed it in (or subsequently amend it) you must use CTRL-SHIFT-ENTER (CSE) to commit it rather than the usual ENTER. If you do this correctly then Excel will wrap curly braces { } around the formula when viewed in the formula bar - you must not type these yourself. You can then copy the formula down column G in the normal manner to suit the number of years you have in column F. You can do a similar thing for precipitation in column H, but change the reference from column C to column D. Hope this helps. Pete "Smalawi" wrote in message ... Hi, ive been asked to work out the average temperature for every year from 1880-2007. The problem i have is when i put in the normal =sum(a1:a12)/12 i need it to fill the formula so the next one is =sum(a13:a25)/12 and then the next one is =sum(a26:38)/12 etc etc ,when i try it excel doesnt remember the adding together the next set of 12 bit. If anyone could help it would save me loads of time Cheers! Year Month Temp Precipitation 1880 1 -5.2 17.7 1880 2 1.7 31.1 1880 3 7.6 14.5 1880 4 10.1 32.8 1880 5 13.2 11.5 1880 6 15.2 53.1 1880 7 19.1 42.1 1880 8 17.2 65.9 1880 9 14.6 56.8 1880 10 9.2 38.7 1880 11 2.9 64.5 1880 12 1.1 63.9 1881 1 -3.7 79.7 1881 2 1.6 33.8 1881 3 6 55.3 1881 4 9.8 16.7 1881 5 13.4 20.1 1881 6 16.8 16.1 1881 7 21 43.3 1881 8 17.9 129.8 1881 9 12.4 34.5 1881 10 5.7 34.4 1881 11 4.2 13.1 1881 12 -1.5 13.5 1882 1 -1.9 2.8 1882 2 0.9 6.4 1882 3 6.8 7.2 1882 4 9.4 18.1 1882 5 13.9 16.1 1882 6 15.8 33.7 1882 7 16.6 55.3 1882 8 16 91.4 1882 9 11.7 147.3 1882 10 8.9 77 1882 11 2.4 136.7 1882 12 0.1 103.1 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula filling????
Smalawi,
That is not how Fill works. I think you should look into using a pivot table for this. It will to all of the calculations you need with out much work from you, Look up pivot tables in XL's help and learn as much about using them as you can. -select all of your data (or just one cell in your data) -(in XL 2002/2003) Click Data menu PivotTable and PivotChart Report... (don't know XL 2007) -You'll probably be save selecting all of the defaults and clicking finish right from the get-go. -Add your "Year" column to the "Row" section of the pivot table -Add the "Temp" column to the "Data" section of the pivot table -Change the Temp column properties to average instead of sum. HTH, Conan "Smalawi" wrote in message ... Hi, ive been asked to work out the average temperature for every year from 1880-2007. The problem i have is when i put in the normal =sum(a1:a12)/12 i need it to fill the formula so the next one is =sum(a13:a25)/12 and then the next one is =sum(a26:38)/12 etc etc ,when i try it excel doesnt remember the adding together the next set of 12 bit. If anyone could help it would save me loads of time Cheers! Year Month Temp Precipitation 1880 1 -5.2 17.7 1880 2 1.7 31.1 1880 3 7.6 14.5 1880 4 10.1 32.8 1880 5 13.2 11.5 1880 6 15.2 53.1 1880 7 19.1 42.1 1880 8 17.2 65.9 1880 9 14.6 56.8 1880 10 9.2 38.7 1880 11 2.9 64.5 1880 12 1.1 63.9 1881 1 -3.7 79.7 1881 2 1.6 33.8 1881 3 6 55.3 1881 4 9.8 16.7 1881 5 13.4 20.1 1881 6 16.8 16.1 1881 7 21 43.3 1881 8 17.9 129.8 1881 9 12.4 34.5 1881 10 5.7 34.4 1881 11 4.2 13.1 1881 12 -1.5 13.5 1882 1 -1.9 2.8 1882 2 0.9 6.4 1882 3 6.8 7.2 1882 4 9.4 18.1 1882 5 13.9 16.1 1882 6 15.8 33.7 1882 7 16.6 55.3 1882 8 16 91.4 1882 9 11.7 147.3 1882 10 8.9 77 1882 11 2.4 136.7 1882 12 0.1 103.1 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Automatically filling in a formula in a cell | Excel Discussion (Misc queries) | |||
filling down a formula using =TEXT() | Excel Discussion (Misc queries) | |||
help with filling in an array formula | Excel Discussion (Misc queries) | |||
filling information from one cell and filling another. | Excel Worksheet Functions | |||
Filling Down Formula | Excel Discussion (Misc queries) |