Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Average across mutiple sheets

Posted on 2016-10-07
5
Medium Priority
?
74 Views
Last Modified: 2016-10-08
Good day.

I am creating a timesheet and have it all working except one function -  to be able to average the hours worked for the previous 9 weeks based on a holiday date.  (Sounds strange, but Provincial regulations!!!)

In the Excel Spreadsheet, I have a tab for each month, and a tab for "Defaults".
On each months Tab
    C8:AG8 has the dates
    C16:AG16 has the hours worked on each of those dates.

Note:  if a date is not worked, then 0 (zero) is recorded in row 16 for that date, therefore the average calculation has to avoid including those dates, i.e the average has to not include zeros.

On the Defaults Tab, I list the holiday and the holiday date which is updated each year - Some holidays are on the 3rd Monday of the month which means the date changes each year.

I have not be able to work out how to average the hours worked across multiple sheets.
The averageifs works, in that it has the ability to use criteria for the start and end dates and ignore 0 (zeros).  However I have not been able to get the range to work across multiple sheets.

This is some if what I have tried.
     - If I was trying to average between two dates on one tab - This works
        =AVERAGEIFS(Jul!C$16:AG$16,Jul!C$8:AG$8,">"&B33,Jul!C$8:AG$8,"<"&B24,Jul!C$16:AG$16,">0")
       
        Jul is the tab name
       C16:AG16 holds the hours worked for each day worked.
       C8:AG8 holds the date in date format.
       B33 is the start date (the holiday - (minus) 63 days (9 weeks)
       B24 is the end date (the holiday)


However when I go and try and expand the above equation to work over multiple tabs, it does not work.
       =AVERAGEIFS(Jan:Dec!C16:AG16,Jan:Dec!C$8:AG$8,">"&B33,Jan:Dec!C$8:AG$8,"<"&B24,Jan:Dec!C$16:AG$16,">0")



Any suggestion on how to average over multiple tabs using a start and end date, ignoring zeros/Null values would be appreciated.

Thank you all for help.

BTW - It will be used in Excel 2013 & 2016
0
Comment
Question by:bmcollis
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 27

Expert Comment

by:Shaun Kline
ID: 41834254
Instead of using AVERAGEIFS, try using SUMIFS and COUNTIFS. Basically you would have a sumifs for each of your month spreadsheets added together and divide that amount by the sum of all of the countifs. The basic formula:

=(SUMIFS(Jan!C16:AG16, Jan!C8:AG8, ">"&B33, Jan!C8:AG8, "<" & B24, Jan!C16:AG16, ">0") + 
SUMIFS(Feb!C16:AG16, Feb!C8:AG8, ">"&B33, Feb!C8:AG8, "<" & B24, Feb!C16:AG16, ">0") + ... + 
SUMIFS(Dec!C16:AG16, Dec!C8:AG8, ">"&B33, Dec!C8:AG8, "<" & B24, Dec!C16:AG16, ">0") ) 
/ 
(COUNTIFS(Jan!C8:AG8, ">"&B33, Jan!C8:AG8, "<" & B24, Jan!C16:AG16, ">0") + 
COUNTIFS(Feb!C8:AG8, ">"&B33, Feb!C8:AG8, "<" & B24, Feb!C16:AG16, ">0") + ... + 
COUNTIFS(Dec!C8:AG8, ">"&B33, Dec!C8:AG8, "<" & B24, Dec!C16:AG16, ">0") ) 

Open in new window

(Added returns for ease of reading. Remove them when using formula.)
0
 

Author Comment

by:bmcollis
ID: 41834473
Hi Shaun,

Thanks for the solution - it does work, although it creates a very long formula.

Pity the averageifs does not appear to work across multiple tabs.

Brian
0
 
LVL 27

Accepted Solution

by:
Shaun Kline earned 2000 total points
ID: 41834566
I found another solution. If you list your sheet names in a column, you can use the SUMPRODUCT, SUMIFS/COUNTIFS, and INDIRECT functions to calculate the average across sheets.

For example, if you list your monthly sheet names in column A from row 1 to 12, your start date is in B2 and your end date is in B3, your formula would look like this: (Tested in Excel 2007)
=SUMPRODUCT(SUMIFS(INDIRECT("'"&A1:A12&"'!C16:AG16"),INDIRECT("'"&A1:A12&"'!C8:AG8"),">" & B2,INDIRECT("'"&A1:A12&"'!C8:AG8"),"<" & B3))/SUMPRODUCT(COUNTIFS(INDIRECT("'"&A1:A12&"'!C8:AG8"), ">" & B2, INDIRECT("'"&A1:A12 &"'!C8:AG8"), "<" & B3, INDIRECT("'"&A1:A12&"'!C16:AG16"), ">0"))

Open in new window


You can also create a named range for your sheet names. Just create a new Named Range using Name Manager, and enter the sheet names as ={"Jan";"Feb";"Mar";...;"Dec"}
0
 
LVL 22

Expert Comment

by:Roy Cox
ID: 41834720
Why not have all the data on one sheet, it would be much easier to manage, maybe use PivotTables to get the reports you require.
0
 

Author Comment

by:bmcollis
ID: 41835349
Hi Shaun,

Thanks for the second take.  I had use the Indirect function in another part of the spreadsheet so had been trying to use that with the sumifs command with no luck.  Apparently missed/did not understand the sumproduct command.

So thank you for the revised and simplified  equation - worked a treat - I have marked it the Best Solution.



Hi Roy,

Thought of that, but believe for the user having separate Month tabs is easier.
Also thought having a "Year" tab, with all the months data on it, which would allow the averageifs formula to work, however at the end of the day, I thought a formula should be possible.

And thanks to Shune it is possible.
Thank your your input.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
This article describes a serious pitfall that can happen when deleting shapes using VBA.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

610 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question