Round a formula in a sum

[CSHEP3K]

Hi all,
I need to calculate how many pieces of MDF I can cut from a sheet at a certain size.

For example:

I need 206 meters at 350mm wide

The sheet size is 1220mm wide and 2000mts long

So I know on this occasion I can cut 3 pieces at 350mm wide at 2 meters long. This is obviously 6mts total

I would be left with some waste which I do not want included in my calculation

I am trying to create a sum that looks something like this, but I know this is not correct.


=ROUNDUP(SUM(250/((1220/350)*2)),0) This would return a sum of 36 Sheets but the quantity needed is actually 42

This is because the formula produces 3.48 as the answer from the (1220/350), but as I am calculating how many whole 350's I can get out of 1220 I need this to round down to 3.

As I have hundreds of different widths to calculate on a daily basis it would be great to have a formula set where I only have to change the width each time.

Any help would be greatly appreciated

Kind regards

Carl

 

[offthelip]

Your numbers don't make sense to me, however I think the formula to calculate how many pieces you can cut from a sheet is:
Total Number = (Rounddown(Sheetwidth/piecewidth,0)) *(Rounddown(Sheetlength/piecelength,0))

 

[CSHEP3K]

Hi thanks for the reply.
Its not quite what I am looking but I probably didn't explain it great

I want to divide my total lengths (250) by the number of lengths I can get from a sheet

my variant is how many widths I can cut from a sheet.
If we say my starting sheet is 1000 long by 500 wide and i need a piece cut to size at 1000 x 400 I get 1 piece out of a sheet and a scrap piece at 1000 x 100.

If i need 5 piece at 1000 x 400 logically I know I would need 5 sheets and would be left with 5 scrap pieces at 1000 x 100, but the formula incorporates the scrap pieces and thinks it makes a whole section so calculates i only need 4 sheets.
This becomes a problem when I am needing 25000 pieces at 1000 x 400

What I want to do is divide my total pieces required by the number of whole widths I can cut

Example =25000/ROUNDDOWN((500/400),0)

So we would get the sum 2500/1 rather than 2500/1.25

does that make more sense??

 

[Fluff]

Not sure I fully understand, but maybe

+Fluff New.xlsm
	A	B
4	Sheet width	350
5	Req'd width	1220
6	Qty	250
7		84
Main

Cell Formulas
Range	Formula
B7	B7	=ROUNDUP(B6/ROUNDDOWN(B5/B4,0),0)

 

[CSHEP3K]

Doesn't matter that you didn't quite understand because you nailed it.
That's perfect thank you

 

[Fluff]

Glad we could help & thanks for the feedback.