Excel Average

[Quagan]

Hello,

I was wondering if it's possible for anyone to help me.

What I have is data of (2600) for 5 months.

I want to show this as an average over each month.

I can visually calculate it, down to 520 a month which works out at 17.87 a day.

However, how can I make a line graph to show this professionally?

Thanks in advance,

Andy.

[Whizbang]

Please see the attached workbook. It demonstrates how to conditionally average a column of values.

It uses the following formula (which is an array formula and must be confirmed with CTRL+SHIFT+ENTER):
=AVERAGE(IF(MONTH($A$2:$A$145) = MONTH(E2),$B$2:$B$145,0))

Where you dates are in column A and your data to be averaged is in column B. Column E contains the months to be averaged.

Here is some informative reading on array formulas. Additionally, there are some links on dynamic named ranges. Because of the limitation of array formulas, you cannot use an entire column (e.g. A:A, B:B, etc.). You must define the limitations of the range (eg. A1:A100, B1:B100, etc.). So, dynamic named ranges allow you to use array formulas without the worry and work of constantly increasing the range size in your array formula. I really recommend it.

Array Formulas:
http://www.cpearson.com/excel/ArrayFormulas.aspx
http://office.microsoft.com/en-us/ex...001087290.aspx
http://support.microsoft.com/kb/275165
http://www.mrexcel.com/articles/CSE-...ulas-excel.php

Dynamic Named Ranges:
http://www.ozgrid.com/Excel/DynamicRanges.htm
http://www.excelhero.com/blog/2011/0...ing-index.html - The bit about named ranges is at the bottom of the article, but I strongly recommend you read the whole thing.
http://support.microsoft.com/kb/830287