Working with named ranges in UDFs

[JenniferMurphy]

I would like to to do some fairly complicated calculations in a UDF. And I would like to do them using named ranges, if possible.

Here's a sample table. The input data is in Columns D, E, & F. These columns have been assigned the names T, A, & B. Column G contains the calculated result. The formulas in Column G are shown in Column H. Each result in G is calculated from the data in the 2x3 range of cells in Columns D, E, & F and the current row plus the previous row. For example, the data in G4 is calculated from the data in cells D4:F5. For the last row, the "previous" row is the top row, so the value in G8 is calculated from the data in cells D8:F8 & D4:F4.

[TABLE="class: grid, width: 850"]
<tbody>[TR]
[TD="align: center"]R/C[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"]H[/TD]
[TD="align: center"]I[/TD]
[TD="align: center"]J[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]#[/TD]
[TD="align: center"]T[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]X[/TD]
[TD="align: center"]Formula in Col G[/TD]
[TD="align: center"]UDF[/TD]
[TD="align: center"]Original Equation[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]82.42[/TD]
[TD="align: center"]20[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]3.52[/TD]
[TD="align: center"]=(D4*F5-D5*F4)/(E4*F5-E5*F4)[/TD]
[TD="align: center"]=MyUDF(T,A,B)[/TD]
[TD="align: center"]=(T4*B5 - T5*B4)/(A4*B5 - A5*B4)[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]33.09[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]0.99[/TD]
[TD="align: center"]=(D5*F6-D6*F5)/(E5*F6-E6*F5)[/TD]
[TD="align: center"]=MyUDF(T,A,B)[/TD]
[TD="align: center"]=(T5*B6 - T6*B5)/(A5*B6 - A6*B5)[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]35.07[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]3.77[/TD]
[TD="align: center"]=(D6*F7-D7*F6)/(E6*F7-E7*F6)[/TD]
[TD="align: center"]=MyUDF(T,A,B)[/TD]
[TD="align: center"]=(T6*B7 - T7*B6)/(A6*B7 - A7*B6)[/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]27.54[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]3.65[/TD]
[TD="align: center"]=(D7*F8-D8*F7)/(E7*F8-E8*F7)[/TD]
[TD="align: center"]=MyUDF(T,A,B)[/TD]
[TD="align: center"]=(T7*B8 - T8*B7)/(A7*B8 - A8*B2)[/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]16.61[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]3.87[/TD]
[TD="align: center"]=(D8*F4-D4*F8)/(E8*F4-E4*F8)[/TD]
[TD="align: center"]=MyUDF(T,A,B)[/TD]
[TD="align: center"]=(T8*B4 - T4*B8)/(A8*B4 - A4*B8)[/TD]
[/TR]
</tbody>[/TABLE]

Column I shows the UDF call I would like to be able to make. Column J shows the original equation that I am trying to implement. I would like the code in the UDF to look as much like that equation as possible.

If I could make the code in the sheet look like that equation, I probably wouldn't need the UDF.

Note that the results are added from the bottom up. New results are added at the top.

Thanks for any help...