Tami said:
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.
If you had 0 for inventory in columns A, C and G but 20 in column E,
do you really want your 'average' to appear as 20 rather than 5? If
the inventory zeros mean there's no inventory in a particular week
when there usually would be positive inventory, then those zeros are
valid numbers and probably should be included in the averages. On the
other hand, if you're using zeros to represent the fact that you don't
carry any inventory for whatever the columns represent (item?
location? something else?) in any week, then it's a mistake to
represent that by 0.
In other words, you may be risking misrepresenting your data if zeros
in columns A, C, E and G represent both no units in stock at
particular times as well as never a particular item in stock in one of
the columns. Better to use 0 to represent occasional/temporary out-of-
stock for items usually kept in stock and blank or a text value for
items never kept in stock. Then you could use AVERAGE to include the
meaningful zeros, and it would automatically skip the blank or text
values.
