Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array Math in UDF
Here's a tricky one that I just can't seem to wrap my head around...
I'm looking at recovery rates (could be for chemical reactions, finanical transaction, etc) over time. In each period I "lose" a certain quantity, but know that I will recover a predetermined portion (say 45%) over a predetermined recovery curve (4.5% in current period, 9%, 18%, 4.5%, and 9% in period +4). I'm trying to create a UDF that will calculate the total recovered for a given period. The recovery for a given period will include the 4.5% of the "loss" in the current period plus and recovered amounts from prior period, if applicable. A simplified example is: Period | Loss | Recovered Amount 1 | 100 | 4.5 2 | 200 | 18 (9%*100 + 4.5%*200) 3 | 100 | 40.5 4 | 0 | 49.5 5 | 0 | 36 6 | 0 | 22.5 7 | 0 | 9 So, the total recovered amount is 180, which is 45% of the 400 that was lost. If anyone help me get started or point me to something that solved for a similar problem, I would greatly appreciate it. Thanks, Steve |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array Math in UDF
I seem to have answered my own problem, so thought I'd post the
solution: Function Recovery(RecovArray, LossArray, PeriodArray, Period As Single) As Double Dim Element As Variant Dim SumRecov As Double Dim Iteration As Integer Dim LossOffset As Integer Dim CalcPeriod As Integer Iteration = 0 LossOffset = Period - 1 Do SumRecov = SumRecov + RecovArray(Period - Iteration) * LossArray(Period - LossOffset) Iteration = Iteration + 1 LossOffset = LossOffset - 1 CalcPeriod = CalcPeriod + 1 Loop Until CalcPeriod = Period Recovery = SumRecov End Function |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
changing math operations for math operations with = sign | Excel Programming | |||
What is the math behind array function | Excel Discussion (Misc queries) | |||
Redimming an array dynamically assigned from range (how to redim first dimension of a 2-D array? /or/ reverse the original array order) | Excel Programming | |||
Matrix Math using LOOKUP inside Array {} Function | Excel Worksheet Functions | |||
efficiency: database functions vs. math functions vs. array formula | Excel Discussion (Misc queries) |