if statement with an average condition 1

[553]

I have a worksheet (Employee Detail)which contains hours per employee. Each employee belongs to a specific region. I was trying to retrieve on different worksheet called sheet8 within the same workbook the averages per region using the following:
=IF('Employee Detail'!$Q:$Q=Sheet8!B2,AVERAGE('Employee Detail'!$O:$O))
Where Employee Detail Q is the Region Column
Where Sheet 8 B@ contains the region number
Where Employee Detail ) is the hours to avaerage for the specific region.
I am getting the average of all the regions instead of just the specific region I am caling out on sheet8 B2. Should I be using a different formula?

I need to formulas, one that accounts for 0 values and one that did not.



I am on Excel 2003 SPI

Thanks!

 

[firefytr]

Hi there,

Use something like this ...


=AVERAGE(IF(('Employee Detail'!$Q:$Q=Sheet8!B2)*(ISNUMBER('Employee Detail'!$Q:$Q)),'Employee Detail'!$O:$O))


Confirm with Ctrl + Shift + Enter instead of just Enter.

HTH

-----------
Regards,
Zack Barresse

 

[con10t]

Hi! I'm working with Excel 2002 and have a similar situation as 553, but instead of looking up a number, it looks up text. I'm wanting to find the average scores of different groups. The following formula is trying to give me the average of the test scores of individuals coded as Engineering.

I've adapted firefytr's solution as follows:

{=AVERAGE(IF((Data!H7:H634="Engineering")*(ISTEXT(Data!H7:H634)), Data!N7:N634))}

It has been entered as an array formula (Control-Shift-Enter), but the result is way off. In fact, I just experimented and
=AVERAGE(IF((Data!H7:H634="Engineering"), Data!N7:N634))

gives me the same result. So I'm not sure what I should do.

H7:H634 is the Code (each cell has a drop down list of different job names, with one name selected)
N7:N634 is the score they received.

My result has gone from 50.9 (the column's average) to 2.3
The correct number should be 43.3. Any ideas?

Thanks!

 

[AnotherHiggins]

Please create a new thread. The original poster's question was answered by Zack.

Your question will get more visibility if it is on it's own.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.