If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Number of days
I connection with some (simple) interest calculation (where interest rate is
fixed every 1st July and 1st January and where interest calculation is based on 360/360) I need to know how many days at which interest rate. Let's say I have an amount which was due on 1st September 2008. Let's further say that in A1:A5 I have: A1: 1-Jul-2008 A2: 1-Jan-2009 A3: 1-Jul-2009 A4: 1-Jan-2010 A5: =Today() E2: First day of the interest period I have the following formula in B1: =IF(A1=$E$2;DAYS360($E$2;A1;1);0) (I use semicolon as separator) Copied down to B5 this gives me: B1: 0 B2: 120 B3: 300 B4: 480 B5: 520 These numbers are the accumulated days, but what I need is an array: 0, 120, 180, 180, 40. The 120 because (according to 360/360) there are 120 days from the date in E2 to the end of the first half-year, that is from 1-Sep 2008 to 1-Jan-2009, 180 because (acc. to 360/360) there are 180 days from 1-Jan-2009 to 1-Jul-2009, and so on. In other words, how can I change the formulas i B1:B5 to a single formula which returns an array identical to the one I get by the array formula =B2:B5-B1:B4 Hans Knudsen |
#2
|
|||
|
|||
Number of days
This will create an array that you can include in another function, but
array enter it (Ctrl-Shift-Enter) IF(A1:A5=$E$2;DAYS360($E$2;A1:A5;1);0) HTH Bob "HK" wrote in message ... I connection with some (simple) interest calculation (where interest rate is fixed every 1st July and 1st January and where interest calculation is based on 360/360) I need to know how many days at which interest rate. Let's say I have an amount which was due on 1st September 2008. Let's further say that in A1:A5 I have: A1: 1-Jul-2008 A2: 1-Jan-2009 A3: 1-Jul-2009 A4: 1-Jan-2010 A5: =Today() E2: First day of the interest period I have the following formula in B1: =IF(A1=$E$2;DAYS360($E$2;A1;1);0) (I use semicolon as separator) Copied down to B5 this gives me: B1: 0 B2: 120 B3: 300 B4: 480 B5: 520 These numbers are the accumulated days, but what I need is an array: 0, 120, 180, 180, 40. The 120 because (according to 360/360) there are 120 days from the date in E2 to the end of the first half-year, that is from 1-Sep 2008 to 1-Jan-2009, 180 because (acc. to 360/360) there are 180 days from 1-Jan-2009 to 1-Jul-2009, and so on. In other words, how can I change the formulas i B1:B5 to a single formula which returns an array identical to the one I get by the array formula =B2:B5-B1:B4 Hans Knudsen |
#3
|
|||
|
|||
Number of days
To Bob Phillips
English is my second language so please bear with me if I haven't expressed myself clearly. Your array formula is not exactly what I wanted, so I will try to explain again. If I have: A B 1 01-Jul-08 =IF(A1=$E$2;DAYS360($E$2;A1;1);0) 2 01-Jan-09 =IF(A2=$E$2;DAYS360($E$2;A2;1);0) 3 01-Jul-09 =IF(A3=$E$2;DAYS360($E$2;A3;1);0) 4 01-Jan-10 =IF(A4=$E$2;DAYS360($E$2;A4;1);0) 5 =TODAY() =IF(A5=$E$2;DAYS360($E$2;A5;1);0) then B1:B5 shows: B1: 0 B2: 120 B3: 300 B4: 480 B5: 520 which is a range with exactly the same results as your array formula, {0;120;300;480;520}, that is the accumulated days. What I want is an array {0; 120;180;180;40} (days in each half-year period) I can almost (apart from the first element) get this by the array formula: = B2:B5-B1:B4. It returns the array {120;180;180;40}. Hans Knudsen "Bob Phillips" skrev i meddelelsen ... This will create an array that you can include in another function, but array enter it (Ctrl-Shift-Enter) IF(A1:A5=$E$2;DAYS360($E$2;A1:A5;1);0) HTH Bob "HK" wrote in message ... I connection with some (simple) interest calculation (where interest rate is fixed every 1st July and 1st January and where interest calculation is based on 360/360) I need to know how many days at which interest rate. Let's say I have an amount which was due on 1st September 2008. Let's further say that in A1:A5 I have: A1: 1-Jul-2008 A2: 1-Jan-2009 A3: 1-Jul-2009 A4: 1-Jan-2010 A5: =Today() E2: First day of the interest period I have the following formula in B1: =IF(A1=$E$2;DAYS360($E$2;A1;1);0) (I use semicolon as separator) Copied down to B5 this gives me: B1: 0 B2: 120 B3: 300 B4: 480 B5: 520 These numbers are the accumulated days, but what I need is an array: 0, 120, 180, 180, 40. The 120 because (according to 360/360) there are 120 days from the date in E2 to the end of the first half-year, that is from 1-Sep 2008 to 1-Jan-2009, 180 because (acc. to 360/360) there are 180 days from 1-Jan-2009 to 1-Jul-2009, and so on. In other words, how can I change the formulas i B1:B5 to a single formula which returns an array identical to the one I get by the array formula =B2:B5-B1:B4 Hans Knudsen |
Thread Tools | |
Display Modes | |
|
|