Average+If+Offset

[nicolehalliday]

Hi,
I am trying to find the average value of some cells in a range, only when the cell 6 columns to the left (same row) meets a condition. The condition is that the cell 6 columns left is a specific number.

K4:K103 is the range from which I would like to pull the values and then average them. E4:E103 (the numbers range from 1-11) is the range of numbers that must match the condition, say, only take the average of cells that when offset by 6 to the left, is 1. If that sounds confusing, I want to average all of the values in column K if in the same row, but column E the value is equal to 1. Once I find this out I can modify it for each # from 1-11.

I'm not really sure how to approach this. Any help would be much appreciated!

 

[Cease]

Hey,
I'm not sure if this is the best answer, but it works for me:

=averageif($E$4:$E$103,1,$K$4:$K103)

You can also do AverageIfs, or an array formula to check the same range for more criteria.

Hope this helps :D

 

[nicolehalliday]

Thanks for the help! Unfortuantly the formula returns #NAME?

When I begin typing in =averageif there is no autocomplete to show what the formula contains, which leads me to think that this function might not be available in Word 2003?

Any other ideas?? Thank you!

 

[Aladin Akyurek]

Thanks for the help! Unfortuantly the formula returns #NAME?

When I begin typing in =averageif there is no autocomplete to show what the formula contains, which leads me to think that this function might not be available in Word 2003?

Any other ideas?? Thank you!

Try...

=SUMIF($E$4:$E$103,1,$K$4:$K103)/COUNTIF($E$4:$E$103,1)

 

[T. Valko]

Hi,
I am trying to find the average value of some cells in a range, only when the cell 6 columns to the left (same row) meets a condition. The condition is that the cell 6 columns left is a specific number.

K4:K103 is the range from which I would like to pull the values and then average them. E4:E103 (the numbers range from 1-11) is the range of numbers that must match the condition, say, only take the average of cells that when offset by 6 to the left, is 1. If that sounds confusing, I want to average all of the values in column K if in the same row, but column E the value is equal to 1. Once I find this out I can modify it for each # from 1-11.

I'm not really sure how to approach this. Any help would be much appreciated!

Try this array formula**.

=AVERAGE(IF(E4:E103=1,K4:K103))

** 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.

When I begin typing in =averageif there is no autocomplete to show what the formula contains, which leads me to think that this function might not be available

The AVERAGEIF function is only available in Excel versions 2007 and later. If you try to use it in earlier versions then you'll get the #NAME? error.