Averaging Issues

[SFCChase]

I have a large workbook that calculates scores based off referencing a hidden tab (so every cell has a =IF formula that references the hidden tab). I'm trying to get a numerical average of those cells that return numbers but everything I've tried counts the IF Formula into the equation and I end up with a really small or really large number (300 cells being counted when i really only want average of 25).

CUMULATIVE PASS RATE

=IF('DATA SHEET'!A2:A302="","",IF(AND('DATA SHEET'!B2="",'DATA SHEET'!C2=""),"",IF('DATA SHEET'!B2>=76,'DATA SHEET'!B2,IF(AND('DATA SHEET'!B2="",'DATA SHEET'!C2>=76),'DATA SHEET'!C2,'DATA SHEET'!B2))))

=IF('DATA SHEET'!A2:A302="","",IF(AND('DATA SHEET'!B3="",'DATA SHEET'!C3=""),"",IF('DATA SHEET'!B3>=76,'DATA SHEET'!B3,IF(AND('DATA SHEET'!B3="",'DATA SHEET'!C3>=76),'DATA SHEET'!C3,'DATA SHEET'!B3))))

=IF('DATA SHEET'!A2:A302="","",IF(AND('DATA SHEET'!B4="",'DATA SHEET'!C4=""),"",IF('DATA SHEET'!B4>=76,'DATA SHEET'!B4,IF(AND('DATA SHEET'!B4="",'DATA SHEET'!C4>=76),'DATA SHEET'!C4,'DATA SHEET'!B4))))

=IF('DATA SHEET'!A2:A302="","",IF(AND('DATA SHEET'!B5="",'DATA SHEET'!C5=""),"",IF('DATA SHEET'!B5>=76,'DATA SHEET'!B5,IF(AND('DATA SHEET'!B5="",'DATA SHEET'!C5>=76),'DATA SHEET'!C5,'DATA SHEET'!B5))))

=IF('DATA SHEET'!A2:A302="","",IF(AND('DATA SHEET'!B6="",'DATA SHEET'!C6=""),"",IF('DATA SHEET'!B6>=76,'DATA SHEET'!B6,IF(AND('DATA SHEET'!B6="",'DATA SHEET'!C6>=76),'DATA SHEET'!C6,'DATA SHEET'!B6))))

=IF('DATA SHEET'!A2:A302="","",IF(AND('DATA SHEET'!B7="",'DATA SHEET'!C7=""),"",IF('DATA SHEET'!B7>=76,'DATA SHEET'!B7,IF(AND('DATA SHEET'!B7="",'DATA SHEET'!C7>=76),'DATA SHEET'!C7,'DATA SHEET'!B7))))

I would like cell A1 (Cumulative Pass Rate) to average the scores that are transposed into the cells (A2:A7 above). I've tried AVERAGE(A2:A7) but I get a #N/A error. I tried AVERAGEIF but it keeps giving me exceedingly high or low results (not a % out of 100...it's almost always resulting 0.05% or 5000%). I'm not big on AGGREGATE so I'm not sure if it would work or not.

Any suggestions would be greatly appreciated.

 

[mmhill]

Not certain what you are trying, but the part of the formula: DATA SHEET'!A2:A302="" may be giving you a probem. It will only evaluate cell A2="" as TRUE or FALSE.
If this is what you want, you could probably use =Sum(A2:A7)/6.