Need a little help on this formula
 

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.

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200912/1

Need a little help on this formula
 

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


"cabana_boy via OfficeKB.com" <u43066@uwe wrote in message
news:a00135a55c289@uwe...
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.

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200912/1

Need a little help on this formula
 

Thanks!!! Worked perfectly!!!

Bernie Deitrick wrote:
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
[quoted text clipped - 18 lines]

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

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200912/1