If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 |
Thread Tools | |
Display Modes | |
|
|