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.

=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.

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.

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.

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.

"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.