i must make a formula to average a column of numbers but only the numbers
which are narrowed down from two other columns.

for example:

Production Calendar (sheet 1)

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


Wage Summary (sheet 2)

average wage of a Memco, SMF employee


The answer would be $15

In Sheet2, array-enter* something like this:
=AVERAGE(IF((Sheet1!B2:B5="Memco")*(Sheet1!C2:C5=" SMF"),Sheet1!D2:D5))
*Press CTRL+SHIFT+ENTER to confirm the formula
Adapt the ranges to suit
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"AAS" wrote:
i must make a formula to average a column of numbers but only the numbers
which are narrowed down from two other columns.

for example:

Production Calendar (sheet 1)

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


Wage Summary (sheet 2)

average wage of a Memco, SMF employee


The answer would be $15

Hi,

You can use the function DAVERAGE to get the average wage of Memco,SMF
employees. I have tried it and got the same result i.e. $15.
following is pasted in columns A to E
Name1 Name2 Deptt code Wage
john doe Memco SMF $20
joe garcia $13
jessie nelson Memco SMF $10
david toms Memco SMM $9
Following criteria is given in column H to L
Name1 Name1 Deptt code Wage
Memco SMF

Now you can use the following formulae:-
=DAVERAGE(A1:E5,E1,H1:L2)
and it will give you the desired result $15.
Please let me know if this helps.

Thanks
--
Dilip Kumar Pandey
MBA, BCA, B.Com(Hons.)


New Delhi, India


"AAS" wrote:

i must make a formula to average a column of numbers but only the numbers
which are narrowed down from two other columns.

for example:

Production Calendar (sheet 1)

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


Wage Summary (sheet 2)

average wage of a Memco, SMF employee


The answer would be $15