Simplify a nested IF, SUM statement

[imported_unknown]

I have a spreadsheet with two columns of data: product inventory and actual sales, each listed by month. I want to calculate how many months of inventory I have based on actual sales. The following formula gives me what I need, but I'm looking for a simpler solution (preferably without macros, since I want the results to update automatically as the raw data changes). Here's my formula:

=IF(SUM(F6:F10)<B5,5+(B5-SUM(F6:F10))/F11,IF(SUM(F6:F9)<B5,4+(B5-SUM(F6:F9))/F10,IF(SUM(F6:F8)<B5,3+(B5-SUM(F6:F8))/F9,IF(SUM(F6:F7)<B5,2+(B5-SUM(F6:F7))/F8,IF(F6<B5,1+(B5-F6)/F7,B5/F6)))))

Inventory is in column B and actual sales is in column F. Typical results are as follows:

Column B (Product inventory):
248
268
252
234
206
188
148
191
195

Column F (Actual sales):
59
56
86
110
126
121
93
79
105
97
65
80

Results of formula:
2.96
2.57
2.13
1.89
1.91
2.15
1.66
1.89
2.41


Column F has more values than column B because the formula must look ahead to do the calculation.

Does anyone have a more elegant solution that what I came up with?
This message was edited by Toddard on 2002-10-04 11:37