Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|