Average Formula -- Ignore cells with "0%"

[cwhaley1]

Hello. This may not be possible but I'll ask anyway as I've not been able to work it out myself...

I have a table which collates scores and calculates the total score as percentage. These percentage cells are spread out over one row, but over multiple separated columns because there are different sets of scores (so can't use a range formula).

At the end of the table I have a cell to calculate the overall score for all scores entered on that row. Not every row will have scores entered for every score so there will be some percentages which will read "0%" as there are no scores to calculate.

How can I get the overall average percentage to ignore the cells with "0%" in? Currently the formula that results in that 0% is:

=((AN5/4*AN$2)+(AO5/4*AO$2)+(AP5/4*AP$2)+(AQ5/4*AQ$2)+(AR5/4*AR$2)+(AS5/4*AS$2)+(AT5/4*AT$2))/(AN$2+AO$2+AP$2+AQ$2+AR$2+AS$2+AT$2)

​

Would I need to have this converted to a value in order to get an average formula to ignore?

 

[James006]

Hello,

You could take a look at the AverageIf function ...

Hope this will help

 

[etaf]

=AVERAGEIF(B6:D6,"<>0")
usually will give the average of a range, but as you say , nor range

i'm assuming this type of formula =((AN5/4*AN$2)+(AO5/4*AO$2)+(AP5/4*AP$2)+(AQ5/4*AQ$2)+(AR5/4*AR$2)+(AS5/4*AS$2)+(AT5/4*AT$2))/(AN$2+AO$2+AP$2+AQ$2+AR$2+AS$2+AT$2)

is in each cell that you then want to average
Average( G2,H2,J2,L2) will ignore blank cells
so can you modify the formulas that are returning a zero to be
= IF ( ((AN5/4*AN$2)+(AO5/4*AO$2)+(AP5/4*AP$2)+(AQ5/4*AQ$2)+(AR5/4*AR$2)+(AS5/4*AS$2)+(AT5/4*AT$2))/(AN$2+AO$2+AP$2+AQ$2+AR$2+AS$2+AT$2) = 0 , "", =((AN5/4*AN$2)+(AO5/4*AO$2)+(AP5/4*AP$2)+(AQ5/4*AQ$2)+(AR5/4*AR$2)+(AS5/4*AS$2)+(AT5/4*AT$2))/(AN$2+AO$2+AP$2+AQ$2+AR$2+AS$2+AT$2)
)

or have i misunderstood

or you could use

=SUM(non contiguous cells )/INDEX(FREQUENCY((non contiguous cells),0),2)
like
=SUM(A4, Z4, AP4)/INDEX(FREQUENCY((A4, Z4, AP4)),0),2)

 

[Special-K99]

This doesn't help your 0% and only tested with a small amount of data but that long formula looks like it can be reduced to

=SUMPRODUCT((AN5:AT5)/4*(AN2:AT2))/(SUM(AN2:AT2))


UPDATE: Does this work with your 0% ?

=SUMPRODUCT((AN5:AT5<>0)*(AN5:AT5)/4*(AN2:T2))/(SUM(AN2:AT2))

This assumes row five will contain 0s, not row two.

 

[cwhaley1]

Thanks all very much for the responses. I should have said -- the formula I quoted in my first post is essential as it is used to work out question weightings.

The formula I have been using to try and work out the overall average of all the percentage scores is:

=SUM(IF(ISNUMBER(Y5),Y5),IF(ISNUMBER(AC5),AC5), IF(ISNUMBER(AJ5),AJ5),IF(ISNUMBER(AM5),AM5),IF(ISNUMBER(AV5),AV5),IF(ISNUMBER(BA5),BA5))/MAX(1,COUNT(Y5,AC5,AJ5,AM5,AV5,BA5))

At the moment, it will work out the average percentage correctly but should there be a 0% score, the average score is brought down which I do not want.​

 

[etaf]

did you try
=SUM(A4, Z4, AP4)/INDEX(FREQUENCY((A4, Z4, AP4)),0),2)

from my edited post ?

 

[cwhaley1]

did you try
=SUM(A4, Z4, AP4)/INDEX(FREQUENCY((A4, Z4, AP4)),0),2)

from my edited post ?

Apologies -- I must have missed the edit.

I have just tried this and receive a message telling me I've entered too few arguments. You have understood me correctly though -- the formula I first posted is in each cell and it's these cells I want to average. Should there be a 0%, I don't want this 0% to be calculated.

I'm trying to pick apart your 2nd formula now, but never used the FREQUENCY function within Excel so unsure of the correct syntax.

 

[etaf]

so all you do is out your non-contiguous cell references in

of your range of cells to average is Y5,AC5,AJ5,AM5,AV5,BA5

then you just put

=SUM(Y5,AC5,AJ5,AM5,AV5,BA5)/INDEX(FREQUENCY((Y5,AC5,AJ5,AM5,AV5,BA5)),0),2)

and it should ignore blanks and zero values
At least it does for me in Excel 365

 

[cwhaley1]

so all you do is out your non-contiguous cell references in

of your range of cells to average is Y5,AC5,AJ5,AM5,AV5,BA5

then you just put

=SUM(Y5,AC5,AJ5,AM5,AV5,BA5)/INDEX(FREQUENCY((Y5,AC5,AJ5,AM5,AV5,BA5)),0),2)

and it should ignore blanks and zero values
At least it does for me in Excel 365

Totally understand your formula and I've even copied it as-is from your post as an example, but I'm still getting the "too few arguments" message. I'm on Office 365 ProPlus (PC work computer unfortunately).

 

[cwhaley1]

I think I've cracked it... I've used the foumula below instead of my first-posted one:

=SUMPRODUCT(($Z12:$AA12/4)*$Z$2:$AA$2)/SUMPRODUCT(--($Z12:$AA12<>"")*($Z$2:$AA$2))

Using this results in the overall average score ignoring zeroes.