Board logo

Excel... should be simple.. but its not
tegwin - 2/11/14 at 05:45 PM

Anyone a dab hand at excel? I am having issues..

I basically have the daily flow rates from a river for the last 10 years. I want to average the flow by month.. So the 5000 rows of data can be displayed in 12 rows!


So in column A I have DATE, and column B I have FLOW

What I want to do is say... Average the numbers in column B IF the month number in column A = x (where x is the month number, 1=Jan, 2=feb etc)

I can use the MONTH() function to return the month number for the dates... but how do you selectivly average based on the constraint that month must = a constant
?


[Edited on 2/11/14 by tegwin]

r1_pete - 2/11/14 at 06:08 PM

Is this what you need:

flow
flow

Use subtotal, at each change in month use average add to flow column

tegwin - 2/11/14 at 06:12 PM

Damn that looks a lot easier ... didnt even think to try that!

gingerprince - 2/11/14 at 09:54 PM

A Pivot Table is what you need for summarising data. Select all you data and choose insert pivot table. Drag the date to the left, and flow to the table. Then right-click on a date, choose to group by month and year. et voila.

Barkalarr - 2/11/14 at 10:34 PM

quote:
Originally posted by gingerprince
A Pivot Table is what you need for summarising data. Select all you data and choose insert pivot table. Drag the date to the left, and flow to the table. Then right-click on a date, choose to group by month and year. et voila.


+1 for the pivot table.

If you need a hand how to do this, let us know what version of excel you're on.

Personally, I'd use year() in one column and month() in another column unless you're only interested in the month of the year.

One tip on a pivot table is make sure all the columns have headings or else it freaks out.

[Edited on 2/11/14 by Barkalarr]