Weighted Average Using Dates

[jdavis]

I have this really ridiculous lengthy formula, Column N, that is calculating the number of days that a specific loan is open and creating a weighted average using A1 as the date for those days to be included. The resulting days are then divided by 365.25 to get a percent of year, Column O. That percentage is then multiplied by the Loan Value arriving at a Weighted Average - Loan Value.

Cut to the chase I need a shorter formula that calculates the days and then provides the Weighted Loan Value.

Thanks

 

[Chihiro]

For N3: =IFERROR(INDEX(C3:K3,,MATCH($A$1,C3:K3,1)+1)-$A$1,C3-$A$1)
Copy down.

 

[jdavis]

That works, thanks you have saved me again.

 

[John Jairo V]

Hi!

Another approach:
=INDEX(C3:K3,MATCH(0,INDEX(($A$1>C3:K3)*C3:K3,),))-$A$1

Blessings!

 

[r1c1]

Awesome solution @Chihiro

Out of curiosity, I experimented with other alternatives. Here is one that tallies the results (not sure if it will work for all conditions).

=IFERROR(LOOKUP($A$1,C3:J3,D3:K3),C3)-$A$1

 

[Chihiro]

@r1c1
Cool. I should really learn use of LOOKUP and HLOOKUP. I tend to use INDEX,MATCH and VLOOKUP only for most of my work.

@John Jairo V
Your solution I think is more flexible. I like it.