# Average of a column with criteria

1. ## 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?

2. ## 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?

3. 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. ## 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..

5. ## Re: Average of a column with criteria

Try

Formula:
`Please Login or Register  to view this content.`

6. ## 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’….

7. ## Re: Average of a column with criteria

So finally, F2=Average of corresponding cells from C2:C11 for which the analogous cell values from B2:B11 is 4.

8. ## 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

9. ## Re: Average of a column with criteria

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

Control+shift+enter

10. ## Re: Average of a column with criteria

file attached

11. ## Re: Average of a column with criteria

file attached
Formula:
`Please Login or Register  to view this content.`

12. ## Re: Average of a column with criteria

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

Control +shift+enter

13. ## Re: Average of a column with criteria

Try

Formula:
`Please Login or Register  to view this content.`

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

#### 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