Excel 2003 Average based on selected criteria

[poporacer]

I have a spreadsheet that has several columns. The fields are Elig, Mon, Tue, Wed, Thur, Fri, TotHrs.
Elig has a formula to determine if this entry is eligible to be included in the calculations, if it is, the cell is populated with "Y". Mon-Fri is used to input the number of hours. TotHrs is used to total the hours worked. I need to calculate the average number of hours worked based upon the eligibility. What would be the best method to do this? I am not sure if you can use the AVERAGE function and exclude certain rows, or if you have to do it in a couple of steps.
Thanks for your assistance!

 

[SkipVought]

Hi,

Please post an illustrative example AND the expected results based on that example.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Kurupt55]

I would make the formula in the elig column return either the value that you want the average of or ""
=IF(your formula,G2,"")
Then if you average this column you will get the average hours if the row is eligible.

Impossible is Nothing

 

[Loomah]

hi
there are likely to be more elegant ways but this will do the job of aclculating the average where you have "y"

=SUMIF(A1:A30,"y",B1:B30)/COUNTIF(A1:A30,"y")

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[poporacer]

Thanks a billion!!! Loomah that was perfect. I was out of town for a bit, so I haven't had time to check back.