# Replace hard coded array in formula with link to input array

1. ## 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. ## 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))

3. ## 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. ## 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. ## 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

6. ## 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. ## 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

Originally Posted by JBeaucaire
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))

8. ## 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. ## 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

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