averaging formula from three cells

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

AAS - 28 May 2008 22:26 GMT

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

Reply to this Message

Max - 29 May 2008 00:01 GMT

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

Signature

Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

> i must make a formula to average a column of numbers but only the numbers
> which are narrowed down from two other columns.
[quoted text clipped - 13 lines]
>
> The answer would be $15

Reply to this Message

DILipandey - 29 May 2008 10:03 GMT

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

Signature

Dilip Kumar Pandey
MBA, BCA, B.Com(Hons.)
dilipandey@yahoo.com
dilipandey@gmail.com
New Delhi, India

> i must make a formula to average a column of numbers but only the numbers
> which are narrowed down from two other columns.
[quoted text clipped - 13 lines]
>
> The answer would be $15

Reply to this Message