Rounding function for optimization

[wberg82]

I am trying to write a formula that rounds up (or down) to the nearest whole number. Here are my parameters. This is for optimizing material lengths in manufacturing.....

Cell P40 = number of stock lengths required
Cell D22 = Quantity of Pcs
Cell J6 = Length of Each Pc.
Cell I40 = Length of stock material

P40=IFERROR((D22/(ROUNDDOWN((I40/J6),0))),"") This is my current formula. The issue is, if my length (J6) is longer than my length to stock material (I40), the formula returns blank.

How do I make the formula recognize that if my length of each pc is longer than my stock material that I will need additional stock material to make up that length?

For example: I have 2 pcs of material at 60' each. My stock length material is 24'. Therefore, I would need 5 pcs of stock length material.

 

[shg]

	A​	B​	C​	D​	E​
1​	Stock Len​	Fab Len​	Fab Qty​	Stock Qty​	​
2​	24​	60​	2​	5​	D2: =CEILING(C2*B2/A2, 1)

 

[wberg82]

The above formula works, but it is still missing something that I need it to take into account. Here is my issue.

Stock Len: 24
Fab Len: 13
Fab Qty: 2
Stock Qty: 5

E2 = 6

This would be incorrect. If I am using 24' material, I can only get (1) 13' pc from that material. Therefore I would need (10) pcs of stock length - or E2=10.