+ Reply to Thread
Results 1 to 9 of 9

Replace hard coded array in formula with link to input array

  1. #1
    Registered User
    Join Date
    09-28-2010
    Location
    Middle East
    MS-Off Ver
    Excel 2003
    Posts
    66

    Replace hard coded array in formula with link to input array

    Hi - help appreciated with this formula.

    The formula works, but I have had to include hard coated arrays in the formula to make it work the only way I knew how. When I try and replace these with a link to an array, an errer reference is generated. Is there a different syntax which allows me to have the arrays referenced to an input column array?


    =if((JF9+KW9)=0,0,offset($c$208,match(min(roundup((JF9+KW9)/{276,240,192,144,96,48,24,12,8,4,2}*(1+$KR$21)/$KR$20,0)*{8,7,6,5,4,3,2,1,1,1,1}),(roundup((JF9+kw9/{276,240,192,144,96,48,24,12,8,4,2}*(1+$KR$21/$KR$20,0)*{8,7,6,5,4,3,2,1,1,1,1}),0),0))


    {276,240,192,144,96,48,24,12,8,4,2} cable array
    {8,7,6,5,4,3,2,1,1,1,1} price of cable array

    Thank you
    David

  2. #2
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Replace hard coated array in formula with link to input array

    A couple of ways.

    1) Direct cell reference

    - enter the cable array into an 11-cell range in a column with
    - enter the prices next to it in the next column over

    =IF((JF9+KW9)=0,0,OFFSET($c$208,MATCH(MIN(ROUNDUP((JF9+KW9)/($ZY$1:$ZY$11)*(1+$KR$21)/$KR$20,0)*($ZZ$1:$ZZ$11)),(ROUNDUP((JF9+KW9/($ZY$1:$ZY$11)*(1+$KR$21/$KR$20,0)*($ZZ$1:$ZZ$11)),0),0))

    2) Same thing but with named ranges

    - Enter the values as shown above but then give the ranges a NAME in the Insert>Name feature.


    =IF((JF9+KW9)=0,0,OFFSET($c$208,MATCH(MIN(ROUNDUP((JF9+KW9)/(Cables)*(1+$KR$21)/$KR$20,0)*(Prices)),(ROUNDUP((JF9+KW9/(Cables)*(1+$KR$21/$KR$20,0)*(Prices)),0),0))
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    “None of us is as good as all of us” - Ray Kroc
    “Actually, I *am* a rocket scientist.” - JB (little ones count!)

  3. #3
    Registered User
    Join Date
    09-28-2010
    Location
    Middle East
    MS-Off Ver
    Excel 2003
    Posts
    66

    Re: Replace hard coated array in formula with link to input array

    Hi thanks for getting back to me. Will method 1 work if the array is not in column beside (Method 1)? Is this different if a named array is used instead (Method 2)
    Thanks/David

  4. #4
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Replace hard coded array in formula with link to input array

    The ranges are colored in each example to draw your attention. Obviously you can put those ranges anywhere you wish and in any columns you wish. I assume you are trying things before posting questions about it not working properly.

  5. #5
    Registered User
    Join Date
    09-28-2010
    Location
    Middle East
    MS-Off Ver
    Excel 2003
    Posts
    66

    Re: Replace hard coded array in formula with link to input array

    Thanks for response. The proposed solution does not seem to work. I have attached a worked example. I have followed the logic in cell HI9 (red) and copied across, but it turns the once correct result to hash/value.

    Also, I am wondering whether there is anyway I can have the price array not beside the cable array (i.e refer column red H209)?

    Note - I have amended the formula slightly compared to what I originally posted, but I believe the change I have made would not make a difference. I have not changed anything relating to the conditionals matches, etc.

    Comment appreciated.
    Thanks/David
    Attached Files Attached Files
    Last edited by David Brown; 10-22-2010 at 09:38 AM.

  6. #6
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Replace hard coded array in formula with link to input array

    I can't read files that actually utilize ranges past column IV.

  7. #7
    Registered User
    Join Date
    09-28-2010
    Location
    Middle East
    MS-Off Ver
    Excel 2003
    Posts
    66

    Re: Replace hard coated array in formula with link to input array

    Hello,
    A little while ago you proposed the solution below to replacing hard coded arrays in a formula. At least using the Direct Cell References method, this has not worked as I would expect. Can you have a look and suggest a correction? I have attached an example Excel (Test 6) with your proposed solution (RED column HK-HL) where you will see "Value" and beside it you will see the original working with the hard coded array (GREEN column HM).

    Assistance appreciated.
    Thanks / David


    Quote Originally Posted by JBeaucaire View Post
    A couple of ways.

    1) Direct cell reference

    - enter the cable array into an 11-cell range in a column with
    - enter the prices next to it in the next column over

    =IF((JF9+KW9)=0,0,OFFSET($c$208,MATCH(MIN(ROUNDUP((JF9+KW9)/($ZY$1:$ZY$11)*(1+$KR$21)/$KR$20,0)*($ZZ$1:$ZZ$11)),(ROUNDUP((JF9+KW9/($ZY$1:$ZY$11)*(1+$KR$21/$KR$20,0)*($ZZ$1:$ZZ$11)),0),0))

    2) Same thing but with named ranges

    - Enter the values as shown above but then give the ranges a NAME in the Insert>Name feature.


    =IF((JF9+KW9)=0,0,OFFSET($c$208,MATCH(MIN(ROUNDUP((JF9+KW9)/(Cables)*(1+$KR$21)/$KR$20,0)*(Prices)),(ROUNDUP((JF9+KW9/(Cables)*(1+$KR$21/$KR$20,0)*(Prices)),0),0))
    Attached Files Attached Files

  8. #8
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: Replace hard coded array in formula with link to input array

    As JB has stated previously he can't review files in the new XL2007 format so if you want him to review post as .xls

    Regards you problem - using the formula with ranges as you have it would require array entry (ie confirmation with CTRL + SHIFT + ENTER not just enter)

    You will find that once confirmed as an Array the formula applied to your matrix generates identical results to that of the version using the inline array (sum of green = 12864)

    If you want to avoid the Array entry you can encase the necessary elements within outer INDEX calls, eg:

    Please Login or Register  to view this content.
    however, this would be less efficient than the Array given the additional function overhead - whether or not you would notice the difference is debatable (I suspect not).

    I've not reviewed the formula itself obviously.

  9. #9
    Registered User
    Join Date
    09-28-2010
    Location
    Middle East
    MS-Off Ver
    Excel 2003
    Posts
    66

    Re: Replace hard coded array in formula with link to input array

    Ok - thanks, I did not quite understand that 2007 had that limitation so now his comment makes sense. I will try the proposed solution. I am sure it will work. Thanks/D
    Last edited by David Brown; 01-08-2011 at 11:38 AM. Reason: SOLVED

+ 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