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]

[Table="width:, class:grid"][tr][td="bgcolor:#C0C0C0"][/td][td="bgcolor:#C0C0C0"]

A​

[/td][td="bgcolor:#C0C0C0"]

B​

[/td][td="bgcolor:#C0C0C0"]

C​

[/td][td="bgcolor:#C0C0C0"]

D​

[/td][td="bgcolor:#C0C0C0"]

E​

[/td][/tr][tr][td="bgcolor:#C0C0C0"]

1​

[/td][td="bgcolor:#F3F3F3"]

Stock Len​

[/td][td="bgcolor:#F3F3F3"]

Fab Len​

[/td][td="bgcolor:#F3F3F3"]

Fab Qty​

[/td][td="bgcolor:#F3F3F3"]

Stock Qty​

[/td][td="bgcolor:#F3F3F3"]

​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

2​

[/td][td]

24​

[/td][td]

60​

[/td][td]

2​

[/td][td="bgcolor:#CCFFCC"]

5​

[/td][td="bgcolor:#CCFFCC"]D2: =CEILING(C2*B2/A2, 1)[/td][/tr]
[/table]

 

[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.