+ Reply to Thread
Results 1 to 1 of 1

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

  1. #1
    Registered User
    Join Date
    MS-Off Ver
    Excel 2007

    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.
    Last edited by Paul; 02-23-2011 at 12:53 PM. Reason: Combined posts

+ Reply to Thread

Thread Information

Users Browsing this Thread

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