Calculating number of periods
 

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

Calculating number of periods
 

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))

--
Regards
Roger Govier

"Ari" wrote in message
...
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

Calculating number of periods
 

Thank you Roger. This was incredibly helpful.

Best,

Ari Blum

"Roger Govier" wrote:

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))

--
Regards
Roger Govier

"Ari" wrote in message
...
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