+ Reply to Thread
Results 1 to 29 of 29

average across worksheets without counting blank cells

  1. #1
    Registered User
    Join Date
    11-08-2017
    Location
    Washington, DC
    MS-Off Ver
    2010
    Posts
    8

    Unhappy average across worksheets without counting blank cells

    my problem is almost exactly the same as the one listed here: https://www.excelforum.com/excel-for...orksheets.html
    accept I will never have negative number so i dont need that part of the equation.
    This formula works but only if there is a number filled in every cell, which wont always be the case: =AVERAGE('Employee 1'!O5:Q6,'Employee 2'!O5:Q6,'Employee 4'!O5:Q6,'Employee 5'!O5:Q6,'Employee 5'!O5:Q6,'Employee 6'!O5:Q6,'Employee 7'!O5:Q6,'Employee 8'!O5:Q6)
    In a simpler part of the workbook, i found =AVERAGEIF(O5:Z6, ">0") fixed this issue but i understand AVERAGEIF doesnt work when your formula is pulling from multiple worksheets.
    Can anyone give me formula that will give me the average of cells ('Employee 1'!O5:Q6,'Employee 2'!O5:Q6,'Employee 4'!O5:Q6,'Employee 5'!O5:Q6,'Employee 5'!O5:Q6,'Employee 6'!O5:Q6,'Employee 7'!O5:Q6,'Employee 8'!O5:Q6) without including the blank cells? I have been stuck for hours.
    Please also explain your formula because I will need to recreate it with slightly different parts of the workbook.
    Thanks

  2. #2
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,007

    Re: average across worksheets without counting blank cells

    Try

    Try

    =SUMPRODUCT(SUMIF(INDIRECT("'" &Sheets &"'!O5:Q6"), ">0"))/SUMPRODUCT(COUNTIF(INDIRECT("'" &Sheets &"'!O5:Q6"), ">0"))

    where Sheets is a named range of all your sheets (Employee 1,Employee 2......Employee 7)

    The SUMIF simply SUMS the vales >0

    The COUNTIF simply counts values > 0

    Results is the AVERAGE

    The SUMPRODUCT with INDIRECT performs a "3D" calculation i.e looks at all sheets in your "Sheets" named list

    However the better (correct) practice is to collect ALL data on one sheet.
    Last edited by JohnTopley; 11-08-2017 at 02:13 PM.

  3. #3
    Registered User
    Join Date
    11-08-2017
    Location
    Washington, DC
    MS-Off Ver
    2010
    Posts
    8

    Re: average across worksheets without counting blank cells

    Thanks john,
    I tried the formula below but it didnt work. I'm not sure I understood where and how i am supposed to enter the sheets that I want cells O5-Q6 pulled from.
    =SUMPRODUCT(SUMIF(INDIRECT("'" &Employee 1, Employee 2, Employee 3, Employee 4, Employee 5, Employee 6, Employee 7, Employee 8&"'!O5:Q6"), ">0"))/SUMPRODUCT(COUNTIF(INDIRECT("'" &Employee 1, Employee 2, Employee 3, Employee 4, Employee 5, Employee 6, Employee 7, Employee 8 &"'!O5:Q6"), ">0"))
    Would you mind explaining it again?

  4. #4
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,007

    Re: average across worksheets without counting blank cells

    See attached with example data.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    11-08-2017
    Location
    Washington, DC
    MS-Off Ver
    2010
    Posts
    8

    Re: average across worksheets without counting blank cells

    Im sorry but I still don't understand how I am supposed to do the whole name range thing. i uploaded the document im using. Im trying to put this formula under "quarterly output" sheet, in cell C4/5

  6. #6
    Registered User
    Join Date
    11-08-2017
    Location
    Washington, DC
    MS-Off Ver
    2010
    Posts
    8

    Re: average across worksheets without counting blank cells

    it appears i am the forum wont let me upload a document.

  7. #7
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    6,266

    Re: average across worksheets without counting blank cells

    Untested, but try:

    =SUM('Employee 1:Employee 8'!O5:Q6)/INDEX(FREQUENCY('Employee 1:Employee 8'!O5:Q6,0),2)

  8. #8
    Registered User
    Join Date
    11-08-2017
    Location
    Washington, DC
    MS-Off Ver
    2010
    Posts
    8

    Re: average across worksheets without counting blank cells

    no luck falcon dude....Thanks for trying though!

  9. #9
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    6,266

    Re: average across worksheets without counting blank cells

    To attach a sample workbook, click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

  10. #10
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,007

    Re: average across worksheets without counting blank cells

    Attach a sample workbook (not image).

    Make sure there is just enough data to demonstrate your need. 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 shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

  11. #11
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,007

    Re: average across worksheets without counting blank cells

    To create your named range:

    List of sheets in a column from row 1 to 8 (say M1:M8) on ANY sheet

    Click on "Formulas"==>"Define Name"

    in "Name:" box enter "Sheets" (without quotes)

    in "Refers to:" enter Sheetx!$M$1:$M$8 (whatever the sheet name is)

    Click "OK"

  12. #12
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: average across worksheets without counting blank cells

    Quote Originally Posted by Allison S View Post
    Can anyone give me formula that will give me the average of cells ('Employee 1'!O5:Q6,'Employee 2'!O5:Q6,'Employee 4'!O5:Q6,'Employee 5'!O5:Q6,'Employee 5'!O5:Q6,'Employee 6'!O5:Q6,'Employee 7'!O5:Q6,'Employee 8'!O5:Q6) without including the blank cells?
    Do you really mean blanks or zeroes? Your average formula ignores blanks anyway so if you only want to ignore blanks then this should work:

    =AVERAGE('Employee 1'!O5:Q6,'Employee 2'!O5:Q6,'Employee 3'!O5:Q6,'Employee 4'!O5:Q6,'Employee 5'!O5:Q6,'Employee 6'!O5:Q6,'Employee 7'!O5:Q6,'Employee 8'!O5:Q6)

    If you really mean that you want to ignore zeroes then I would expect 63falcondude's suggestion to work, i.e.

    Quote Originally Posted by 63falcondude View Post
    =SUM('Employee 1:Employee 8'!O5:Q6)/INDEX(FREQUENCY('Employee 1:Employee 8'!O5:Q6,0),2)
    You said that didn't work, but why not - did you get an error, an incorrect answer or something else?
    Audere est facere

  13. #13
    Forum Expert leelnich's Avatar
    Join Date
    03-20-2017
    Location
    Delaware, USA
    MS-Off Ver
    Office 2016
    Posts
    2,807

    Re: average across worksheets without counting blank cells

    Quote Originally Posted by 63falcondude View Post
    Untested, but try:
    =SUM('Employee 1:Employee 8'!O5:Q6)/INDEX(FREQUENCY('Employee 1:Employee 8'!O5:Q6,0),2)
    Unfortunately, not all Excel functions work with 3-D refs. Here's a list of those that do:

    SUM - adds up numerical values.
    AVERAGE - calculates arithmetic mean of numbers.
    AVERAGEA - calculates arithmetic mean of values, including numbers, text and logicals.
    COUNT - Counts cells with numbers.
    COUNTA - Counts non-empty cells.
    MAX - Returns the largest value.
    MAXA - Returns the largest value, including text and logicals.
    MIN - Finds the smallest value.
    MINA - Finds the smallest value, including text and logicals.
    PRODUCT - Multiplies numbers.
    STDEV, STDEVA, STDEVP, STDEVPA - Calculate a sample deviation of a specified set of values.
    VAR, VARA, VARP, VARPA - Returns a sample variance of a specified set of values.


    Note that FREQUENCY() is not among them...
    Clicking the Add Reputation star below helpful posts is a great way to show your appreciation.
    Please mark your threads as SOLVED upon conclusion (Thread Tools above Post # 1). - Lee

  14. #14
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    6,266

    Re: average across worksheets without counting blank cells

    leelnich, that is what I thought too until being proven wrong by daddylonglegs in another thread and some further research.

    See attached.
    Attached Files Attached Files

  15. #15
    Forum Expert leelnich's Avatar
    Join Date
    03-20-2017
    Location
    Delaware, USA
    MS-Off Ver
    Office 2016
    Posts
    2,807

    Re: average across worksheets without counting blank cells

    @63falcondude, I stand corrected! Thanks very much for pointing that out.

  16. #16
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: average across worksheets without counting blank cells

    [offtopic]There’s a longer list here

    https://bettersolutions.com/excel/fo...imensional.htm

    ....but it still doesn’t include FREQUENCY......and I haven’t verified that any of those work.

    Note that FREQUENCY is very accommodating. It will accept a 3D reference as the bins array....and also accepts “unions” which can be useful for calculating average above zero over discontinuous ranges, in a similar way to the dude’s suggestion here, e.g.

    =SUM(range1, range2)/INDEX(FREQUENCY((range1, range2),0),2)
    [\offtopic]

  17. #17
    Registered User
    Join Date
    11-08-2017
    Location
    Washington, DC
    MS-Off Ver
    2010
    Posts
    8

    Re: average across worksheets without counting blank cells

    Thank you all for the help! Sorry I was at a training last week without a computer. None of the suggestions being given work. I dont understand what I'm doing wrong but whenever i try to upload a doc, the sceen blinks to show it updated but my document isnt listed in the attachments. I have 8 epmployee sheets that show their total cases completed each day, on the side of each sheet i have a table that calculates each employees monthly completion averages. My problem is that i need a quarterly output average for the office. I created a 9th sheet and am trying to figure out a formula that will allow me to take the monthly average from 3 months, across all 8 sheets but everything I try gives me some basic error (saying there is an error in the formula or I have added to many factors to the formula).

  18. #18
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,007

    Re: average across worksheets without counting blank cells

    Without a file it is not possible to diagnose the problem.

    See post #10 for details of how to add an attachment.

  19. #19
    Registered User
    Join Date
    11-08-2017
    Location
    Washington, DC
    MS-Off Ver
    2010
    Posts
    8

    Re: average across worksheets without counting blank cells

    I figured out what the problem was with uploading my document. I had to delete sheets 2-6 because the file was too big but the were exactly the same as the the other employee tabs.
    Attached Files Attached Files

  20. #20
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    6,266

    Re: average across worksheets without counting blank cells

    =SUM('Employee 1:Employee 8'!O5:Q5)/INDEX(FREQUENCY('Employee 1:Employee 8'!O5:Q5,0),2)

    Edit: Will give you Q4 2017
    Last edited by 63falcondude; 11-20-2017 at 12:13 PM.

  21. #21
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,007

    Re: average across worksheets without counting blank cells

    I added IFERROR to O5 and across

    =IFERROR(AVERAGEIF(C3:C33,"full",D$3:D$33),"")

    AVERAGE will ignore blanks.

    In "Quartlerly output"

    in C4

    =IFERROR(SUMPRODUCT(SUMIF(INDIRECT("'" &Sheets &"'!O5:Q5"), ">0"))/SUMPRODUCT(COUNTIF(INDIRECT("'" &Sheets &"'!O5:Q5"), ">0")),0)

    and modified ranges for other quarters

    in G4

    =IFERROR(SUMPRODUCT(SUMIF(INDIRECT("'" &Sheets &"'!O5:Z5"), ">0"))/SUMPRODUCT(COUNTIF(INDIRECT("'" &Sheets &"'!O5:Z5"), ">0")),0)

    and set "Sheets" named range
    Attached Files Attached Files

  22. #22
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,007

    Re: average across worksheets without counting blank cells

    NOTE: I believe mathematically the Quarterly. figures are incorrect as you are averaging AVERAGES.

    The correct way is to average ALL (raw) data for a quarter period.

  23. #23
    Registered User
    Join Date
    11-08-2017
    Location
    Washington, DC
    MS-Off Ver
    2010
    Posts
    8

    Re: average across worksheets without counting blank cells

    That works only when there is a number in each of the monthly averages. If someone is out for a month and they were not assigned to casework, that throws off the calculation. I have the same issue with the monthly averages too. If I had , ">0" it says i have to many functions.

  24. #24
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    6,266

    Re: average across worksheets without counting blank cells

    Quote Originally Posted by JohnTopley View Post
    NOTE: I believe mathematically the Quarterly. figures are incorrect as you are averaging AVERAGES.

    The correct way is to average ALL (raw) data for a quarter period.
    As long as the ranges are the same size (didn't check to see if this was the case), you can take an average of averages.

  25. #25
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    6,266

    Re: average across worksheets without counting blank cells

    Quote Originally Posted by Allison S View Post
    That works only when there is a number in each of the monthly averages. If someone is out for a month and they were not assigned to casework, that throws off the calculation. I have the same issue with the monthly averages too.
    If you include an IFERROR function to your formulas, that will remedy this.

    e.g. O5 =IFERROR(AVERAGEIF(C3:C33,"full",D3:D33),"")

  26. #26
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,007

    Re: average across worksheets without counting blank cells

    You miss the point:

    Values 1,2 3 Average=2

    Values 10,20 average =15

    Average of averages =(2 + 15) =17/2 =8.5

    Average of 1,2,3,10,20= 36/5 =7.2

    Extreme values just to illustrate the maths

  27. #27
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    6,266

    Re: average across worksheets without counting blank cells

    John, the ranges in the example that you shared are not the same size.

    {1,2,3} = length of 3
    {10,20} = length of 2

    If they both had a length of 3, for example, then the average of the averages will be equal to the average of all of the numbers at once.

    e.g. {1,2,3} and {10,20,30}

    Average of {1,2,3} = 2
    Average of {10,20,30} = 20
    Average of 2 and 20 = 11

    Average of {1,2,3,10,20,30} = 11

  28. #28
    Forum Expert leelnich's Avatar
    Join Date
    03-20-2017
    Location
    Delaware, USA
    MS-Off Ver
    Office 2016
    Posts
    2,807

    Re: average across worksheets without counting blank cells

    Quote Originally Posted by JohnTopley View Post
    NOTE: I believe mathematically the Quarterly. figures are incorrect as you are averaging AVERAGES.
    The correct way is to average ALL (raw) data for a quarter period.
    Quote Originally Posted by 63falcondude View Post
    As long as the ranges are the same size (didn't check to see if this was the case), you can take an average of averages.
    @63falcondude, I believe John is correct. The physical ranges could be the same size, but if you're ignoring blanks, the actual number of entries per month could vary.

    Oct Nov Dec Tot
    SUM
    80
    35
    35
    150
    150/20 = 7.5
    COUNT
    10
    5
    5
    20
    AVG
    8
    7
    7
    22
    22/3 = 7.3
    Last edited by leelnich; 11-20-2017 at 01:36 PM.

  29. #29
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    6,266

    Re: average across worksheets without counting blank cells

    leelnich, yes that makes sense. If we are ignoring blanks then the size of the ranges that are being averaged could be different thus making the average of averages not correct.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Counting blank cells up to first non-blank cell
    By DLG3 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 12-08-2015, 07:36 AM
  2. Replies: 9
    Last Post: 08-17-2015, 04:05 PM
  3. Average without counting not used cells?
    By Vihral in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 07-23-2013, 09:08 AM
  4. Replies: 10
    Last Post: 04-25-2013, 06:12 PM
  5. Find a blank cell and calculate the average of all cells above till the next blank
    By joshnathan in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-27-2012, 11:51 AM
  6. Replies: 1
    Last Post: 02-02-2012, 09:42 AM
  7. Replies: 0
    Last Post: 08-23-2005, 03:43 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1