Impact of negative sales on inventory weeks on hand

[DHallgren]

Hi! I am working on a spreadsheet which will show the 10 items with the most
inventory at each account, measured by weeks on hand. Weeks on hand is
calculated by dividing the value of inventory on hand by the averages sales
for the past several weeks (usually 13).

This is fairly straightforward unless an item has had no sales, or negative
sales (due to seasonality). Obvioulsy the formula will return #DIV/0! or a
negative number. Can someone help me with a formula that will return a value
that will return a value that can be sorted in descending order for
distribution to my coworkers, and easy population into presentations?

Thanks for any help you can give.

 

[Dave F]

Are you just looking to avoid the #DIV/0! error and negative values? Then do
something like this: =IF(OR(ISERROR([your calculation]),[your
calculation]<0),"",[your calculation])

Dave

 

[DHallgren]

Thanks for your response. I think that I may not have been very clear on my
post.

I want to show that an item with $1000 of inventory on hand and no sales
during the past 13 weeks is worse than an item that has $3000 of inventory on
hand, but has average sales of $500 per week. The $3000 worth of inventory
represents 6 weeks of inventory on hand. The $1000 with no sales represents
WAY more weeks on hand.

Does that make more sense?

Thanks again. Hope you have a great day.
--
Diane

Are you just looking to avoid the #DIV/0! error and negative values? Then do
something like this: =IF(OR(ISERROR([your calculation]),[your
calculation]<0),"",[your calculation])

Dave
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.

Hi! I am working on a spreadsheet which will show the 10 items with the most
inventory at each account, measured by weeks on hand. Weeks on hand is
calculated by dividing the value of inventory on hand by the averages sales
for the past several weeks (usually 13).

This is fairly straightforward unless an item has had no sales, or negative
sales (due to seasonality). Obvioulsy the formula will return #DIV/0! or a
negative number. Can someone help me with a formula that will return a value
that will return a value that can be sorted in descending order for
distribution to my coworkers, and easy population into presentations?

Thanks for any help you can give.

 

[Fred Smith]

Yes, it makes sense, but you still have to decide what answer you want.

Obviously, any item that has inventory, but no (or negative) sales has an
infinite supply. All you need to decide is what to display when the answer is
infinite. Some people will choose blanks, some an asterisk, some a bunch of
nines, some an infinity symbol. Simply insert whatever answer you want in place
of the quotes in Dave's if statement. Something like:

=if(sales<=0,"infinity",inventory/sales)

--
Regards,
Fred

Thanks for your response. I think that I may not have been very clear on my
post.

I want to show that an item with $1000 of inventory on hand and no sales
during the past 13 weeks is worse than an item that has $3000 of inventory on
hand, but has average sales of $500 per week. The $3000 worth of inventory
represents 6 weeks of inventory on hand. The $1000 with no sales represents
WAY more weeks on hand.

Does that make more sense?

Thanks again. Hope you have a great day.
--
Diane

Are you just looking to avoid the #DIV/0! error and negative values? Then do
something like this: =IF(OR(ISERROR([your calculation]),[your
calculation]<0),"",[your calculation])

Dave
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.

Hi! I am working on a spreadsheet which will show the 10 items with the
most
inventory at each account, measured by weeks on hand. Weeks on hand is
calculated by dividing the value of inventory on hand by the averages sales
for the past several weeks (usually 13).

This is fairly straightforward unless an item has had no sales, or negative
sales (due to seasonality). Obvioulsy the formula will return #DIV/0! or a
negative number. Can someone help me with a formula that will return a
value
that will return a value that can be sorted in descending order for
distribution to my coworkers, and easy population into presentations?

Thanks for any help you can give.

 

[DHallgren]

Fred,

That's exactly it, I just wasn't thinking about it the right way. I kept
thinking that it was in the formula I was using and how I was formatting my
numbers, not about choosing a result that will put them at the top of the
list. Thanks so much for your help!
--
Diane

Yes, it makes sense, but you still have to decide what answer you want.

Obviously, any item that has inventory, but no (or negative) sales has an
infinite supply. All you need to decide is what to display when the answer is
infinite. Some people will choose blanks, some an asterisk, some a bunch of
nines, some an infinity symbol. Simply insert whatever answer you want in place
of the quotes in Dave's if statement. Something like:

=if(sales<=0,"infinity",inventory/sales)

--
Regards,
Fred

Thanks for your response. I think that I may not have been very clear on my
post.

I want to show that an item with $1000 of inventory on hand and no sales
during the past 13 weeks is worse than an item that has $3000 of inventory on
hand, but has average sales of $500 per week. The $3000 worth of inventory
represents 6 weeks of inventory on hand. The $1000 with no sales represents
WAY more weeks on hand.

Does that make more sense?

Thanks again. Hope you have a great day.
--
Diane

Are you just looking to avoid the #DIV/0! error and negative values? Then do
something like this: =IF(OR(ISERROR([your calculation]),[your
calculation]<0),"",[your calculation])

Dave
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.


:

Hi! I am working on a spreadsheet which will show the 10 items with the
most
inventory at each account, measured by weeks on hand. Weeks on hand is
calculated by dividing the value of inventory on hand by the averages sales
for the past several weeks (usually 13).

This is fairly straightforward unless an item has had no sales, or negative
sales (due to seasonality). Obvioulsy the formula will return #DIV/0! or a
negative number. Can someone help me with a formula that will return a
value
that will return a value that can be sorted in descending order for
distribution to my coworkers, and easy population into presentations?

Thanks for any help you can give.