# Excel 2007 : Can I assign a variable to an index function?

1. ## Can I assign a variable to an index function?

I am trying to build SS that solves a simple problem.

The problem is choosing the best wire size. In order to do this I have a table with wire ampacity and I enter the wire distance and calculate the voltage drop, I then calculate the value of the lost power and compare it with the lost power from the next size up wire. I want it to return the correct wire size if the upgrade simple payback is less than 6 years (or whatever I set it at).

Unfortunately the formula (which does work) is turning out to be a beast! Is there a better way?

The formula now is:

=IF(((INDEX(DataTables!A\$3:C\$32,MATCH(INDEX(DataTables!A\$3:C\$32,MATCH(B\$2*B\$3*500/C15/B15,DataTables!A\$3:A\$32,-1)+2,3),DataTables!C\$3:\$C32,0),2)-(INDEX(DataTables!A\$3:C\$32,MATCH((INDEX(DataTables!A\$3:C\$32,MATCH(B\$2*B\$3*500/B15/C15,DataTables!A\$3:A\$32,-1)+1,3)),DataTables!C\$3:C\$32,0),2)))*B15)/ ((INDEX(DataTables!A\$3:C\$32,MATCH((INDEX(DataTables!A\$3:C\$32,MATCH(B\$2*B\$3*500/B15/C15,DataTables!A\$3:A\$32,-1)+1,3)),DataTables!C\$3:C\$32,0),1)*2*C15*B15/1000)*E15*B\$4*B\$1/1000-(INDEX(DataTables!A\$3:C\$32,MATCH((INDEX(DataTables!A\$3:C\$32,MATCH(B\$2*B\$3*500/C15/B15,DataTables!A\$3:A\$32,-1)+2,3)),DataTables!C\$3:C\$32,0),1)*2*C15*B15/1000)*B\$4*E15*B\$1/1000)>B\$6,INDEX(DataTables!A\$3:C\$32,MATCH(B\$2*B\$3*500/C15/B15,DataTables!A\$3:A\$32,-1)+1,3), INDEX(DataTables!A\$3:C\$32,MATCH(B\$2*B\$3*500/B15/C15,DataTables!A\$3:A\$32,-1)+2,3))

Am I making this harder on myself than it needs to be?

Edit-An additional problem I am running into if for 2003 users the formula limit is 1024 characters, when I apply this formula in a more useful fashion it goes over the char limit. Again, shortening the functions would help. I already tried changing the table name to 1 char, but I was still 80 chars over.

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

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