Sum Until A Given Number

Important Notice


Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.

    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.

    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 R-30 to an R-48 or more roof insulation and I have standard Insulation thickness of 2.5" (R-7), 3" (R-10), and so forth up to 9" (R-30). We have to lay between the rafters up to R-30 but also need to lay crossways an aditional layer up to but not to exceed 6" R-19 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


    Quote

    VBA 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 R-number (say, 48), and press OK. Voila! You have your combinations.

    Re: Sum Until A Given Number


    Seams ok But It gives me for R-48, R-24 & R-11 and R-13, Two layer answers that is close, would be prefered (R-30 & R-19) (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 R-value 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!

Register Yourself Login