Glenn,

After checking the data calculations, I noticed that partial months (less
than one month) calculations do not work quite right. I sorta fixed this by
including an "IF" function in the 2nd half of the formula that says if the
calculation is less than zero to add a 1. This works. For instance, I had 1
item that the calculation was -.02. I had 81 in stock and 83 sales in the
first month. It should have been .98, so adding 1 was the solution. If there
was no inventory the calculation was -1. Adding one solved this problem as
well.

Again, this is a great formula, it just needed a little tweaking for less
than a month supply. Did you have a better fix for this? or did I have
something wrong in your formula that caused the negatives?

I also had another problem that wasn't really associated with the formula.
We have some new items that do not have sales in the first few or more months
of the year (because the item was introduced later in the year). The
calculation counts these zero months as full months of supply. Is there any
way to account for leading zero months and skip them? As a fix to get the
report finished, I created a new formula in another cell to count zero months
and then deducted them from the calculation. This worked somewhat well, but
it also counts months where there was no sales in the middle of the year.
(sales in Jan-May, no sales in Jun, sales in Jul-Dec for instance). These
should be accounted for as having no sales. Mostly due to the seasonality of
our business.

Any thoughts you have would be appreciated. Again, I really love the
formula, it saved me hours of work!

Jennifer

"Glenn" wrote:

JLewis wrote:
Awesome! It works. I see now what I was and wasn't doing. Thank you, Thank
you! I really appreciate your help on this. This will help me in other
reports as well.



Glad I could help.