Replace hard coded 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))

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.

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

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

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.

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