# Simplify a nested IF, SUM statement

#### imported_unknown

##### Active Member
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

### Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

Replies
5
Views
298
Replies
0
Views
216
Replies
2
Views
312
Replies
1
Views
2K
Replies
9
Views
270

1,196,430
Messages
6,015,222
Members
441,882
Latest member
rcgyuk

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back