Averaging numbers in query.

D

Duser

I am currently pulling a query into Microsoft Excel and I need to create a
function that averages all cells from a specific month. So i need to have
one cell that searchs all the queried data and pulls every number for a
specific plant and month in june and then averages all the numbers it finds.

For example below... I could just say =averge(B1:B5) except the range will
increase every month, and then every year. So I am looking for a function
that says look for TMMKV6 Engine Assy for every month 11 regardless of the
year. Each month the query will run the averages for the new month should
change and the old one's will only change once every year.

A B
TMMKV6 Engine Assy112005 0.86621058
TMMKV6 Engine Assy112004 0.850701809
TMMKV6 Engine Assy112003 0.805216908
TMMKV6 Engine Assy112002 0.805999994

Thank you for your help.
 
D

Dave Peterson

=average(if(left(a1:a99,20)="TMMKV6 Engine Assy11",b1:b99)

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Adjust the range to match--but you can't use the whole column.
 
B

Bob Phillips

plus another trailing bracket

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)
 
D

Dave Peterson

Oopsie.

Thanks for the correction.

Bob said:
plus another trailing bracket

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)
Click to expand...
 
Top