Need a little help on this formula

  • Thread starter cabana_boy via OfficeKB.com
  • Start date
C

cabana_boy via OfficeKB.com

I'm trying to use the following formula to find the average an array of data
using an ID as a criteria. The problem is, the array I'm pulling data from
has some fields that are blank. My average function is including those cells
in the calculation instead of ignoring. Here's my formula:

{=AVERAGE(IF($B2='Total Transactions'!$C$2:$C$205,'Total Transactions'!$E$2:
$Q$205,"Not Found"))}

As an example, the sum of one of my rows of data is 5,367 total transactions
over 7 months. That should work out to 767 transactions per month. Instead,
the formula returns 413 because it is dividing by 13 (the total number of
columns in my array).

I even tried to go the long route and do a SUM(IF) divided by COUNT(IF)
formula, but I got the same result.

Hope that all makes sense. Any help would be greatly appreciated.
 
B

Bernie Deitrick

Try it this way to ignore blanks. Array enter

=IF(COUNTIF('Total Transactions'!$C$2:$C$205,$B2)=0,"Not
Found",AVERAGE(IF($B2='Total Transactions'!$C$2:$C$205,IF('Total
Transactions'!$E$2:$Q$205<>"",'Total Transactions'!$E$2:$Q$205))))

HTH,
Bernie
MS Excel MVP
 
C

cabana_boy via OfficeKB.com

Thanks!!! Worked perfectly!!!

Bernie said:
Try it this way to ignore blanks. Array enter

=IF(COUNTIF('Total Transactions'!$C$2:$C$205,$B2)=0,"Not
Found",AVERAGE(IF($B2='Total Transactions'!$C$2:$C$205,IF('Total
Transactions'!$E$2:$Q$205<>"",'Total Transactions'!$E$2:$Q$205))))

HTH,
Bernie
MS Excel MVP
I'm trying to use the following formula to find the average an array of
data
Click to expand...
[quoted text clipped - 18 lines]
Hope that all makes sense. Any help would be greatly appreciated.
Click to expand...
Click to expand...
 
Top