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

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

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