Math and excel challenge for someone who knows excel:

A bundle of raw lumber has a hundred or more boards of random width and length. We want to find the optimal flooring blank width between 4" and 7" that maximizes square footage of flooring blanks and minimizes waste.

Parameters
> Only one width (not multiple widths)
> no gluing up narrow pieces to yield a wider board



Example - if 5" is chosen for blank size:
> All boards with widths narrower than this are waste
> a 6" board yields one 5" blank x length and 1" x the length of waste
> a 10" board yields two 5" blanks and zero waste
> a 9.9" board yields one 5" blank and 4.9" of waste.
> a 12" board two 5" blanks and 2" of waste.

Desired Excel outcome:
> a box to enter the width I want to try, with three outcome boxes:

1, total square footage of usable flooring blanks from the stack
2, total square footage of waste
3, ratio of usable flooring vs waste.

any ideas of how to set this up?