Average If

[sdoppke]

=AVERAGEIF(AND(VLOOKUP(D2,Data!$D$2:$BL$2078,42,0),VLOOKUP(D2,Data!$D$2:$BL$2078,43,0),VLOOKUP(D2,Data!$D$2:$BL$2078,44,0),VLOOKUP(D2,Data!$D$2:$BL$2078,45,0),VLOOKUP(D2,Data!$D$2:$BL$2078,46,0),VLOOKUP(D2,Data!$D$2:$BL$2078,47,0),VLOOKUP(D2,Data!$D$2:$BL$2078,48,0),VLOOKUP(D2,Data!$D$2:$BL$2078,49,0),VLOOKUP(D2,Data!$D$2:$BL$2078,50,0)),>0)

Hi everyone, can anyone figure out why my formula doesnt work? I am trying to averge all these vlookups that are greater than 0.

thanks a ton in advance

sd

 

[T. Valko]

=AVERAGEIF(AND(VLOOKUP(D2,Data!$D$2:$BL$2078,42,0),VLOOKUP(D2,Data!$D$2:$BL$2078,43,0),VLOOKUP(D2,Data!$D$2:$BL$2078,44,0),VLOOKUP(D2,Data!$D$2:$BL$2078,45,0),VLOOKUP(D2,Data!$D$2:$BL$2078,46,0),VLOOKUP(D2,Data!$D$2:$BL$2078,47,0),VLOOKUP(D2,Data!$D$2:$BL$2078,48,0),VLOOKUP(D2,Data!$D$2:$BL$2078,49,0),VLOOKUP(D2,Data!$D$2:$BL$2078,50,0)),>0)

Hi everyone, can anyone figure out why my formula doesnt work? I am trying to averge all these vlookups that are greater than 0.

thanks a ton in advance

sd

Try this array formula**:

=AVERAGE(IF(Data!D2:D2078=D2,IF(Data!AS2:BA2078>0,Data!AS2:BA2078)))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

 

[sdoppke]

Try this array formula**:

=AVERAGE(IF(Data!D2:D2078=D2,IF(Data!AS2:BA2078>0,Data!AS2:BA2078)))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Thank you as allways for your help, worked perfect! Im not sure i understand the exact logic of the formula. Could you help me (learn to) tweak this:

=AVERAGE(IF(Data!D2:D2078=F2,IF(Data!BB2:BL2078>0,Data!BB2:BL2078)))

to include to set of lookups (not sure if lookup was the right word) so in other words i need to average all the above number plus these below:

=AVERAGE(IF(Data!D2:D2078=E2,IF(Data!BB2:BL2078>0,Data!BB2:BL2078)))

Hope that made sense.

sd

 

[sdoppke]

Thank you as allways for your help, worked perfect! Im not sure i understand the exact logic of the formula. Could you help me (learn to) tweak this:

=AVERAGE(IF(Data!D2:D2078=F2,IF(Data!BB2:BL2078>0,Data!BB2:BL2078)))

to include to set of lookups (not sure if lookup was the right word) so in other words i need to average all the above number plus these below:

=AVERAGE(IF(Data!D2:D2078=E2,IF(Data!BB2:BL2078>0,Data!BB2:BL2078)))

Hope that made sense.

sd

I'm thinking this?

=AVERAGE(IF(Data!D2:D2078=F2,IF(Data!BB2:BL2078>0,Data!BB2:BL2078)),IF(Data!D2:D2078=D2,IF(Data!BB2:BL2078>0,Data!BB2:BL2078)))

 

[T. Valko]

I'm thinking this?

=AVERAGE(IF(Data!D2:D2078=F2,IF(Data!BB2:BL2078>0,Data!BB2:BL2078)),IF(Data!D2:D2078=D2,IF(Data!BB2:BL2078>0,Data!BB2:BL2078)))

Yeah, like that but you have the same range listed twice.

Array entered**:

=AVERAGE(IF(Data!D2:D2078=D2,IF(Data!AS2:BA2078>0,Data!AS2:BA2078)),IF(Data!D2:D2078=F2,IF(Data!BB2:BL2078>0,Data!BB2:BL2078)))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.