+ Reply to Thread
Results 1 to 10 of 10

Need to Automatically fill out an Array (Linear Programming?)

  1. #1
    Registered User
    Join Date
    06-28-2017
    Location
    Dallas
    MS-Off Ver
    2016
    Posts
    54

    Need to Automatically fill out an Array (Linear Programming?)

    Hi,

    I have attached a spreadsheet that is a representation of a much larger project I am working on. I need a way to automatically fill out an array (Highlighted in yellow) based on the numbers in column D and row 6. There are 2 sheets in this workbook. You can see that the colors correspond to the cell that will change. For example, in my array, when I change cell F2 to .1, I want the cell in the array (F7) to equal the number that would be spit out in cell I3 (In this case it would be .3). Similarly, when I change the green cell C4 on Sheet2 to 4, the number cell I3 would spit out would be .2, so cell E8 would = .2.

    So what I am looking for is a way to fill in my array automatically, without me having to go in and manually type in the numbers each time.

    If something on here doesn't make sense ask me, I will be at my computer.
    Attached Files Attached Files

  2. #2
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Need to Automatically fill out an Array (Linear Programming?)

    Similarly, when I change the green cell C4 on Sheet2 to 4, the number cell I3 would spit out would be .2, so cell E8 would = .2.
    You lost me there.

    When I changed "the green cell C4 on Sheet2 to 4" I get 0.4 in I3

    What have I missed?
    Dave

  3. #3
    Registered User
    Join Date
    06-28-2017
    Location
    Dallas
    MS-Off Ver
    2016
    Posts
    54

    Re: Need to Automatically fill out an Array (Linear Programming?)

    1. The orange cell F2 on the main sheet should be set at .05
    2. Change the green cell on Sheet2 (C4) to 4.
    3. Cell I3 on the main sheet should now =.2
    4. Cell E8 should still be blank after you make these changes, but I want to be able to fill out the array automatically.

    Let me know if you need further clarification.

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

    Re: Need to Automatically fill out an Array (Linear Programming?)

    I believe that you would like only the "intersection" cell, of the numbers in Sheet1 F2 and Sheet2 C4, to display a value.
    If that is the case then paste the following into cell E7 and drag over and down: =IF(AND($F$2=E$6,Sheet2!$C$4=$D7),PRODUCT(E$6,$D7),"")
    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  5. #5
    Registered User
    Join Date
    06-28-2017
    Location
    Dallas
    MS-Off Ver
    2016
    Posts
    54

    Re: Need to Automatically fill out an Array (Linear Programming?)

    Thank you for your response. I may not have been clear enough in my description because your formula works, but it only shows one cell in the array at a time, and its based on the numbers I have in the input cells. This solution does not solve my issue because I need the ENTIRE array to be filled out at once based on the corresponding numbers I have in column D and row 6, without having to go in and change the numbers each time.

    So I want the end product to have all 9 cells in the array to be filled out with the correct numbers. This may require some sort of what if analysis or function in Solver, I am just not sure what to do.

  6. #6
    Registered User
    Join Date
    06-28-2017
    Location
    Dallas
    MS-Off Ver
    2016
    Posts
    54

    Re: Need to Automatically fill out an Array (Linear Programming?)

    I have attached a revised excel sheet to show what the end product should look like.
    Attached Files Attached Files

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

    Re: Need to Automatically fill out an Array (Linear Programming?)

    I feel as if I am still not understanding, however =PRODUCT(E$6,$D7) pasted into cell E7, then dragged across and down yields the same results as those manually placed in E12:G14.
    Let us know if you have any questions.

  8. #8
    Registered User
    Join Date
    06-28-2017
    Location
    Dallas
    MS-Off Ver
    2016
    Posts
    54

    Re: Need to Automatically fill out an Array (Linear Programming?)

    Yes, you are correct about that formula working in the spreadsheets current state, but I want the array to auto populate based on the number that would be spit out in cell I3 (the yellow cell) on the "main" sheet.

    I have attached another spreadsheet in which I have changed cell F3 on the "main" sheet from 1 to .5. This would completely change the numbers spit out in the array, so a simple product function would not work.


    To try and make this clearer, I have filled cell F2 on the "main" sheet and C4 on "sheet2" in red. These are the two cells that the array is based off of. I want the array to be filled out so that it shows the result (cell I3 "main" sheet) when both the red cells equal the corresponding row and column of the array.

    Here's 2 examples:

    1.) Look at cell E8. The X-axis = .05 and the Y-axis = 4. This means that the number I want in cell E8 = cell I3, when cell F2 on the "main" sheet = .05, and when cell C4 on "Sheet2" = 4. F2 corresponds to the X-axis, C4 corresponds to the Y-axis.

    The result from this... Cell E8 would = .1

    2.) Look at cell E9. The X-axis = .05 and the Y-axis = 5. This means that the number I want in cell E9 = cell I3, when cell F2 on the "main" sheet = .05, and when cell C4 on "Sheet2" = 5. F2 corresponds to the X-axis, C4 corresponds to the Y-axis.

    The result from this... Cell E9 would = .125

    What I want is for the array to be filled out in one calculation - without me having to manually type it in every time and copy paste the answer in cell I3 to each cell in the array.
    Attached Files Attached Files

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

    Re: Need to Automatically fill out an Array (Linear Programming?)

    Try pasting the following into cell E7 and dragging over and down: =E$6*$F$3*$D7*Sheet2!$C$5
    I tested with all nine combinations of Sheet1 F2 and Sheet2 C4 and they all agree with cell I3 on Sheet 1.
    Let us know if you have any questions.

  10. #10
    Registered User
    Join Date
    06-28-2017
    Location
    Dallas
    MS-Off Ver
    2016
    Posts
    54

    Re: Need to Automatically fill out an Array (Linear Programming?)

    JeteMc, your formula works 100% for this workbook.

    However, the real workbook I am working on has a variety of other calculations that back the actual number that would be in I3, including IRR calculations and others, so unfortunately this solution still does not work for me. I can't post the real workbook (ore even a representation of it) because it has a lot of complex formulas and sensitive information. I need a solution that involves some type what if analysis or solver to compute the array. I'm not even sure if this can be done in excel, but I would think there is a way.

+ 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. Fill in blank cells with linear values with linear percentage increases
    By jstanley41 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-21-2018, 03:31 PM
  2. Help with Linear Programming
    By Cadis in forum Excel General
    Replies: 1
    Last Post: 10-15-2014, 01:48 PM
  3. Solver add-in linear programming problem
    By Stewart723 in forum Excel Programming / VBA / Macros
    Replies: 17
    Last Post: 05-11-2014, 01:17 PM
  4. Linear Programming
    By obbor in forum Excel General
    Replies: 2
    Last Post: 03-06-2012, 05:43 PM
  5. Linear Programming Question
    By ScabbyDog in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 03-05-2009, 09:06 AM
  6. Linear Programming
    By Franklin in forum Excel General
    Replies: 2
    Last Post: 01-22-2005, 06:06 PM

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