defined sheet formula to vb code

[ajn1155]

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

 

[Niek Otten]

What is this formula supposed to achieve? Please explain in words what you are trying to do.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

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

 

[ajn]

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.