Calculating number of periods

[Ari]

Hello,

I have a number of unequal periods in a year. For this example say 5
periods. There are no actual dates here, just a raw number of months (number
format for column C) in cells B1 : C5.

P1: 1
P2: 2
P3: 3.4
P4: 2
P5: 3.6

The numbers in column C will always add up to 12.

I am searching for a formula that will calculate the number of periods (X)
that make up Y months, moving backwards from period Z. Y and Z are given. X
is what I am looking to calculate. For example:

Example 1
Cell A1 = Y (# Months) = 5
Cell A2 = Z (Period Beginning) = 4
Cell A3 = X (Formula) = 1.882*

* because
P4 = 1 using up 2 months
P3 = .882 using up the remaining 3 months

Example 2
Cell A1 = Y (# Months) = 5.8
Cell A2 = Z (Period Beginning) = 3
Cell A3 = X (Formula) = 2.4*

* because
P3 = 1 using up 3.4 months
P2 = 1 using up 2 months
P1 = .4 using up the remaining .4 months

Any help would be very much appreciated. I have considered building an
intermediate table to help the formula, but any advice would be great.

Thanks,

Ari Blum

 

[Roger Govier]

Hi Ari

I think that this does what you want, with your periods held in C1:C5

=IF($A$1-INDEX(C$1:$C$5,$A$2)<=INDEX($C$1:$C$5,$A$2-1),
($A$1-INDEX($C$1:$C$5,$A$2))/INDEX($C$1:$C$5,$A$2-1),
(($A$1-INDEX($C$1:$C$5,$A$2)-INDEX($C$1:$C$5,$A$2-1))/
INDEX($C$1:$C$5,$A$2-2))+INDEX($C$1:$C$5,A2-1))

 

[Ari]

Thank you Roger. This was incredibly helpful.

Best,

Ari Blum