Formula for decreasing value.

[omnifold]

Here is my problem. I want to know how many boards I can get out of a piece of wood.

I have a length that is 192 inches.

What I need to do is cut single boards that start with board 1 at 8 inches wide and each board after descreases by .5 inch.
so I would have

board 1 8" wide
board 2 7.5" wide
board 3 7" wide

and so on. But I would like to know how many I can get. This way I can change the .5 inch width to something else.

Thanks

[dredwolf]

do you mean it starts at 8" LONG and the length decreases by 1/2 inch ?

[omnifold]

Yes..........

[dredwolf]

Sorry for taking so long to get back

Could not figure out a single formula solution, but maybe this will work for you :omnifold.sol1.xlsx

Hope this helps

[omnifold]

Thats about what I came up with. That works. I just wanted one like loan payment formula. Another problem I am running into is

If the board is 100 feet in length and I start with an 8" piece and take off a 1/2" on each cut I will not use the whole length of the board. What I would like is where I can cut so many 8" inch pieces then start cutting 7.5" pieces and so on.

so maybe there will be 5 of each of the different cuts.

Thanks so much so far.

[dredwolf]

Sorry---was adding the saw curf, not subtracting..

Revised : omnifold.sol2.xlsx

[dredwolf]

Okay, Try this UDF solution, will work for both scenarios

Please Login or Register  to view this content.

to use, allow Macros,open VBA editor (Alt-F11), Insert Module, Copy & paste above into the module,save close editor

Parameters are
StrtLeng, FrstCutLeng, CutIncr, Optional NumCutsPer, Optional IncldCurf, Optional CurfSz, Optional ReturnOpt
StrtLeng - Starting length of your board, in decimal inches (ie 100 1/2 inches = 100.5)
FrstCutLeng - Length of your first cut(s) in decimal inches
CutIncr - length to decrease cut length by in decimal inches
NumCutsPer (Optional) - how many cuts to make at each Length (defaults to 1)
IncldCurf (Optional) - 1 = include saw curf,0 = don't include saw curf (defaults to 0)
CurfSz (Optional) - size of the saw blade curf in decimal inches (defaults to 0.125 (1/8"))
ReturnOptions (optional) 0- Total number of cuts made, 1- length of board left (if any) after as many cuts can be made using first 4 parameters, 2- the length of the last cut, in decimal inches, 3- a brief text summary of options 1,2&3 (defaults to 0)

See attachment for examples

Hope this helps

[omnifold]

Thanks you so much for the time spent. The unkown is the cuts per length. That is what I really want.

So if the length is 192 and cuts per length = 1 it will give me 50cuts.
2 errors
1 is if I double the 192 and make it 386 and keep the cuts per length =1 it still returns 50 cuts
2nd error is if i take the cuts per length = 2.5 the cuts will remain 8 inches

[dredwolf]

1) if you run out of decreases, doubling the starting board length wont increase the number of cuts, it will just increase the amount of board you have left at the end of the sequence of cuts
2) as for 2.5 cuts per lenth...how would you accomplish this? I have never been able to do it with my saw ! the number of cuts is supposed to be an integer, ( the display using the helper columns assumed an integer # of cuts, so you keep seeing 8"(I left it in for error checking the UDF)), however, this did point out a flaw in the logic of the UDF as well --- I did not check for that !!!

Please Login or Register  to view this content.

new code forces the number of cuts to an integr value, and I updated the Formula version as well to display interim results properly
Same Parameters as before

Hope this helps

[dredwolf]

Forgot the example , then I couldn't get back on!
Anyways, here''s the example

Remmeber, the UDF is displaying its results in Column I, the results returned in columns D,E and F are all from Excel Functions alone, just to visualize the What the UDF is doing