averaging info from three columns

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

AAS - 29 May 2008 14:43 GMT

Still not quite sure what is going wrong..

=AVERAGE(IF(('Production Calendar'!C11:C97="Memco")*('Production
Calendar'!D11:D97="SMF"),'Production Calendar'!G11:G97))

I'm trying this formula but its not working. I'm trying to average the wages
from column G11:G97 that are Memco column C11:C97 and SMF column D11:D97

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

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

Reply to this Message

Mike H - 29 May 2008 14:53 GMT

Try

=AVERAGE(IF(C11:C97="Memco",IF(D11:D97="SMF",G11:G97)))

Which is an array so commit with
Ctrl+Shift+Enter

Mike

> Still not quite sure what is going wrong..
>
[quoted text clipped - 41 lines]
> >
> > The answer would be $15

Reply to this Message

Max - 29 May 2008 15:15 GMT

You never gave our responses a chance. Stick to your original thread

> =AVERAGE(IF(('Production Calendar'!C11:C97="Memco")*('Production
> Calendar'!D11:D97="SMF"),'Production Calendar'!G11:G97))

Your adaptation of the suggestion above seems ok.
But did you remember to "array-enter" it, as per my earlier line:
"..*Press CTRL+SHIFT+ENTER to confirm the formula"

Signature

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

Reply to this Message

AAS - 29 May 2008 16:19 GMT

I was not sure if original posts were kept up with, sorry about that. I have
been array-entering two different formulas but am not coming up with a number

=AVERAGE(IF(('Production Calendar'!C11:C105="Memco")*('Production
Calendar'!D11:D105="SMF"),'Production Calendar'!G11:G105))

=AVERAGE(IF('Production Calendar'!C11:C97="Memco",IF('Production
Calendar'!D11:D97="SMF",'Production Calendar'!G11:G97)))

Thanks alot for the advice.

> You never gave our responses a chance. Stick to your original thread
>
[quoted text clipped - 4 lines]
> But did you remember to "array-enter" it, as per my earlier line:
> "..*Press CTRL+SHIFT+ENTER to confirm the formula"

Reply to this Message

AAS - 29 May 2008 17:50 GMT

=SUMIF('Production Calendar'!D10:D104,"SMF",'Production
Calendar'!G10:G105)/COUNTIF('Production Calendar'!D10:D104,"SMF")

Using this formula I was able to get the average wage of all the SMF
employees but I need a formula that will give me all the SMF and Memco
employees average wage.

Thanks for the help guys

> Production Calendar (sheet 1)
>
[quoted text clipped - 8 lines]
>
> The answer would be $15

Therefore if someone is added to sheet 1 their wage would be calculated into
sheet 2's formula and therefore automatically updating sheet 2

> I was not sure if original posts were kept up with, sorry about that. I have
> been array-entering two different formulas but am not coming up with a number
[quoted text clipped - 15 lines]
> > But did you remember to "array-enter" it, as per my earlier line:
> > "..*Press CTRL+SHIFT+ENTER to confirm the formula"

Reply to this Message

Max - 29 May 2008 21:35 GMT

> =SUMIF(...G105)/COUNTIF(..

G104 you mean. You gotta be careful to be consistent with the ranges

Anyway, here's a quick sample which shows
the earlier array-entered average working properly:
http://www.freefilehosting.net/download/3hlc8
Conditional Averaging.xls

If you still get discrepancies, then the source data is probably
inconsistent, with extra white spaces here & there throwing things off. You
could deploy TRIM directly within the conditional average expression to clear
it up, like this (remember, array-entered):
=AVERAGE(IF((TRIM('Production Calendar'!C11:C105)="Memco")*(TRIM('Production
Calendar'!D11:D105)="SMF"),'Production Calendar'!G11:G105))

Signature

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

Reply to this Message