Results 1 to 2 of 2

Thread: column average

  1. #1
    Join Date
    Apr 2004
    Location
    Chicago
    Posts
    1

    Talking Unanswered: column average

    I have a column I want to average. It always starts at C12, but the column length is dependent on the input data file. At the moment, I use a name defined as follows to refer to the column data.

    STRAP
    =OFFSET('Input Data'!$D$12,0,0,COUNTA('Input Data'!$D:$D)-2,1)

    Then of course I just average strap for the data I require. The problem I have is I want to disqualify certain cells with values much greater than the majority before the average is taken. An example I would have is below.

    .1
    .1
    .2
    1.2
    .1
    .1
    1.2
    .1
    .1
    .1

    I want to throw out the 1.2s and only average the .1s and .2s.
    Any ideas?
    Thank you.

  2. #2
    Join Date
    Oct 2003
    Posts
    1,091
    Try something like this:

    =(SUMIF(B2:B10,"<0.25",B2:B10))/COUNTIF(B2:B10,"<0.25")

    This sets the upper limit at 0.25. Adjust as needed.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

Posting Permissions

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