+ Reply to Thread
Results 1 to 6 of 6

Formula Inquiry

  1. #1
    Valued Forum Contributor
    Join Date
    03-17-2007
    Location
    Michigan
    MS-Off Ver
    Excel 2016
    Posts
    951

    Formula Inquiry

    Anyone have any suggestions how I might be able to do the following:
    I'm have a column "K3 thru K24" which depicts data ranging from zero to 60, etc. What I'd like to do is be able to take the data and only obtain the average of the data of anything greater than zero. I don't want anyone with zero's to be averaged into the formula and I need this formula to adjust if I filter my data as well. I hope that makes sense. I'd appreciate anyone's advice or tips!

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

    =AVERAGE(IF(K3:K24,K3:K24))

    confirmed with CTRL+SHIFT+ENTER

    or

    =SUM(K3:K24)/MAX(1,COUNTIF(K3:K24,">0"))

    the second one also avoids #DIV/0! errors if you have no numbers in the range above zero

  3. #3
    Valued Forum Contributor
    Join Date
    03-17-2007
    Location
    Michigan
    MS-Off Ver
    Excel 2016
    Posts
    951

    Not working when I filter data

    I've attached a file of what I'm trying to do.. Thanks for your help!!
    Attached Files Attached Files

  4. #4
    Forum Expert mikerickson's Avatar
    Join Date
    03-30-2007
    Location
    Davis CA
    MS-Off Ver
    Excel 2011
    Posts
    6,229
    =SUMIF(K3:K24,"<>0",K3:K24)/COUNTIF(K3:K24,"<>0")

    Should do it.

  5. #5
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675
    OK, so averaging only visible data after filtering, including only numbers greater than zero....try

    =SUMPRODUCT(--(B3:B24>0),SUBTOTAL(9,OFFSET(B3:B24,ROW(B3:B24)-ROW(B3),0,1)))/MAX(1,SUMPRODUCT(--(B3:B24>0),SUBTOTAL(3,OFFSET(B3:B24,ROW(B3:B24)-ROW(B3),0,1))))

  6. #6
    Valued Forum Contributor
    Join Date
    03-17-2007
    Location
    Michigan
    MS-Off Ver
    Excel 2016
    Posts
    951

    Thanks for all the help!

    I really appreciate the help that everyone has given these past few days!

    I am curious is it possible to use conditional formatting to change the background of unknown data. For instance with this problem, I'm developing a point scale 1 thru 5-each point has a different color. Depending on the average result, which will automatically get 3 points, I'm assigning 2 points for 10% less than average, 1 point for 20% less than average and visa versa, 4 points for 10% above average and 5 points for 20% above average. Even though until all the data is entered, is there a way to have my cells color code themselves depending on where each falls depending on where the "average" data falls, from whatever data is entered. I hope this makes sense...
    Last edited by lilsnoop; 04-02-2007 at 10:39 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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