# average if references a cell

#### jmazorra

##### Well-known Member
Hello everyone:

I have the following formula:

=AVERAGE(IF('All Employee Hourly'!\$D\$6:\$D\$24986 =E36,'All Employee Hourly'!\$L\$6:\$L\$24986),0)

the formula looks a cell E36 and is supposed to return an average of column L from all cells that match value of E36. The problem is that is not returning the correct value. I tested the formula, and one of the values only repeats once on the referenced table so it should have returned the exact value, however, it did not.

I am missing something?

### Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
Hello everyone:

I have the following formula:

=AVERAGE(IF('All Employee Hourly'!\$D\$6:\$D\$24986 =E36,'All Employee Hourly'!\$L\$6:\$L\$24986),0)

the formula looks a cell E36 and is supposed to return an average of column L from all cells that match value of E36. The problem is that is not returning the correct value. I tested the formula, and one of the values only repeats once on the referenced table so it should have returned the exact value, however, it did not.

I am missing something?
With so many rows, are you certain the E36 value only appears once?
Did you use a COUNTIF('All Employee Hourly'!\$D\$6:\$D\$24986,E36) to verify only one instance?

Thanks, I used that one particular reference as a unique value to verify that my formula worked, and it did not.

Thanks, I used that one particular reference as a unique value to verify that my formula worked, and it did not.
Is your formula returning an error like #VALUE!?, and just to be certain did you try the countif to verify only one instance of E36 in the range?

It actually returns a value of 6.98. However, since there is only one occurence on my reference table it should return the value of 13.96 but instead it returns 6.98, which is exactly half of the number.

I ran your countif formula again, and it returns 1, i can't understand why is thinking there is more?

Hello everyone:

I have the following formula:

=AVERAGE(IF('All Employee Hourly'!\$D\$6:\$D\$24986 =E36,'All Employee Hourly'!\$L\$6:\$L\$24986),0)

the formula looks a cell E36 and is supposed to return an average of column L from all cells that match value of E36. The problem is that is not returning the correct value. I tested the formula, and one of the values only repeats once on the referenced table so it should have returned the exact value, however, it did not.

I am missing something?

The formula must be confirmed with control+shift+enter, not just enter.

Also, are you sure you need that 0 in the formula? If no, the formula would be:

=AVERAGE(IF('All Employee Hourly'!\$D\$6:\$D\$24986 =E36,'All Employee Hourly'!\$L\$6:\$L\$24986))

This is equivalent on all versions to:

=SUMIF('All Employee Hourly'!\$D\$6:\$D\$24986,E36,'All Employee Hourly'!\$L\$6:\$L\$24986)/COUNTIF('All Employee Hourly'!\$D\$6:\$D\$24986,E36)

and on Excel 2007 or later to:

=AVERAGEIF('All Employee Hourly'!\$D\$6:\$D\$24986,E36,'All Employee Hourly'!\$L\$6:\$L\$24986)

each of which just needs enter.

Thanks, it was the 0 that was causing the problem.

Thanks, it was the 0 that was causing the problem.

You are welcome. Thanks for providing feedback.

Replies
7
Views
110
Replies
0
Views
134
Replies
3
Views
144
Replies
3
Views
119
Replies
3
Views
263

1,203,102
Messages
6,053,538
Members
444,670
Latest member
laurenmjones1111

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back