Anyone with some Excel knowledge around?

[ROcHE]

Anyone around feeling helpful?

 

[ohtwell]

=Average(A1:B1)

This will give you the average of a set of numbers horizontally. If you want to do it vertically, just substitute B1 for A3, 4, or whatever cell the last number is in. Just make sure that all the cells you want to be included in the estimate are included in the function.


: ) Amanda

 

[ROcHE]

Anyway here is what I need :

I have a sheet with maybe 1000 lines of data, here what it looks like :



------------A----------------------------------B
1------2004-01-01------------------------1290
2------2004-01-01------------------------4923
3------2004-01-07------------------------2346
4------2004-01-18------------------------1236
5------2004-01-31------------------------1434
6------2004-02-01------------------------2344
7------2004-02-07------------------------1345
8------2004-02-30------------------------1234
9------2004-03-08------------------------7971
10-----2004-03-27-----------------------7977

I need an average per month. But I cannot do like Average(B1:B5) and Average(B6:B8) etc because the data will change every year and sometimes January will go from B1 to B12 for example.

Any ideas?

 

[DAM]

Use an IF statement, also use the help.


dam()

 

[ROcHE]

Originally posted by: ohtwell
=Average(A1:B1)

This will give you the average of a set of numbers horizontally. If you want to do it vertically, just substitute B1 for A3, 4, or whatever cell the last number is in. Just make sure that all the cells you want to be included in the estimate are included in the function.


: ) Amanda

Oops you replied before my details. Thanks anyway 😉

 

[ohtwell]

Do you mean something like this?

I would just do the months all the way across with the days going down. Then average them at the end, the way I described. If you wanted to do a yearly average you could do it rather easily. Plus, that looks neater.

Or you could do it oog's way. 🙂


: ) Amanda

 

[oog]

calculate a third column based on the dates in A. you can use a formula like =MONTH(A1) + "/" + YEAR(A1) to extrace just the month and year. then create a pivot table (it's under the data menu) where you choose this month column and column B. you can put the month column on either the X or Y axis (depending on if you want your averages to go across or down the page), and put column B should go into the center of the table. by default, the pivot table will try to just do a SUM for each month, but you can select the field properties on the pivot table toolbar and ask it to display averages instead.

edit: instead of the formula i posted to extract the date, you may be better of creating a new date based on only the month and year and something like day 1 (like this: = DATE(YEAR(A1), MONTH(A1), 1) ). that way you have a date rather than a string. you can always format the date to show only the month and year at that point.

 

[ROcHE]

Thanks to both of you for inputs. I'm gonna try that.