I want it to average based on month and year. I have daily data and want to average everything for say January 2000 into one cell and February of 2000 and so on. Column A has date (mm/dd/yy) and column B has data.
I want it to average based on month and year. I have daily data and want to average everything for say January 2000 into one cell and February of 2000 and so on. Column A has date (mm/dd/yy) and column B has data.
Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.
Click on GO ADVANCED and use the paperclip icon to open the upload window.
View Pic
Alan עַם יִשְׂרָאֵל חַי
Change an Ugly Report with Power Query
Database Normalization
Complete Guide to Power Query
Man's Mind Stretched to New Dimensions Never Returns to Its Original Form
You can try this:
For January: =AVERAGEIFS(B:B,A:A,">=1/1/2000",A:A,"<=1/31/2000")
For February: =AVERAGEIFS(B:B,A:A,">=2/1/2000",A:A,"<=2/29/2000")
etc...
For the year: =AVERAGEIFS(B:B,A:A,">=1/1/2000",A:A,"<=12/31/2000")
- Moo
Discharge example.xlsx
Here is the example. I want the formula to live on the second tab.
You can use a Pivot Table to achieve this
Here is a Pivot Table example as Pepe suggested - see attached file
- Moo
Not familiar with pivot tables. Can you walk me through how you did that?
I'm not familiar with pivot table. Can you walk me through how you did that?
Do this:
- Select all data from Sheet1 (A1:B732)
- Select the 'Insert' tab on the toolbar, then click on 'PivotTable'
- In the section 'Choose where you want the PivotTable report to be placed', click the 'Existing Worksheet' radio button, then select the 'Spring Branch Monthly Median' tab and select cell E2 then click OK.
- A 'PivotTable Field List' should appear to the right side of your screen. Drag the 'Date' field into the 'Row Labels' section, and drag the 'Discharge...' field into the 'Values' section.
You should now see that the PivotTable displays the data for every date in the data set... we're going to fix that in a second...
- In the 'Values' section, click the black downward pointing arrow at the right side of where it says "Sum of Discharge..." and select 'Value Field Settings'
- In the window that pops up, where it says 'Summarize value field by', select 'Average' then click OK
You'll see that nothing has really changed yet, except it now says Average... instead of Sum... Here's where we average it by month and year:
- Right-click the cell with 1/1/00 in it (should be E3) and select 'Group..."
- Make sure the 'Starting At' and 'Ending At' checkboxes are checked and the dates should be 1/1/2000 for 'Starting', and 12/31/2001 for 'Ending'
- Finally, select the 'Months' option and then hold down CTRL and select the 'Years' option as well, so both are selected. Then click OK.
What you SHOULD have now is a PivotTable with Averages grouped by year, and within each year, by month. Feel free to format to as many or as few decimal places as you wish.
If you have any other questions, I will try to answer them as I have time. =)
- Moo
And there is also a lot of explanations at http://www.contextures.com/tiptech.html#Go_P
And another place to look is in my signature, click on the link to Excel Video Tutorials.
You guys have been a lot of help. I've noticed that there isn't a median option in the pivot table. Am I missing something or am I going to have to do it as Moo suggested in post #3?
You can use this ARRAY formula to derive the MEDIAN by month from your data (Pivot Tables don't do MEDIAN as far as I know):
Formula:Please Login or Register to view this content.
Click on cell C2 of the 'Spring Branch Monthly Median' sheet, and paste the above formula into the formula bar, then press CTRL + SHIFT + ENTER, instead of just enter, so that it applies the array formula.
You can then just drag-fill that down for the other months.
- Moo
That worked great. Thanks.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks