Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 08-24-2017, 11:19 AM
Notryonlydo Notryonlydo is offline Finding the optimal value in a set of data Windows 10 Finding the optimal value in a set of data Office 2016
Novice
Finding the optimal value in a set of data
 
Join Date: Aug 2017
Posts: 3
Notryonlydo is on a distinguished road
Default Finding the optimal value in a set of data

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?
Reply With Quote
  #2  
Old 08-24-2017, 01:28 PM
NoSparks NoSparks is offline Finding the optimal value in a set of data Windows 7 64bit Finding the optimal value in a set of data Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 831
NoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really nice
Default

Hello No try only do

Am I missing something here ?

Is there no allowance for saw kerf ?
For square footage, wouldn't you need to know raw lumber width, raw lumber length and quantity of raw lumber ?
Reply With Quote
  #3  
Old 08-24-2017, 06:17 PM
Notryonlydo Notryonlydo is offline Finding the optimal value in a set of data Windows 10 Finding the optimal value in a set of data Office 2016
Novice
Finding the optimal value in a set of data
 
Join Date: Aug 2017
Posts: 3
Notryonlydo is on a distinguished road
Default

yes there is saw kerf but I am trying to keep things simple. my goal is an Excel file that can do the analysis and that I can use to enter various widths and see the resulting waste percentage. Ignore saw kerf for this question.

yes, the data will be entered in a table - the length and width of every board in the bundle. What I am asking here is how to set up the file that will analyze the data in that table.
Reply With Quote
  #4  
Old 08-24-2017, 08:12 PM
NoSparks NoSparks is offline Finding the optimal value in a set of data Windows 7 64bit Finding the optimal value in a set of data Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 831
NoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really nice
Default

I just put a couple numbers on a sheet and did a macro for them.
Without seeing your table I can't tell how to analyze it other than to say you'll likely need something similar.

Ignoring the kerf doesn't make things simpler, it makes them wrong. If you really want to ignore the kerf, enter it as 0.

Hopefully this will help
Good Luck
Attached Files
File Type: xlsm Optimal_Flooring.xlsm (18.7 KB, 12 views)
Reply With Quote
  #5  
Old 08-30-2017, 10:30 AM
Notryonlydo Notryonlydo is offline Finding the optimal value in a set of data Windows 10 Finding the optimal value in a set of data Office 2016
Novice
Finding the optimal value in a set of data
 
Join Date: Aug 2017
Posts: 3
Notryonlydo is on a distinguished road
Default

Thanks NoSparks!
Can you put that formula used in the macro in a cell? i'd rather have it in a cell that I can copy. Or at least tell me what the formula is.
Reply With Quote
  #6  
Old 08-30-2017, 10:55 AM
NoSparks NoSparks is offline Finding the optimal value in a set of data Windows 7 64bit Finding the optimal value in a set of data Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 831
NoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really nice
Default

No I can't, I'm go good with formulas, but if you were to attach a workbook with a typical "Table" of what you would be working with and what/where you're wanting results I can show you how to apply the macro to it.
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Finding the optimal value in a set of data finding data hidden in fields John Witmer Word 2 03-13-2017 01:34 AM
finding variations in the data base adryan Excel 4 07-15-2015 12:19 AM
Finding the optimal value in a set of data finding the data source for a scatter graph Natalie728 Excel 1 01-29-2015 09:21 AM
Finding sub-sets of data in worksheets Rudi Excel 3 01-28-2013 06:13 AM
Finding the optimal value in a set of data Finding the complements between the two data sets psenku Excel 2 08-17-2009 11:15 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 12:40 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft