Averaging, but excluding zeros
 

i have weekly inventory in a spreadsheet....but the columns are not right
next to eachother...for example columns a, c, e, and g have
inventory.....Sales may be in column b,d,f and h. I need a formula in column
z that averages a,c,e and g. But if the inventory is zero, don't average it.

Averaging, but excluding zeros
 

=AVERAGE(IF((A2:G7<0)*(MOD(COLUMN(A2:G7),2)=1),A2 :G7))

ctrl+shift+enter, not just enter

Adjust your range to suit



"Tami" wrote:

i have weekly inventory in a spreadsheet....but the columns are not right
next to eachother...for example columns a, c, e, and g have
inventory.....Sales may be in column b,d,f and h. I need a formula in column
z that averages a,c,e and g. But if the inventory is zero, don't average it.

Averaging, but excluding zeros
 

thanks, i'll try it!

"Teethless mama" wrote:

=AVERAGE(IF((A2:G7<0)*(MOD(COLUMN(A2:G7),2)=1),A2 :G7))

ctrl+shift+enter, not just enter

Adjust your range to suit



"Tami" wrote:

i have weekly inventory in a spreadsheet....but the columns are not right
next to eachother...for example columns a, c, e, and g have
inventory.....Sales may be in column b,d,f and h. I need a formula in column
z that averages a,c,e and g. But if the inventory is zero, don't average it.

Averaging, but excluding zeros
 

Tami wrote...
i have weekly inventory in a spreadsheet....but the columns are not right
next to eachother...for example columns a, c, e, and g have
inventory.....Sales may be in column b,d,f and h. *I need a formula in column
z that averages a,c,e and g. But if the inventory is zero, don't average it.

Averaging, but excluding zeros
 

Hi,

It is not clear from your question whether you are trying to do this
separately for each row or for the entire columns, here is a similar idea for
a row by row average. Note you can change it to a column average by just
changing G2 to G20 or whatever.

=AVERAGE(IF(A2:G2*(MOD(COLUMN(A2:G2),2)=1)<0,A2:G 2))

this is an array formula so you need to press Shift+Ctrl+Enter

If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Tami" wrote:

i have weekly inventory in a spreadsheet....but the columns are not right
next to eachother...for example columns a, c, e, and g have
inventory.....Sales may be in column b,d,f and h. I need a formula in column
z that averages a,c,e and g. But if the inventory is zero, don't average it.

Averaging, but excluding zeros
 

"Shane Devenshire" wrote:

Hi,

It is not clear from your question whether you are trying to do this
separately for each row or for the entire columns, here is a similar idea for
a row by row average. Note you can change it to a column average by just
changing G2 to G20 or whatever.

=AVERAGE(IF(A2:G2*(MOD(COLUMN(A2:G2),2)=1)<0,A2:G 2))

this is an array formula so you need to press Shift+Ctrl+Enter

If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Tami" wrote:

i have weekly inventory in a spreadsheet....but the columns are not right
next to eachother...for example columns a, c, e, and g have
inventory.....Sales may be in column b,d,f and h. I need a formula in column
z that averages a,c,e and g. But if the inventory is zero, don't average it.