So I have a workbook with multiple sheets and I need to average data based on the month and year in the adjacent cell. I have an array that I plug in to do monthly averaging, but I'm trying to go back and get older data. My date column (A) is setup month/day/year. The data that I want to average is in column (C). Also I need to keep the empty cells out of the formula.
Last edited by phantasm79; 01-24-2017 at 01:05 PM. Reason: Solved
Use cells to hold the date criteria.
E2 = start date
F2 = end date
Then:
=AVERAGEIFS(C2:C20,A2:A20,">="&E2,A2:A20,"<="&F2)
Adjust the ranges to suit.
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
I was looking for something that looked for the year and month in the formula so I could copy it down. I have 40 wells that I get a level reading from twice a month and some of the data goes back to the 60's. I'm trying to attach an example but it wont let me attach anything for some reason.
Make sure it's a SMALL file.
We don't need 1000's of rows and dozens of columns worth of data.
20 rows and a few columns worth of data is plenty.
To attach a file to your post...
Click the Reply button to open the Reply Editor
Click the Go Advanced button
Scroll down until you see the Manage Attachments link and click that
Click the Browse button and select your file
Click the Open button
Click the Upload button
Click the Close Window button
Click the Submit Reply button
The attached file is the smallest example I have.
OK, so what am I looking at here?
Where is the data you want to average and where should the result(s) appear?
This is why I asked for a SMALL sample file.
Sorry it is a bit busy, but its the smallest one I have. The results are actually going to appear on another workbook, but for now lets say B2 on the next sheet. The data I want to reference is starts @ A6 (date column). The data I need to average starts @ C6 (Level column). I have about 40 of these sheets so what I'm trying to achieve is a column of years and row of months.
Example: Say January 2011 I need to average the level (column C) for all 40 sheets for the corresponding date (column A).
Try
=SUM(SUMIF(INDIRECT("'"&Sheets&"'!A:A"),A6,INDIRECT("'"&Sheets&"'!C:C"))/SUM(COUNTIF(INDIRECT("'"&Sheets&"'!A:A"),A6)))
where "Sheets" is a named range of ALL your 40 tabs.
Enter with Ctrl+Shift+Enter
Date in A6
You mean something like this:Originally Posted by phantasm79
Data Range
And then the results would be the average across all 40 sheets?
Doable but VERY complicated and calculation intensive.
yup that's what the info would go into. complicated is why I came to the experts.
Do the sheet names follow some sort of sequential naming pattern or are they just random?
See the attached which is "sample" of what I think you require:
3 sheets with dates in column A and data in C ("Template" generates data)
Table in Summary is averages by MonTh and Year
Formula in B2
=SUM(SUMIFS(INDIRECT("'"&Sheets&"'!C:C"),INDIRECT("'"&Sheets&"'!A:A"),">=" & DATE($A2,MONTH(B$1),1),INDIRECT("'"&Sheets&"'!A:A"),"<=" & EOMONTH(DATE($A2,MONTH(B$1),1),0))/SUM(COUNTIFS(INDIRECT("'"&Sheets&"'!A:A"),">=" & DATE($A2,MONTH(B$1),1),INDIRECT("'"&Sheets&"'!A:A"),"<=" & EOMONTH(DATE($A2,MONTH(B$1),1),0))))
Enter with Ctrl+Shift+Enter
They all live in the same folder, but in different workbooks. The names are based on the well location so random.
I'm not following the filtered sheet references. Also there are data points I don't have on my workbook. Did you just make up some numbers?
Yes the data is generated from random (RANDBETWEEN function): I was simply trying to establish if the format was what was required.
So it calculates the data from a named list of tabs (your wells) and calculates the average by month for a given year.
ok so with your method would I just point to the work sheet in place of "sheets" and indicate the range with c:c and a:a?
In (under) "Sheets" you add the tab names (40 or so) ... changed the range in named range "Sheets" to be 40 sheets and change the starting range for the date : A6 in your sheet (?) whereas it was A2 in my example.
As Tony pointed out, it will take some time to process 40 sheets.
Perhaps test with 3 sheets (replacing Sheet1-3) [no change required to named range "Sheets"] so you can check the results: if these are OK add further entries into "Sheets"
does it need to be in the same workbook?
Yes: if there is a problem, post a file with 3 sheets (ZIP it if size is problem).
No there isn't a problem, it works. Is there a way to make it exclude 0 and blank cells? Also could it be placed on its own workbook instead of where the data lives. The reason I ask is that twice a month I have to batch convert all my data to CSV for our website. I could just go and delete the extra files if it cant be done.
To exclude 0s: based on my sample formula
=SUM(SUMIFS(INDIRECT("'"&Sheets&"'!C:C"),INDIRECT("'"&Sheets&"'!C:C"),">=0",INDIRECT("'"&Sheets&"'!A:A"),">="&DATE($A2,MONTH(B$1),1),INDIRECT("'"&Sheets&"'!A:A"),"<="&EOMONTH(DATE($A2,MONTH(B$1),1),0))/SUM(COUNTIFS(INDIRECT("'"&Sheets&"'!C:C"),">=0",INDIRECT("'"&Sheets&"'!A:A"),">="&DATE($A2,MONTH(B$1),1),INDIRECT("'"&Sheets&"'!A:A"),"<="&EOMONTH(DATE($A2,MONTH(B$1),1),0))))
Re being in a different workbook: INDIRECT only works with OPEN workbooks so you would to have your "data" workbook open.
I'll need to try it and see.
Last edited by JohnTopley; 01-23-2017 at 03:42 PM.
Ok so the 0 isn't helping. What's happening is that occasionally I have missed checks and therefor have no data for some dates. I just leave these as a blank cell, but the date is there. It looks like the formula you've supplied is taking the 2 data points and averaging 1500 and 0 coming up with 750.
If a value is 0 it is ignored: I tested this on my test file (sent to you), setting a cell to 0 or blank and it is not averaged.
If still have it, set C10 in sheet1 to blank (or zero) and you will see the average change.
If you look at the "validations" on the SUUMARY page and change the same value (B10) will get the same average and you will find it has ignored the blank.
BUT my error (I changed from this originally)..
=SUM(SUMIFS(INDIRECT("'"&Sheets&"'!C:C"),INDIRECT("'"&Sheets&"'!C:C"),">0",INDIRECT("'"&Sheets&"'!A:A"),">="&DATE($A2,MONTH(B$1),1),INDIRECT("'"&Sheets&"'!A:A"),"<="&EOMONTH(DATE($A2,MONTH(B$1),1),0))/SUM(COUNTIFS(INDIRECT("'"&Sheets&"'!C:C"),">0",INDIRECT("'"&Sheets&"'!A:A"),">="&DATE($A2,MONTH(B$1),1),INDIRECT("'"&Sheets&"'!A:A"),"<="&EOMONTH(DATE($A2,MONTH(B$1),1),0))))
Last edited by JohnTopley; 01-23-2017 at 04:34 PM.
that didn't fix mine. also I tried removing the date in C10 and it did nothing to the December 2016 value.
I attached what I have so far. I'm just using the one that I have one well in.
See if this file helps.
You did not copy my formula: you entered your own version.
The attached shows the results from my formula and Tony's.
Last edited by JohnTopley; 01-24-2017 at 09:12 AM.
Well I was pretty sure I copied what you put, but I guess not. Thought I only changed the range so I wouldn't be including the GPS coordinates at the top of column C, but I guess it doesn't matter since it doesn't have a corresponding date in A correct?
So now my question is do you think Tony's or John's will calculate quicker when I put 40 of these together?
Also have you made any progress on locating the data on a stand alone table?
Also thanks a lot for ya'll help so far and thanks for the patience.
Did some testing using the timer code found here:
http://msdn.microsoft.com/en-us/library/aa730921.aspx
I tested just the single formula for Dec 2016. Here are the test results. Results are times in seconds.
Data Range
That's just for 1 sheet. For 40 sheets this will be slow to calculate.
No: I haven't done any further work re results in separate workbook..
Perhaps Tony might advise on this as his formula is by far the faster.
Again thanks for the help and patience, if you think of a way or chat with someone that can show me how to put the formulas on a separate workbook send them my way. Just seems like the path could be put into the formula so the output data can live in a different workbook. It might take forever to calculate, but I'm cool with that. I have one that takes 3 minutes.
Try
=IFERROR(SUM(SUMIFS(INDIRECT("'[Upper Trinity Graphs.xlsx]"&Sheets&"'!C:C"),INDIRECT("'[Upper Trinity Graphs.xlsx]"&Sheets&"'!C:C"),">0",INDIRECT("'[Upper Trinity Graphs.xlsx]"&Sheets&"'!A:A"),">="&DATE($A2,MONTH(B$1),1),INDIRECT("'[Upper Trinity Graphs.xlsx]"&Sheets&"'!A:A"),"<="&EOMONTH(DATE($A2,MONTH(B$1),1),0))/SUM(COUNTIFS(INDIRECT("'[Upper Trinity Graphs.xlsx]"&Sheets&"'!C:C"),">0",INDIRECT("'[Upper Trinity Graphs.xlsx]"&Sheets&"'!A:A"),">="&DATE($A2,MONTH(B$1),1),INDIRECT("'[Upper Trinity Graphs.xlsx]"&Sheets&"'!A:A"),"<="&EOMONTH(DATE($A2,MONTH(B$1),1),0)))),"")
Insert highlighted into INDIRECT formulae
OR
=IFERROR(SUM(SUMIFS(INDIRECT("'[" & FileName & "]"&Sheets&"'!C:C"),INDIRECT("'[" & FileName & "]"&Sheets&"'!C:C"),">0",INDIRECT("'[" & FileName & "]"&Sheets&"'!A:A"),">="&DATE($A2,MONTH(B$1),1),INDIRECT("'[" & FileName & "]"&Sheets&"'!A:A"),"<="&EOMONTH(DATE($A2,MONTH(B$1),1),0))/SUM(COUNTIFS(INDIRECT("'[" & FileName & "]"&Sheets&"'!C:C"),">0",INDIRECT("'[" & FileName & "]"&Sheets&"'!A:A"),">="&DATE($A2,MONTH(B$1),1),INDIRECT("'[" & FileName & "]"&Sheets&"'!A:A"),"<="&EOMONTH(DATE($A2,MONTH(B$1),1),0)))),"")
Where FileName is named range containing name of your date file e.g.Upper Trinity Graphs.xlsx
Last edited by JohnTopley; 01-24-2017 at 03:20 PM.
Where does it pull the sheets from? My &sheets& don't light up when I inspect the formula.
"Sheets" is named range with sheet names
You mean make a sheet list in the new workbook?
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks