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<390000, L3:L150,false)) and it gives me a number I am
not sure is right. Thank you so much.

Try:

=AVERAGE(IF(L3:L1500,IF(L3:L15<39000, L3:L150)))

confirmed with CTRL+SHIFT+ENTER


--
NBVC

Where there is a will there are many ways.
'The Code Cage' (http;//www.thecodecage.com)
------------------------------------------------------------------------
NBVC's Profile: http://www.thecodecage.com/forumz/member.php?userid=74
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=109857

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.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Max S." wrote:

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<390000, L3:L150,false)) and it gives me a number I am
not sure is right. Thank you so much.

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


"Max S." wrote:

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<390000, L3:L150,false)) and it gives me a number I am
not sure is right. Thank you so much.

Right on! Works perfectly. Thank you again.

"Shane Devenshire" wrote:

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


"Max S." wrote:

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<390000, L3:L150,false)) and it gives me a number I am
not sure is right. Thank you so much.