DAVERAGE

[AAS]

trying to average a column of numbers but only the numbers which are memco
from column B and SMF from column C

I was told DAVERAGE is the way to go but I can't get the formula right

 

[Mike H]

Hi,

You've been given answers that work in your other posts.

her
http://www.microsoft.com/office/com...b79a&cid=40c88541-76a8-45b8-99c6-26b8db68b10f

and her
http://www.microsoft.com/office/com...0ea7&cid=40c88541-76a8-45b8-99c6-26b8db68b10f

Mike

 

[AAS]

yes i have been given answers and im sure they should be working. if you
could help me figure it out that would be great. i have been using the formula


=AVERAGE(IF((Production Calendar!C11:C105="Memco")*(Production
Calendar!D11105="SMM"),Production Calendar!G11:G105))

- and pressing ctrl shift enter simultaneously but it has not worked.

a few times a window will appear that says update values: calendar

 

[Mike H]

If that formula isn't working and it's entered properly then the problem is
data,
highlight each part of the formula (as below) in the formula bar and tap F9,
you may need to shorten the range a bit and you will see if it is evaluating
TRUE or FALSE. If it's false and you think it should be true then you have a
data issue

(Production Calendar!C11:C20="Memco")
(Production Calendar!D1120="SMM")
Click the red cross after checking each part

If they are evaluating correctly then check your numbers. Are they really
numbers?

Mike

yes i have been given answers and im sure they should be working. if you
could help me figure it out that would be great. i have been using the formula

=AVERAGE(IF((Production Calendar!C11:C105="Memco")*(Production
Calendar!D11105="SMM"),Production Calendar!G11:G105))

 

[AAS]

i'm really not sure. After doing that the line goes to #name?.
=AVERAGE(IF((Production Calendar!C11:C105="Memco")*(Production
Calendar!D11105="SMM"),Production Calendar!G11:G105))

i would just like all the Memco employees from column C that are ALSO SMM
employees from column D to average their hourly wage rates from column G.
They have Memco and SMM in common.

-to activate the array enter all i have to do is press ctrl shift enter
simultaneously, correct?


john doe Memco SMF $20
joe garcia AAS SMH $13
jessie nelson Memco SMF $10
david toms Memco SMM $ 9

Thanks alot for the help

 

[T. Valko]

a few times a window will appear that says update values: calendar

That's because it's looking for a sheet named Calendar but it doesn't exist.

Try this (still pressing ctrl shift enter simultaneously):

=AVERAGE(IF(('Production Calendar'!C11:C105="Memco")*('Production
Calendar'!D11105="SMM"),'Production Calendar'!G11:G105))

Notice the single quotes around the sheet name. When a sheet name contains
spaces or is a number you must use these quotes.

 

[AAS]

thanks alot that did the job...also my keyboard was having trouble when i
would array enter. i have to do it a few times before it kicks in. thanks
again

 

[T. Valko]

Good deal!

BTW, "pressing ctrl shift enter simultaneously" is sort of misleading. You
don't have to do it simultaneously.

Hold down both the CTRL key and the SHIFT key then hit ENTER.