+ Reply to Thread
Results 1 to 15 of 15

Average of a column with criteria

  1. #1
    Forum Contributor
    Join Date
    10-18-2019
    Location
    Mumbai
    MS-Off Ver
    OFFICE 2010
    Posts
    427

    Post Average of a column with criteria

    C2:C11 contains numerical numbers either 0 or a +ve number
    In this above range i.e. C2:C11, a series of cells contains 'same' numbers (in the example: C6:C11)

    Output cell: F2=Average of above numbers (here average of C2:C5)

    Note: IF C10 (2nd last number) is not equal to C11 (last number), then F2=Average of C2:C11
    IF any of C2:C11='0' then F2=0
    How to accomplish?
    Thanks in advance
    Attached Files Attached Files

  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
    27,991

    Re: Average of a column with criteria

    Try

    =IF(COUNTIF($C$2:$C$11,0),0,IF($C$10<>$C$11,AVERAGE($C$2:$C$11),AVERAGE($C$2:$C$5)))

    However: is list of "same" numbers alwya the same number of rows?
    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

  3. #3
    Forum Contributor
    Join Date
    10-18-2019
    Location
    Mumbai
    MS-Off Ver
    OFFICE 2010
    Posts
    427
    Thanks John for your efforts
    There can be cases like C8:C11 are equal in values. Then I would like F2=Average(C2:C7)

    Moreover this is a sample. My real range is C2:C1000

    In every case the lower portion of the C column is equal in values...it can be C600:C1000 are equal in values..so F2=Average (C2:C599)

    It will never be C600:C800 values are 'different' than C801:C1000

  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
    27,991

    Re: Average of a column with criteria

    There can be cases like C8:C11 are equal in values. Then I would like F2=Average(C7:C11
    this does not seem to align with your first post: should it be F2=Average(C2:C7) ??

    And you also have condition where the penuultimate value does equal the last value: is it only this case, as there needs to be a way of testing for the range of "same" numbers.

    and finally are you still on Excel 2010: if not, please update your profile with your current Excel version..
    Last edited by JohnTopley; 11-24-2022 at 08:49 AM.

  5. #5
    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
    27,991

    Re: Average of a column with criteria

    Try

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Last edited by JohnTopley; 11-24-2022 at 09:30 AM.

  6. #6
    Forum Contributor
    Join Date
    10-18-2019
    Location
    Mumbai
    MS-Off Ver
    OFFICE 2010
    Posts
    427

    Re: Average of a column with criteria

    To make my case understandable:
    a column may be ‘broken’ in 2 parts for understanding purpose
    Part 1: Upper portion where cells consists values
    Part 2: Lower portion…in this ALL values would be EQUAL.

    In my case, Part 2 WILL EXIST
    i.e. if C2:C1000 is range, then it can be values of C300:C1000 would be EQUAL
    i.e. ‘penultimate cell’ C1000 (if range is C2:C1000) will always appear in Part 2

    The crux is to identify the ‘penultimate’ cell of Part 1 and then calculate the required AVERAGE OF Part 1

    I immediately tried using your formula, but it missed the ‘goal’….
    Last edited by bittubadri; 11-24-2022 at 10:28 AM.

  7. #7
    Forum Contributor
    Join Date
    10-18-2019
    Location
    Mumbai
    MS-Off Ver
    OFFICE 2010
    Posts
    427

    Re: Average of a column with criteria

    Please allow me to add a 'helper' column B2:B11.
    So finally, F2=Average of corresponding cells from C2:C11 for which the analogous cell values from B2:B11 is 4.

  8. #8
    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
    27,991

    Re: Average of a column with criteria

    Post a much more representative file as the formula I gave worked on the small file you posted: saying it "missed the goal" is not very helpful without the data
    Last edited by JohnTopley; 11-24-2022 at 12:05 PM.

  9. #9
    Forum Expert
    Join Date
    09-25-2015
    Location
    Milan Italy
    MS-Off Ver
    office 365
    Posts
    1,775

    Re: Average of a column with criteria

    F5=AVERAGE(IF(COUNTIF(C2:C100,C2:C100)=1,C2:C100))

    Control+shift+enter

  10. #10
    Forum Contributor
    Join Date
    10-18-2019
    Location
    Mumbai
    MS-Off Ver
    OFFICE 2010
    Posts
    427

    Re: Average of a column with criteria

    file attached
    Attached Files Attached Files

  11. #11
    Forum Expert
    Join Date
    01-25-2011
    Location
    Belgium, Alveringem
    MS-Off Ver
    Excel 2003, 2007, 365
    Posts
    1,418

    Re: Average of a column with criteria

    file attached
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

  12. #12
    Forum Expert
    Join Date
    09-25-2015
    Location
    Milan Italy
    MS-Off Ver
    office 365
    Posts
    1,775

    Re: Average of a column with criteria

    G5=AVERAGE(IF(COUNTIF(C2:C100,C2:C100)=1,C2:C100))

    Control +shift+enter

  13. #13
    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
    27,991

    Re: Average of a column with criteria

    Try

    Formula: copy to clipboard
    Please Login or Register  to view this content.

  14. #14
    Forum Contributor
    Join Date
    10-18-2019
    Location
    Mumbai
    MS-Off Ver
    OFFICE 2010
    Posts
    427

    Re: Average of a column with criteria

    John...Your formula given in #13 works well. Still there is 1 criteria which this formula could not 'pass through'........please give some more time to satisfy myself please

  15. #15
    Registered User
    Join Date
    12-09-2022
    Location
    USA
    MS-Off Ver
    10
    Posts
    1

    Re: Average of a column with criteria

    this is great, thank you

+ 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. [SOLVED] How to calculate average of column B if criteria met in column a
    By yettie10ff in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 08-08-2017, 05:18 PM
  2. Replies: 4
    Last Post: 04-06-2017, 03:42 PM
  3. Replies: 2
    Last Post: 09-30-2015, 06:10 PM
  4. [SOLVED] Get average of some cells of a column If criteria in a different column with vba not worki
    By capson in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-12-2015, 09:47 AM
  5. [SOLVED] =Average a whole column but only if criteria is met ?
    By ryefield in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-01-2013, 02:41 AM
  6. Can you do a average with criteria from another column?
    By dredd2525 in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 04-24-2013, 04:36 AM
  7. Replies: 2
    Last Post: 05-12-2006, 01:10 PM

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