Results 1 to 3 of 3

Combine sum, offset, and countif functions

Threaded View

cbarr1987 Combine sum, offset, and... 02-23-2012, 06:55 PM
rylo Re: Combine sum, offset, and... 02-23-2012, 07:11 PM
cbarr1987 Re: Combine sum, offset, and... 02-24-2012, 11:32 AM
  1. #1
    Registered User
    Join Date
    02-23-2012
    Location
    Austin, Texas
    MS-Off Ver
    Excel 2010
    Posts
    7

    Lightbulb Combine sum, offset, and countif functions

    Howdy,

    I have a range of single column data, and I need a formula that will calculate the average of only the last four entries that also ignores zero's and blank cells. Please see below and attachment for additional information:

    Example (four blank cells in the range below the zero's):

    2
    9
    3
    4
    7
    8
    3
    0
    0
    0
    0



    If this is my data set, I need a formula to calculate the average of 4, 7, 8, and 3 only. The idea here is that when that first zero underneath the 3 becomes a number, lets say a 10- that the formula will then return the average for: 10,3, 8, 7.

    Here is where I've gotten with my formula: =((SUM(A1:A25))/(OFFSET(A1,COUNTIF(A1:A25,">0")-4,0,4,1)))
    Attached Files Attached Files

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