averaging info from three columns
 

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
--
Dilip Kumar Pandey
MBA, BCA, B.Com(Hons.)


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

averaging info from three columns
 

Try

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

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

Mike

"AAS" wrote:

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
--
Dilip Kumar Pandey
MBA, BCA, B.Com(Hons.)


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

averaging info from three columns
 

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"
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

averaging info from three columns
 

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.



"Max" wrote:

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"
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

averaging info from three columns
 

=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)

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


Wage Summary (sheet 2)

average wage of a Memco, SMF employee formula on sheet 2


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








"AAS" wrote:

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.



"Max" wrote:

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"
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

averaging info from three columns
 

=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))
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---