Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Moving numbers to different months
I can not work how to solve the follwing problem, please bear with me.
I have a loan e.g. £3m taken out in Nov 07 This loan will have the following drawings (negative number in months) and final repayment (postive number in months) 2008 Jan, Feb, Mar, Apr, May Jun Jul Aug Sept etc (50k) (50k) 3100k =zero (ie -3000-50-50+3100) What I want to do is to move the repayment amount (£3100k) to different months, depending on the number of months I input into a particular cell. For example the cell is in a sheet that makes certain assumptions. If I type 5 in this cell, the repayment amount of £3100k should move 5 month from Mar 08 to Aug 08, If I type 3 it should move Jun 08. Just to let you know Mar is the maturity date of this loan.But I need to have the ability to change the number of months I don't know how to factor in an IF statement that could have a range from 1 month to poss 24 months. ie more than 1 If statements. If you do answer could you please explain it in simply terms as my excel skills are average to say the least. Thank you in adance for your help. Best regards Paul |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Moving numbers to different months
Hi Paul.
From the Italian NG, by Mr. Ivano, a possible solution: in A2 starting date 01/03/2007 in B2 rate numbers 5 in C2 total amount 5000 in D2:M2 =IF(AND(($B2+MONTH($A2)-1)=(COLUMN(D2)-3);MONTH($A2)<=(COLUMN(D2)-3));$C2/$B2;"-") in D1:M1 month 1:10 jan : oct extend the copy of the formula in accordance with your riferiments. Regards, Eliano "Paul" wrote: I can not work how to solve the follwing problem, please bear with me. I have a loan e.g. £3m taken out in Nov 07 This loan will have the following drawings (negative number in months) and final repayment (postive number in months) 2008 Jan, Feb, Mar, Apr, May Jun Jul Aug Sept etc (50k) (50k) 3100k =zero (ie -3000-50-50+3100) What I want to do is to move the repayment amount (£3100k) to different months, depending on the number of months I input into a particular cell. For example the cell is in a sheet that makes certain assumptions. If I type 5 in this cell, the repayment amount of £3100k should move 5 month from Mar 08 to Aug 08, If I type 3 it should move Jun 08. Just to let you know Mar is the maturity date of this loan.But I need to have the ability to change the number of months I don't know how to factor in an IF statement that could have a range from 1 month to poss 24 months. ie more than 1 If statements. If you do answer could you please explain it in simply terms as my excel skills are average to say the least. Thank you in adance for your help. Best regards Paul |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Moving numbers to different months
Thank you very much, I will study and reply in due course. Once again many thanks Paul "eliano" wrote: Hi Paul. From the Italian NG, by Mr. Ivano, a possible solution: in A2 starting date 01/03/2007 in B2 rate numbers 5 in C2 total amount 5000 in D2:M2 =IF(AND(($B2+MONTH($A2)-1)=(COLUMN(D2)-3);MONTH($A2)<=(COLUMN(D2)-3));$C2/$B2;"-") in D1:M1 month 1:10 jan : oct extend the copy of the formula in accordance with your riferiments. Regards, Eliano "Paul" wrote: I can not work how to solve the follwing problem, please bear with me. I have a loan e.g. £3m taken out in Nov 07 This loan will have the following drawings (negative number in months) and final repayment (postive number in months) 2008 Jan, Feb, Mar, Apr, May Jun Jul Aug Sept etc (50k) (50k) 3100k =zero (ie -3000-50-50+3100) What I want to do is to move the repayment amount (£3100k) to different months, depending on the number of months I input into a particular cell. For example the cell is in a sheet that makes certain assumptions. If I type 5 in this cell, the repayment amount of £3100k should move 5 month from Mar 08 to Aug 08, If I type 3 it should move Jun 08. Just to let you know Mar is the maturity date of this loan.But I need to have the ability to change the number of months I don't know how to factor in an IF statement that could have a range from 1 month to poss 24 months. ie more than 1 If statements. If you do answer could you please explain it in simply terms as my excel skills are average to say the least. Thank you in adance for your help. Best regards Paul |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
converting numbers to months | Excel Discussion (Misc queries) | |||
How do I spread numbers over several months? | Excel Discussion (Misc queries) | |||
How do I spread numbers over several months? | Excel Worksheet Functions | |||
Converting Numbers to Months | Excel Worksheet Functions | |||
Want average sales numbers for last 12 months. | Excel Worksheet Functions |