Any Excel Formula Experts?

[Bsweeney]

I am trying to calculate Inventory Months Supply.
I can calculate the months suppply as shown below in multiple cells, but I am looking for a formula to do in one cell..
The correct result in this example is: 2.26048

Thanks!

 

[Colin Legg]

Welcome to the forum.

This appears to be a duplicate of this thread:
http://www.mrexcel.com/forum/showthread.php?t=380320

Can you explain how the numbers in Row 6 are currently calculated?

 

[schielrn]

Can you tell us how you are manually calculating E7? All you are giving us is numbers and expect us to know what goes together? You should also have stuck to your original thread.

 

[Joe4]

I deleted the duplicate.

In the future, please do not post duplicate threads. All follow-ups, clarifications, or bumps should be posted to the original thread. Per forum rules, all duplicates will be locked or deleted (http://www.mrexcel.com/forum/showthread.php?t=127080 , rules 21-23)

 

[Bsweeney]

1st time posting, sorry for duplicating.

Cell C6 & D6 is a if statement that return a "0' if row 5 is a positive #.

Cell E 6 is D5/E2

 

[schielrn]

Cell C6 & D6 is a if statement that return a "0' if row 5 is a positive #.

So what makes C6 and D6 = to 1? C5 and D5 are both positive numbers?

 

[Bsweeney]

Correction. below is my revised sheet showing formulas on bottom:

 

[schielrn]

Ok now that makes a lot more sense, so let me make sure I am understanding you correctly, you want to get rid of the formulas in C4, D4 and E4 and only have the formula in E5?

 

[Bsweeney]

Yes, That would be perfect if that could be done. The formula would actually have to cover a 12 month span of budgetd sales. In this example I knew that it was just over 2 months, but it just as easily be 11 months.

Appreciate the help!

 

[schielrn]

How about this entered in E5:

=SUM(IF($C$3:E3>0,1,$B$3:D3/$C$2:E2))

This needs to be confirmed with control+shift+enter and not just enter.

It can also be copied accross the columns to the other cells.

Book3
	A	B	C	D	E	F
1		Feb	Mar	Apr	May	Jun
2	BudgetedSales		2221595	1795717	28976236	16425465
3	EndingInventory	11565027	9343432	7547715	-21428521	-37853986
4
5					2.2604795	0.955888
Data

Hope that helps.