VBA formula

[calepp]

Hello everyone!

I'm working on a financial project but I can't finish it because of one formula.

In a table I have, in first column the dates and second column a data called IDH.
In the third column, I want to have the result of this :

1.JPG
Can you help me please?

Thanks

[Paul_Hossler]

I think that there is a series of Cn's ???

It seems that each C(n) can be calculated from the C(n-1) and the IDH(n-1) before

C(n-1) = 1/ C(n-2) / (1+IDH(n-2))
C(n) = 1/ C(n-1) / (1+IDH(n-1))

(those are subscripts)

So maybe something like this


Capture.JPG

(I think this is the right screen shot)

[SamT]

What do the dates have to do with the formula?

Here are two UDF's (User Defined Functions) that can be used like any Excel Function. To use them, In VBA, Insert a Module into the ThisWorkbook item, then paste these into that module.

The Cell Formula to use the first UDF is =udfCalc_IDH_Below(B9) where B9 is the top of the IDH range you want.

Function udfCalc_IDH_Below(TargetIDH As Range) As Double
'Runs formula from selected IDH to bottom of IDH Column
'Function can be Used in any cell

Dim Rng As Range
Dim Cel As Range
Dim Temp As Double

Set Rng = Range(TargetIDH.Offset(1), TargetIDH.End(xlDown))
Temp = 1 + TargetIDH

For Each Cel In Rng
 Temp = Temp * (1 + Cel)
 Next Cel
 
Calc_IDH_Below = 1 / Temp
End Function

The Cell Formula to use this UDF is =udfCalc_IDH_Date_To_Date(B9, A9, A22) where B9 is the top of the IDH range you want, A9 is the top of the Date Range you want, and A22 is the bottom of the last of the dates you want..

Function udfCalc_IDH_Date_To_Date(TargetIDH As Range, StrtDate As Range, EndDate As Range) As Double
'Runs formula on IDH from selected start date to selected end date
'Function can be used in any cell

Dim Rng As Range
Dim Cel As Range
Dim Temp As Double

Set Rng = Range(Cells(StrtDate.Row + 1, TargetIDH.Column), Cells(EndDate.Row, TargetIDH.Column))
Temp = 1 + TargetIDH

For Each Cel In Rng
 Temp = Temp * (1 + Cel)
 Next Cel
 
Calc_IDH_Date_To_Date = 1 / Temp
End Function

You can change the names of the UDF's, but I suggest that you keep the "udf" prefix in whatever name you choose.

Note that neither of these UDF's require the Columns to be in any particular location. ie, Dates in Column Z, IDH's in Column M, and formulas in Column A. The Formulas do not have to be in the same Rows as the other values, and do not even have to be on the same Sheet. The Date Rows and the IDH Rows must correspond and be on the same Sheet.

[calepp]

Thanks for your help !!!

[SamT]

You're welcome. But, who and how helped?

Should this thread be marked Solved?