Hi all,
I am using a formula within a database i have created. I have now altered the way the data is extracted into the database and this now causes some of the data to return 0's but my formula now looks at these 0's and dilutes my end figures.
Here is the array formula that i am using:
{=AVERAGE(IF($C$6:$C$500=$C503,IF($C$5:$BC$5=D$502-1,$D$6:$BC$500)))}
$C$6:$C$500=$C503 - (In this range, C503 is looking for a particular name[This name can appear more than once])
$C$5:$BC$5=D$502 - (In this range, D502 is looking for a particular number)
$D$6:$BC$500 - (This is the range that houses the data that i need averaging based on the criteria above)
Below is a snip of my sheet, Bob is in cell C6.
The current formula reads, If Adam appears in column C, and the number 4 appears in row 5, then average the data in column D that matches this criteria.
But i now need the same formula to ignore the blank cells/cells that have zero values.
At present the formula will give the outcome of 90 as it will take the 180 next to Adam and also include the blank cell next to the other Adam.
A bit long winded but any help on this would be greatly apprecieated.
Thank you
I am using a formula within a database i have created. I have now altered the way the data is extracted into the database and this now causes some of the data to return 0's but my formula now looks at these 0's and dilutes my end figures.
Here is the array formula that i am using:
{=AVERAGE(IF($C$6:$C$500=$C503,IF($C$5:$BC$5=D$502-1,$D$6:$BC$500)))}
$C$6:$C$500=$C503 - (In this range, C503 is looking for a particular name[This name can appear more than once])
$C$5:$BC$5=D$502 - (In this range, D502 is looking for a particular number)
$D$6:$BC$500 - (This is the range that houses the data that i need averaging based on the criteria above)
Below is a snip of my sheet, Bob is in cell C6.
The current formula reads, If Adam appears in column C, and the number 4 appears in row 5, then average the data in column D that matches this criteria.
But i now need the same formula to ignore the blank cells/cells that have zero values.
At present the formula will give the outcome of 90 as it will take the 180 next to Adam and also include the blank cell next to the other Adam.
A bit long winded but any help on this would be greatly apprecieated.
Thank you