average if references a cell

jmazorra

Well-known Member
Joined
Mar 19, 2011
Messages
715
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

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
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?
 
Upvote 0
Thanks, I used that one particular reference as a unique value to verify that my formula worked, and it did not.
 
Upvote 0
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?
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,308
Messages
6,171,330
Members
452,396
Latest member
ajl_ahmed

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?

Disable AdBlock

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
Back
Top