Excluding a row according to "if" criteria

gwstat001 · Sep 25, 2011

Hi

I have two columns of data with 50 numbers each ranging between 0 and 100 randomly placed in each column. i want to work out the average of each column but also I would like a command that says "if the second column contains a row number greater than 50, then exclude the entire row across the two columns" when caluclating the average. i.e if B2 = 67, then exclude A2 and B2 when working out the average of column A and B.

T. Valko · Sep 25, 2011

gwstat001 said:
Hi

I have two columns of data with 50 numbers each ranging between 0 and 100 randomly placed in each column. i want to work out the average of each column but also I would like a command that says "if the second column contains a row number greater than 50, then exclude the entire row across the two columns" when caluclating the average. i.e if B2 = 67, then exclude A2 and B2 when working out the average of column A and B.

Try this array formula**:

=AVERAGE(IF(A1:A50<=50,IF(B1:B50<=50,A1:B50)))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Assuming no empty cells within the data range.

T. Valko · Sep 25, 2011

T. Valko said:
Try this array formula**:

=AVERAGE(IF(A1:A50<=50,IF(B1:B50<=50,A1:B50)))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Assuming no empty cells within the data range.

After reading your post again I'm not sure whether you want the average of the COMBINED columns or the average of each separate column.

My first reply will return the COMBINED average.

Use these for the SEPARATE averages.

Both are still array entered**:

For column A:

=AVERAGE(IF(A1:A50<=50,IF(B1:B50<=50,A1:A50)))

For column B:

=AVERAGE(IF(A1:A50<=50,IF(B1:B50<=50,B1:B50)))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

T. Valko · Sep 25, 2011

T. Valko said:
After reading your post again I'm not sure whether you want the average of the COMBINED columns or the average of each separate column.

My first reply will return the COMBINED average.

Use these for the SEPARATE averages.

Both are still array entered**:

For column A:

=AVERAGE(IF(A1:A50<=50,IF(B1:B50<=50,A1:A50)))

For column B:

=AVERAGE(IF(A1:A50<=50,IF(B1:B50<=50,B1:B50)))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

And, if you happen to be using Excel 2007 or later you can use these normally entered formulas for the separate averages.

For column A:

=AVERAGEIFS(A1:A50,A1:A50,"<=50",B1:B50,"<=50")

For column B:

=AVERAGEIFS(B1:B50,A1:A50,"<=50",B1:B50,"<=50")

gwstat001 · Sep 25, 2011

Thanks. Works like a charm. ok so that solves half the problem.

What if now there is a 3rd column (column C), and this time if one of the numbers in column C is greater than 50, then the corresponding rows of A and B must be excluded from the calculation of the SUMPRODUCT of column A and column B.

T. Valko · Sep 25, 2011

gwstat001 said:
Thanks. Works like a charm. ok so that solves half the problem.

What if now there is a 3rd column (column C), and this time if one of the numbers in column C is greater than 50, then the corresponding rows of A and B must be excluded from the calculation of the average of just column A and column B, so column C is not included in the average calculation.

What version of Excel are you using?

For this last question...

Do you want the COMBINED average of columns A and B when the corresponding cell in column C is less than or equal to 50?

If this was your data then average only the cells highlighted in green:

Book1

A

B

C

1

10

46

7

2

33

38

91

3

88

75

4

43

82

92

5

36

17

6

20

88

7

93

69

68

8

69

21

28

9

37

49

53

10

51

81

60

Sheet1

Is that what you want?

gwstat001 · Sep 25, 2011

Sorry. I phrased the question wrong. What I meant to say is: 

I have three columns of data with 50 numbers each ranging between 0 and 100 randomly placed in each column. i want to work out the SUMPRODUCT of column A and B but also I would like a command that says "if column C contains a row number greater than 50, then exclude the entire row across the columns A and B" when caluclating the SUMPRODUCT. i.e if C2 = 67, then exclude the product of A2 and B2 when working out the SUMPRODUCT of column A and B.

So in your case get SUMPRODUCT of (A1:B1, A5:B5, A8:B8)

T. Valko · Sep 25, 2011

gwstat001 said:
Sorry. I phrased the question wrong. What I meant to say is:

I have three columns of data with 50 numbers each ranging between 0 and 100 randomly placed in each column. i want to work out the SUMPRODUCT of column A and B but also I would like a command that says "if column C contains a row number greater than 50, then exclude the entire row across the columns A and B" when caluclating the SUMPRODUCT. i.e if C2 = 67, then exclude the product of A2 and B2 when working out the SUMPRODUCT of column A and B.

So in your case get SUMPRODUCT of (A1:B1, A5:B5, A8:B8)

See my reply in your other post on that subject.

gwstat001 · Sep 25, 2011

THANX!!!! it works!!!!

T. Valko · Sep 25, 2011

gwstat001 said:
THANX!!!! it works!!!!

Good deal. Thanks for the feedback!

Excluding a row according to "if" criteria

gwstat001

New Member

Excel Facts

T. Valko

Well-known Member

T. Valko

Well-known Member

T. Valko

Well-known Member

gwstat001

New Member

T. Valko

Well-known Member

gwstat001

New Member

T. Valko

Well-known Member

gwstat001

New Member

T. Valko

Well-known Member

Similar threads

Forum statistics

Share this page

Excluding a row according to "if" criteria

New Member

Excel Facts

Well-known Member

Well-known Member

Well-known Member

New Member

Well-known Member

New Member

Well-known Member

New Member

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock