Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
defined sheet formula to vb code
I have this huge formula in the cells of a matrix that have
tremendously increased the size of the excel file and decreased efficiency. I tried to convert this into a vba function but could not find a vba equivalent function for 'offset' and 'rows'. Don't know if there is a way to do that?? or if I'm going the wrong path and there is a more effective suitable way to convert this into a 'function' and/or macro. Will appreciate your help! = IF((B3=0) + (B3 < 0.05),0,OFFSET($B$266,MATCH (INDEX($B$3:$CA$80,MATCH($A341,$A$3:$A$80,0),MATCH ( B$340,$B$2:$CA$2,0)),$A$267:$A$330,1),ROWS($A$171: $A$193)* INDEX($B$171:$B$193,MATCH(INDEX($D$87:$CC$164, MATCH($A341,$A$87:$A$164,0),MATCH(B$340,$D$85:$CC$ 85,0)), $A$171:$A$193,0))+INDEX($C$171:$C$193, MATCH(INDEX($D$87:$CC$164,MATCH($A341,$A$87:$A$164 ,0), MATCH(B$340,$D$85:$CC$85,0)),$A$171:$A$193,0),1))) |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
defined sheet formula to vb code
What is this formula supposed to achieve? Please explain in words what you are trying to do.
-- Kind regards, Niek Otten Microsoft MVP - Excel wrote in message oups.com... |I have this huge formula in the cells of a matrix that have | tremendously increased the size of the excel file and decreased | efficiency. | I tried to convert this into a vba function but could not find a vba | equivalent function for 'offset' and 'rows'. Don't know if there is a | way to do that?? or if I'm going the wrong path and there is a more | effective suitable way to convert this into a 'function' and/or macro. | Will appreciate your help! | | = IF((B3=0) + (B3 < 0.05),0,OFFSET($B$266,MATCH | (INDEX($B$3:$CA$80,MATCH($A341,$A$3:$A$80,0),MATCH ( | B$340,$B$2:$CA$2,0)),$A$267:$A$330,1),ROWS($A$171: $A$193)* | INDEX($B$171:$B$193,MATCH(INDEX($D$87:$CC$164, | MATCH($A341,$A$87:$A$164,0),MATCH(B$340,$D$85:$CC$ 85,0)), | $A$171:$A$193,0))+INDEX($C$171:$C$193, | MATCH(INDEX($D$87:$CC$164,MATCH($A341,$A$87:$A$164 ,0), | MATCH(B$340,$D$85:$CC$85,0)),$A$171:$A$193,0),1))) | |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
defined sheet formula to vb code
In the worksheet,
there are 2 78X78 matrices and 2 tables table 1: 2 columns containing 'level' and corresponding 'rate' table 2: contains price pertaining to weight (in increments of 0.5), level, rate and price matrix1: contains 'weight' values from 0 to 30 in intervals of 0.5 in each of its cells matrix2: contains 'rate' value in each cell matrix3: contains the huge formula in each of its cells, the formula does the following : 1)looks up the corresponding cell from matrix1, grabs the 'weight' value 2)looks up the corresponding cell from matrix2, grabs the 'rate' value 3) looks up table 1, grabs 'level' corresponding to rate 4) using weight, rate and level, looks up table 2 to get the price which is stored in cells of matrix 3 i can send you a excel file if that is okay, since its easier to interpret looking at the worksheet while reading the above description. Niek Otten wrote: What is this formula supposed to achieve? Please explain in words what you are trying to do. -- Kind regards, Niek Otten Microsoft MVP - Excel wrote in message oups.com... |I have this huge formula in the cells of a matrix that have | tremendously increased the size of the excel file and decreased | efficiency. | I tried to convert this into a vba function but could not find a vba | equivalent function for 'offset' and 'rows'. Don't know if there is a | way to do that?? or if I'm going the wrong path and there is a more | effective suitable way to convert this into a 'function' and/or macro. | Will appreciate your help! | | = IF((B3=0) + (B3 < 0.05),0,OFFSET($B$266,MATCH | (INDEX($B$3:$CA$80,MATCH($A341,$A$3:$A$80,0),MATCH ( | B$340,$B$2:$CA$2,0)),$A$267:$A$330,1),ROWS($A$171: $A$193)* | INDEX($B$171:$B$193,MATCH(INDEX($D$87:$CC$164, | MATCH($A341,$A$87:$A$164,0),MATCH(B$340,$D$85:$CC$ 85,0)), | $A$171:$A$193,0))+INDEX($C$171:$C$193, | MATCH(INDEX($D$87:$CC$164,MATCH($A341,$A$87:$A$164 ,0), | MATCH(B$340,$D$85:$CC$85,0)),$A$171:$A$193,0),1))) | |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Data exctraction from multiple sheets | Excel Discussion (Misc queries) | |||
copy and use vba code | Excel Discussion (Misc queries) | |||
Using an offset formula for the reference in a relative reference | Excel Worksheet Functions | |||
Is it possible? | Excel Worksheet Functions | |||
Reusing formula | Excel Discussion (Misc queries) |