AVERAGEIF with division

[zafega]

Good morning,

I am using average if =AVERAGEIF(AT177:AY177,"<>0"), to get the average of a series of data (similar to the one in the fist raw I have pasted here)
I am using if because I need to exclude the "0" from the calculation.
The complicated things for me now is that I need to divide each data by a number contained in a cell (shown here as 4), before getting the average (in this example is 2928)



[TABLE="width: 258"]
<colgroup><col><col><col span="2"><col><col></colgroup><tbody>[TR]
[TD]0[/TD]
[TD]12,000[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]11,420[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2928[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]




Any help is very much appreciate it.

Best regards,

Federico

 

[etaf]

you want to divide the result by 4
or each data element by 4
2928 is the average / 4
so its NOT each / 4
its the average / 4


can you just expand - so the average
is 12,000 + 11,420 = 23,420
two items so to get average = 23,420 / 2 = 11, 710
then / 4
= 2928

is that the arithmetic you want to carry out ?]

guessing your layout

=AVERAGEIF(AT177:AY177,"<>0") / AT179
assuming that cell AT179 has the number 4 in

 

[zafega]

Thanks @etaf for the reply.
Indeed the result you see there is manually calculated and not done with the expression I am searching for here.
The result you see (2928) is each cell / 4, and then averaged.
What I am searching for here is an expression where I can carry all this calculation in just one cell, taking into consideration that I have to take out all the 0s.
Thanks for trying to helping me out

 

[etaf]

this may not be the best way to do this - and others may have a better answer
But I'm thinking of using sumproduct to divide each entry by 4
so you get
=SUMPRODUCT((AT177:AY177)/AT179) . - again assuming AT179 is the cell with the 4 in
Now you can divide by the count of none zero entries to get average
=COUNTIF(AT177:AY177," < > "&0)

putting all together
=SUMPRODUCT((AT177:AY177)/AT179) / COUNTIF(AT177:AY177," < > "&0)

I have added spaces to any < or > as this forum converts to html and you dont see all the formula

 

[zafega]

Thanks Wayne,

beautifully solved!

 

[etaf]

you are welcome

 

[FormR]

=AVERAGEIF(AT177:AY177,"<>0") / AT179

=SUMPRODUCT((AT177:AY177)/AT179) / COUNTIF(AT177:AY177," < > "&0)

Hi, aren't both of these formulas mathematically equivalent - I'd suggest it's probably best to just use the simpler one.