+ Reply to Thread
Results 1 to 9 of 9

Need help creating formular to max possible sum values.

  1. #1
    Registered User
    Join Date
    06-23-2018
    Location
    Denmark
    MS-Off Ver
    Microsoft Office 365
    Posts
    5

    Need help creating formular to max possible sum values.

    Hello there.

    I have basic knowledge of Excel and some of the formulars, and now I need some help with the following.

    I want a function/formular to calculate the highest or lowest possible value of a sum in column A, based on a given number that must be split into 1-5 cells in column D (That given number = sum of the 1-5 cells). Each number in these cells are connected to a separate sheet, that generates the value in column A (main sheet).

    For example (see attached document), if I choose the number 100, I want the sheet to calculate the highest or lowest possible value of the sum of cell A3:A7, by optimizing all values in D3:D7 with all real numbers (up to 2 max decimal places) from 0 to 100 (the given number). All those numbers in B3:B7 will then generate the lowest or highest value by distributing my given number in the 5 cells in column D.

    All help is appreciated, thanks.

    //
    Rune H.
    Attached Files Attached Files
    Last edited by Dejligrar; 06-23-2018 at 10:06 PM. Reason: Changed description to match new attached document.

  2. #2
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: Need help creating formular to max possible sum values.

    Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
    My General Rules if you want my help. Not aimed at any person in particular:

    1. Please Make Requests not demands, none of us get paid here.

    2. Check back on your post regularly. I will not return to a post after 4 days.
    If it is not important to you then it definitely is not important to me.

  3. #3
    Registered User
    Join Date
    06-23-2018
    Location
    Denmark
    MS-Off Ver
    Microsoft Office 365
    Posts
    5

    Re: Need help creating formular to max possible sum values.

    Thanks for replying so quickly. I'll do this tomorrow, as I am heading straight to bed now. Thanks again.

  4. #4
    Registered User
    Join Date
    06-23-2018
    Location
    Denmark
    MS-Off Ver
    Microsoft Office 365
    Posts
    5

    Re: Need help creating formular to max possible sum values.

    Okay so I couldn't sleep, cause I was trying to solve the issue described.
    I have now attached a document with just enough information to give an understanding of what I need done.
    The automated process I need is described in the best way I could, so I hope it does make sence.
    I cannot make an aftersheet or show the optimized values im looking for, as the math for that is to complicated for me to solve, not having a tool like what I'm asking for here.

    Thanks once again. Looking forward to see what any of you outhere figure out.

  5. #5
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Need help creating formular to max possible sum values.

    [deleted by me]

  6. #6
    Registered User
    Join Date
    06-23-2018
    Location
    Denmark
    MS-Off Ver
    Microsoft Office 365
    Posts
    5

    Re: Need help creating formular to max possible sum values.

    I'm ready (and happy) to provide any additional information if it helps in any way getting to a solution.

  7. #7
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,513

    Re: Need help creating formular to max possible sum values.

    how 100 is distributed. What is the basis for defining a value in each row of column D.
    Your example is not able clear this.
    Pl note
    Array formula should be confirmed with Ctrl+Shift+Enter keys together.
    If answere is satisfactory press * to add reputation.

  8. #8
    Registered User
    Join Date
    06-23-2018
    Location
    Denmark
    MS-Off Ver
    Microsoft Office 365
    Posts
    5

    Re: Need help creating formular to max possible sum values.

    I'm afraid I might have been explaining it to poorly. I also need the distribution of the 100 to be calculated.

    Basicly what I'm looking for is a function that tries to distribute the chosen value 100 between the 5 cells (D3:D7)
    in every possible way with all real numbers from 0 to the chosen value 100 (with 2 decimal points, if possible).

    Another example, I choose the value 200, I want D3:D7 to have the new sum 200, with values distributed most efficiently,
    so that the value in A8 becomes yet again the highest or lowest value possible.

  9. #9
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: Need help creating formular to max possible sum values.

    This submission addresses the statement: "distribute the chosen value 100 between the 5 cells (D3:D7) in every possible way with all real numbers from 0 to the chosen value 100 (with 2 decimal points, if possible)"
    I assume that "in every way" means randomly. If that is the case then I submit that it is possible using helper columns (G:H).
    Cells G3:G6 are populated using: =RANDBETWEEN(0,D$10-SUM(D$2:D2))
    Cells H3:H6 are populated using: =IF(SUM(D$2:D2,G3)<D$10,MIN(D$10-SUM(D$2:D2,G3),ROUND(RAND(),2)),0)
    Cells D3:D6 are populated using: =G3+H3
    Cell D7 is populated using: =D10-SUM(D2:D6)
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Creating a timesheet formular
    By bufftom in forum Excel General
    Replies: 33
    Last Post: 09-15-2014, 12:14 PM
  2. Complicated formular. Formular, remove text if available
    By TheTrooper1 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-02-2014, 12:39 PM
  3. [SOLVED] Replacing a date formular result with another formular
    By stpeter in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 08-06-2013, 06:27 PM
  4. Replies: 1
    Last Post: 05-23-2013, 02:06 AM
  5. [SOLVED] Array formular - Return only unique values
    By jackyong1985 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-20-2012, 07:17 AM
  6. Need help with formular for a split in values
    By SJC2006 in forum Excel - New Users/Basics
    Replies: 3
    Last Post: 11-21-2006, 06:01 AM
  7. if () formular disappearing when values change
    By Excel 2003 - SPB in forum Excel General
    Replies: 1
    Last Post: 08-05-2006, 10:45 AM

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