Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average price between dates
I would like to get the average number on a specific range.
I have B7:B172 with dates (from 01/01/08 to 25/08/08) sorted descending. I have D7:D172 with cooper prices for these days. I would like to get the average price per month. Be aware that I use spanish dates so I use dd/mm/yyyy. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average price between dates
You have at least a couple of choices.
One way is to make sure you have headers and then add another helper column: =text(b7,"yyyymm") (with my USA settings and my English function and yyyymm abbreviations!) Then sort your data (all the columns!) by this helper column. Then use Data|Subtotal (xl2003 menus). Be sure to use Average as the subtotal function. ======== Another way is to use data|pivottable (also xl2003 menus). Add those headers if you don't have them, but don't bother sorting the data (it's not required). Then select the range Data|Pivottable Follow the wizard until you get to a step with a Layout Button on it. Click that layout button Drag the header for the date to the row area Drag the header for the price to the Data area (rightclick on it and choose Average) and finish up the wizard. You now have a summary report for average per date. So you're not quite done. Right click on the Date header in the pivottable. Choose Group and show detail. Then choose Group. And group by months and years. If you've never used pivottables, here are a few links: Debra Dalgleish's pictures at Jon Peltier's site: http://peltiertech.com/Excel/Pivots/pivottables.htm And Debra's own site: http://www.contextures.com/xlPivot01.html John Walkenbach also has some at: http://j-walk.com/ss/excel/files/general.htm (look for Tony Gwynn's Hit Database) Chip Pearson keeps Harald Staff's notes at: http://www.cpearson.com/excel/pivots.htm MS has some at (xl2000 and xl2002): http://office.microsoft.com/downloads/2000/XCrtPiv.aspx http://office.microsoft.com/assistan...lconPT101.aspx PaulinaDi wrote: I would like to get the average number on a specific range. I have B7:B172 with dates (from 01/01/08 to 25/08/08) sorted descending. I have D7:D172 with cooper prices for these days. I would like to get the average price per month. Be aware that I use spanish dates so I use dd/mm/yyyy. -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
average price | Excel Worksheet Functions | |||
calculate/convert volume price to monthly average price | Excel Worksheet Functions | |||
average price of 4 columns of figures but some cells can be blank | Excel Worksheet Functions | |||
How do I get the average price per bid for an individual month? | Excel Discussion (Misc queries) | |||
Calculating Net Position and Average Price | Excel Worksheet Functions |