Average If statement ignoring zero's

[Adam57]

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

 

[Peter_SSs]

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)))}

Are you sure that is the formula you are using, or is it this?
{=AVERAGE(IF($C$6:$C$500=$C503,IF($D$5:$BC$5=D$502-1,$D$6:$BC$500)))}

Try this
{=AVERAGE(IF($C$6:$C$500=$C503,IF($D$5:$BC$5=D$502-1,IF($D$6:$BC$500<>"",$D$6:$BC$500))))}

 

[Adam57]

Are you sure that is the formula you are using, or is it this?
{=AVERAGE(IF($C$6:$C$500=$C503,IF($D$5:$BC$5=D$502-1,$D$6:$BC$500)))}

Try this
{=AVERAGE(IF($C$6:$C$500=$C503,IF($D$5:$BC$5=D$502-1,IF($D$6:$BC$500<>"",$D$6:$BC$500))))}

Hi Peter,

Thank you for your reply,

The formula i pasted is the correct one that i am ising.
If i was to make the changes that you have pointed out then this will return either a #VALUE! as a standard formula, or #DIV/0! as an array.

This is the same for the formula you have pasted, it is returning the same value that i already have unfortunately.

 

[Peter_SSs]

The formula i pasted is the correct one that i am ising.

OK, then I must have guessed incorrectly about what is in D502 and/or the rest of row 5.

What about this then? Your original formula in D505, my adjusted formula in D506.
I have entered these as array formulas with Ctrl+Shift+Enter but I note you have two Excel versions and there would be no need to enter as an array formula if using 365 as shown in D507 which was entered normally (as I am using 365)

21 09 04.xlsm
	C	D	E
5	3	4	5
6	Bob	300	200
7	Adam	180	200
8	Adam
9	Dave	200	100
10
11
12
13
501
502		4
503	Adam
504
505		90
506		180
507		180
Average

Cell Formulas
Range	Formula
D505	D505	=AVERAGE(IF($C$6:$C$500=$C503,IF($C$5:$BC$5=D$502-1,$D$6:$BC$500)))
D506	D506	=AVERAGE(IF($C$6:$C$500=$C503,IF($C$5:$BC$5=D$502-1,IF($D$6:$BC$500<>"",$D$6:$BC$500))))
D507	D507	=AVERAGE(IF($C$6:$C$500=$C503,IF($C$5:$BC$5=D$502-1,IF($D$6:$BC$500<>"",$D$6:$BC$500))))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Adam57]

Hi Peter, The bottom formula seems to have worked. I have changed the <>"" to <>0 and this works fine for me and what i need it for.
Thank you for the perseverance and the help.
Its greatly appreciated.

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.