| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
average with if and AND statements
Hi,
This array formula =AVERAGE(IF(F2:F190=B5,K2:K190)) works great for one if condition but Im trying to add one more AND statement to it like this: IF(F2:F190=B5) AND G2:G190=B5 than average K2:K190. I changed the above to the next array formula: =AVERAGE(IF(AND(F2:F190=A5,G2:G190=B5),K2:K190)) but it doesnt work. Does anybody know how to do this? |
|
#2
|
|||
|
|||
|
you only have one condition there,what happens if the average isnt equal to b5?
Any way this is what i would do,slightly different ranges,but =IF(AND(AVERAGE(A1:A5)=A6,AVERAGE(C1:C5)=A6),AVERA GE(C1:C5),"averages NOT eqaul A6") -- paul remove nospam for email addy! "Tim" wrote: Hi, This array formula =AVERAGE(IF(F2:F190=B5,K2:K190)) works great for one if condition but Im trying to add one more AND statement to it like this: IF(F2:F190=B5) AND G2:G190=B5 than average K2:K190. I changed the above to the next array formula: =AVERAGE(IF(AND(F2:F190=A5,G2:G190=B5),K2:K190)) but it doesnt work. Does anybody know how to do this? |
|
#3
|
|||
|
|||
|
Thank you for your answer Paul!
Sorry I have made a mistake in my question. Please read IF(F2:F190=A5) not IF(F2:F190=B5). So i need to get the average in column K2:K190 only for the cells which corresponding(offset) cells in Column F2:F190=A5 AND Column G2:G190=B5. "paul" wrote: you only have one condition there,what happens if the average isnt equal to b5? Any way this is what i would do,slightly different ranges,but =IF(AND(AVERAGE(A1:A5)=A6,AVERAGE(C1:C5)=A6),AVERA GE(C1:C5),"averages NOT eqaul A6") -- paul remove nospam for email addy! "Tim" wrote: Hi, This array formula =AVERAGE(IF(F2:F190=B5,K2:K190)) works great for one if condition but Im trying to add one more AND statement to it like this: IF(F2:F190=B5) AND G2:G190=B5 than average K2:K190. I changed the above to the next array formula: =AVERAGE(IF(AND(F2:F190=A5,G2:G190=B5),K2:K190)) but it doesnt work. Does anybody know how to do this? |
|
#4
|
|||
|
|||
|
"Tim" wrote:
... average in column K2:K190 only for the cells which corresponding cells in Column F2:F190=A5 AND Column G2:G190=B5 .. Try, array-entered (CTRL+SHIFT+ENTER): =AVERAGE(IF((F2:F190=A5)*(G2:G190=B5),K2:K190)) -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- |
|
#5
|
|||
|
|||
|
This one realy works.
Thank you Max! Tim "Max" wrote: "Tim" wrote: ... average in column K2:K190 only for the cells which corresponding cells in Column F2:F190=A5 AND Column G2:G190=B5 .. Try, array-entered (CTRL+SHIFT+ENTER): =AVERAGE(IF((F2:F190=A5)*(G2:G190=B5),K2:K190)) -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- |
|
#6
|
|||
|
|||
|
You're welcome, Tim !
-- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- "Tim" wrote in message ... This one realy works. Thank you Max! Tim |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Linear Mode
