+ Reply to Thread
Results 1 to 5 of 5

Working out an average across multiple non concurrent cells excluding blanks and zeros

  1. #1
    Registered User
    Join Date
    03-20-2014
    Location
    London, England
    MS-Off Ver
    Excel 2007
    Posts
    48

    Working out an average across multiple non concurrent cells excluding blanks and zeros

    Hi again guys,

    I'm still working on my tracker spread sheet that some of you kindly helped with yesterday but I've got stuck again.

    I have now populated all of the data into the cells using the 'Data Dump' tab but I now need to work out how to calculate averages for the individuals 'talk time' in the 'Overall' tab excluding the blanks (which are days when they were not in) and the '0.00%' cells which are for future dates that are yet to be input.

    I've tried a few things myself and from looking around on this site (and some others) but none of the formulas work.
    Attached Files Attached Files

  2. #2
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,904

    Re: Working out an average across multiple non concurrent cells excluding blanks and zeros

    See the attached file

    Data was taken from Data Dump instead of TAM, HAM
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    03-20-2014
    Location
    London, England
    MS-Off Ver
    Excel 2007
    Posts
    48

    Re: Working out an average across multiple non concurrent cells excluding blanks and zeros

    perfect, I didn't actually think of taking the average from the 'data dump' as previously when working out averages I've only ever known to drag a table out against an =AVERAGE formula.

    Thanks very much!!

  4. #4
    Registered User
    Join Date
    03-20-2014
    Location
    London, England
    MS-Off Ver
    Excel 2007
    Posts
    48

    Re: Working out an average across multiple non concurrent cells excluding blanks and zeros

    just one question though, will this update when I paste in new data tomorrow and so on? I need to be able to paste in fresh data each day that will amend the average in the 'Overall' tab for each agent
    Last edited by Streaky; 06-11-2014 at 07:10 AM.

  5. #5
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,904

    Re: Working out an average across multiple non concurrent cells excluding blanks and zeros

    Yes, it will work but you have to extend the Range in the Formula (Ex: Change 699 as 1000 or 12000 etc according to ur need)

+ 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. average column excluding zeros?
    By Willardio in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-21-2013, 08:40 PM
  2. [SOLVED] Average for non-consecutive cells excluding zeros (even when they all contain zeros)
    By pao13 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-05-2012, 04:32 PM
  3. Average Random cells excluding zeros
    By shnybltz in forum Excel General
    Replies: 3
    Last Post: 02-10-2012, 11:08 AM
  4. average over worksheets excluding zeros
    By sccottt in forum Excel General
    Replies: 16
    Last Post: 12-18-2009, 01:13 PM
  5. Calculating the Average of Every Nth Value, Excluding Zeros
    By tsaravan in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-31-2006, 06:24 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