Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculating overlapping time periods | Excel Worksheet Functions | |||
Calculating overlapping time periods | Excel Worksheet Functions | |||
Calculating overlapping time periods | Excel Worksheet Functions | |||
calculating number of three month periods between two dates... | Excel Discussion (Misc queries) | |||
Calculating number of periods when payment amount changes | Excel Worksheet Functions |