Average IF with multiple OR criteria

[genepaton]

I tried writing an OR multiple criteria to work out averages from a column of data, but seem to be getting different results to manual calculations.
Can anyone assist in writing a single formula for the following multiple ones?

{=IF(B62=0,"0",AVERAGE(IF((Sheet1!H10:H65536="ARP"),Sheet1!I10:I65536)))/1440}
{=IF(B62=0,"0",AVERAGE(IF((Sheet1!H10:H65536="ART"),Sheet1!I10:I65536)))/1440}
{=IF(B62=0,"0",AVERAGE(IF((Sheet1!H10:H65536="AP"),Sheet1!I10:I65536)))/1440}
{=IF(B62=0,"0",AVERAGE(IF((Sheet1!H10:H65536="AH"),Sheet1!I10:I65536)))/1440}

B62 is just a COUNTIF formula to see if there's any "A*" entries in the range H10:H65536.

I tried {=AVERAGE(IF(OR(Sheet1!H10:H65536="ARP",Sheet1!H10:H65536="ART",Sheet1!H10:H65536="AP",Sheet1!H10:H65536="AH"),Sheet1!I10:I65536))/1440}
But got different results.

 

[T. Valko]

I tried writing an OR multiple criteria to work out averages from a column of data, but seem to be getting different results to manual calculations.
Can anyone assist in writing a single formula for the following multiple ones?

{=IF(B62=0,"0",AVERAGE(IF((Sheet1!H10:H65536="ARP"),Sheet1!I10:I65536)))/1440}
{=IF(B62=0,"0",AVERAGE(IF((Sheet1!H10:H65536="ART"),Sheet1!I10:I65536)))/1440}
{=IF(B62=0,"0",AVERAGE(IF((Sheet1!H10:H65536="AP"),Sheet1!I10:I65536)))/1440}
{=IF(B62=0,"0",AVERAGE(IF((Sheet1!H10:H65536="AH"),Sheet1!I10:I65536)))/1440}

B62 is just a COUNTIF formula to see if there's any "A*" entries in the range H10:H65536.

I tried {=AVERAGE(IF(OR(Sheet1!H10:H65536="ARP",Sheet1!H10:H65536="ART",Sheet1!H10:H65536="AP",Sheet1!H10:H65536="AH"),Sheet1!I10:I65536))/1440}
But got different results.

Try it like this.

Put the criteria in a range of cells:

• A2 = ARP
• A3 = ART
• A4 = AP
• A5 = AH

Array entered**:

=IF(B62=0,0,AVERAGE(IF(ISNUMBER(MATCH(Sheet1!H10:H65536,A2:A5,0)),Sheet1!I10:I65536))/1440)

** 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.

 

[genepaton]

Try it like this.

Put the criteria in a range of cells:

• A2 = ARP
• A3 = ART
• A4 = AP
• A5 = AH

Array entered**:

=IF(B62=0,0,AVERAGE(IF(ISNUMBER(MATCH(Sheet1!H10:H65536,A2:A5,0)),Sheet1!I10:I65536))/1440)

** 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.

Once again Mr Valko, you save the day!
Thanks, and i actually was going to mention a range of reference cells! Just to demonstrate that i am learning from your wisdom

 

[T. Valko]

Once again Mr Valko, you save the day!
Thanks, and i actually was going to mention a range of reference cells! Just to demonstrate that i am learning from your wisdom

You're welcome. Thanks for the feedback!