+ Reply to Thread
Results 1 to 2 of 2

Looking for a SUM Best Match Formula?

  1. #1
    Registered User
    Join Date
    06-17-2011
    Location
    Phoenix, AZ
    MS-Off Ver
    Google Spreadsheets & Excel:Mac 2008
    Posts
    1

    Exclamation Looking for a SUM Best Match Formula?

    I am needing a non-Macro or Solver approach to this problem:

    1. I have a pole (say...360 cm) that needs to be built using two different length pieces (20 & 50 cm).
    2. I need a best combination of the building pieces so that I am given exactly or slightly over the total pole height.
    3. I was using TRUNC w/ 50s first and then filling the rest with 20s, until I realized that is not accurate. (360 would actually be 6x50s and 3x20s)
    4. I am forced to use Google Spreadsheets for this "calculator", so it must not contain macros or use Solver.

    Thank you for your help!

  2. #2
    Forum Expert Colin Legg's Avatar
    Join Date
    03-30-2008
    Location
    UK
    MS-Off Ver
    2003, 2007, 2010, 2013
    Posts
    1,252

    Re: Looking for a SUM Best Match Formula?

    Hi and welcome to the forum.

    If the length of pole is in A2, then this formula in C2 will give you the number of 50 length pieces required:
    Please Login or Register  to view this content.
    And this formula in D2 will give you the number of 20 piece lengths required:
    Please Login or Register  to view this content.
    These assume that the length of pole is given to the nearest 10cm.




    If the length of the pole is not given to the nearest 10cm, then you can use these variations instead:
    Please Login or Register  to view this content.
    Please Login or Register  to view this content.
    Last edited by Colin Legg; 06-17-2011 at 06:58 PM. Reason: corrected cell reference
    Hope that helps,

    Colin

    RAD Excel Blog

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1