AgTalk Home
AgTalk Home
Search Forums | Classifieds (9) | Skins | Language
You are logged in as a guest. ( logon | register )

Excell help from the pros
View previous thread :: View next thread
   Forums List -> Computer TalkMessage format
 
Omar
Posted 9/16/2014 20:57 (#4078210 - in reply to #4077308)
Subject: RE: Excell help from the pros


Elmira, Ontario

My first choice would be a pivot table as already mentioned. Grouping, sub-totals and averages are what they are good for. The date column has to be actual dates, not just text that looks like dates to make the grouping work.

A second option is a helper column with a formula that looks like this:

=DATE(YEAR(A1),MONTH(A1),1)

What that does is converts every date that falls within the month to the first day.

In your report section, you'd have a list of possible months. The first one would be 1/1/2004. The second row would have a formula that you can copy down:

=DATE(YEAR(B1),MONTH(B1)+1,1)

This will magically give you a list of months in the column.

Beside that list, you will need a formula that is similar to one I used in a previous post.

=IFERROR(SUMIFS(TablePriceData[Price],TablePriceData[Month],C2)/COUNTIFS(TablePriceData[Month],C2),NA())

Oh, you might as well look at the sample I built to get an idea. I threw in a chart for the fun of it, just because the data was already there.

EDIT: changed the spreadsheet to use AVERAGEIFS instead of SUMIFS/COUNTIFS.



Edited by Omar 9/16/2014 21:17




Attachments
----------------
Attachments Average by Month.xlsx (16KB - 168 downloads)
Top of the page Bottom of the page


Jump to forum :
Search this forum
Printer friendly version
E-mail a link to this thread

(Delete cookies)