Average for a column to exclude zeros and numbers above 39000

[Max S.]

Dear ladies and gents, please help me figure out how to build a formula for a
column of 150 cells, where I need to obtain the average excluding the cells
containing zeros and numbers above 39000. I tried
=AVERAGE(if(L3:L150<39000>0, L3:L150,false)) and it gives me a number I am
not sure is right. Thank you so much.

 

[Luke M]

You are correct, that formula will give the wrong number. You can use this
array* formula:

=AVERAGE(IF((L3:L150<>0)*(L3:L150<39000),A1:A4))

*Use Ctrl+Shift+Enter to confirm formula, not just Enter.

 

[Shane Devenshire]

Hi,

Try the following array:

=AVERAGE(IF((L3:L150<39000)*(L3:L150<>0),L3:L150))

To make this an array Press Shift+Ctrl+Enter to enter the formula not Enter

 

[Max S.]

Right on! Works perfectly. Thank you again.

Hi,

Try the following array:

=AVERAGE(IF((L3:L150<39000)*(L3:L150<>0),L3:L150))

To make this an array Press Shift+Ctrl+Enter to enter the formula not Enter

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire