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.
Bookmarks