Given a list of numbers, say 7,11,13,19,24,30 and a certin given number say 45 Is thera a formula that will take any 2 numbers in the list add them together to give you a sum that comes closest to the given number. and tell you what the two numbers are that was used.
Sum Until A Given Number



Re: Formula To Match A Given Number With The Nearenst Sum.
This sum link may be of interest
http://www.dailydoseofexcel.com/archives/2005/10/27/whichnumberssumtotarget/
VBA Noob

Re: Formula To Match A Given Number With The Nearenst Sum.
the given number will change, but the list numbers will remain the same[hr]*[/hr] Auto Merged Post Until 24 Hrs Passes;[dl]*[/dl]VBA is just a little bit out of my league. I am just trying to set up an easy way to figure out IF i have an R30 to an R48 or more roof insulation and I have standard Insulation thickness of 2.5" (R7), 3" (R10), and so forth up to 9" (R30). We have to lay between the rafters up to R30 but also need to lay crossways an aditional layer up to but not to exceed 6" R19 What is the best combonation so that i then can use those numbers to quote with is an excel file

Re: Sum Until A Given Number
QuoteVBA is just a little bit out of my league.
You don't have to know how to write the code, just know where to put it and how to run it.The code, via the link VBA Noob provided , is exactly what you need. Quite a slick piece of coding, actually.
I've attached the workbook from the link, modified it to include your values and annotated the text. There is a button in the workbook to run the code, so it doesn't get any easier. Just select your range of values and press the button, enter a target Rnumber (say, 48), and press OK. Voila! You have your combinations.

Re: Sum Until A Given Number
Seams ok But It gives me for R48, R24 & R11 and R13, Two layer answers that is close, would be prefered (R30 & R19) (even thought it is 1 value more the savings cost in labor well pays for it). then how do I take this nifty button and put it in my work sheet so that when I enter the desired Rvalue It comes up with the best combination and put the numbers in the estimating worksheet to be used to price the roof insulation.

Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!