Dear all,

I have an issue for index the closest date's data(cash flow). There is
my table below,

A B
C D
1 Valuation Day 5/31/2007 Principal $
Interest $
2
3 Purchase 1/29/2007 (100000)
(3000)
4 Payment 1 2/20/2007 1500
500
5 Payment 2 3/25/2007 1625
526
6 Payment 3 4/21/2007 1738
610
7 Payment 4 5/26/2007 1866
690
8 Payment 5 6/15/2007 1945
750
9 Payment 6 7/23/2007 2053
840


I wish I can index the closest Principal and Interest amount by
changing my valuation day setting. For example, if valuation day is
5/31/2007, than my principal should be 1866(C7) and my interest should
be 690(D7). If I change my valuation day to 3/24/2007, then my
principal should be 1500(C4) and Interest should be 500(D4).

Has any could give me a favor, tell me how to get the formula by this
criteria?

Thanks a lot

Vincent
please email to:

Hi Vincent

I would use a helper column - Column E. In E3
=$B$1-B3
Set cell Format to General, then copy down.
Then use the following array entered formulae
For Capital
{=INDEX($C$3:$C$9,MATCH(MIN(IF($E$3:$E$90,$E$3:$E $9)),$E$3:$E$9,0))}
For Interest
{=INDEX($D$3:$D$9,MATCH(MIN(IF($E$3:$E$90,$E$3:$E $9)),$E$3:$E$9,0))}

Array formulae must be committed and edited using Control, Shift, Enter
(CSE) not just Enter.
Do not type the curly braces { } yourself. When you use CSE, Excel
will insert them for you.

--
Regards

Roger Govier


wrote in message
oups.com...
Dear all,

I have an issue for index the closest date's data(cash flow). There is
my table below,

A B
C D
1 Valuation Day 5/31/2007 Principal $
Interest $
2
3 Purchase 1/29/2007 (100000)
(3000)
4 Payment 1 2/20/2007 1500
500
5 Payment 2 3/25/2007 1625
526
6 Payment 3 4/21/2007 1738
610
7 Payment 4 5/26/2007 1866
690
8 Payment 5 6/15/2007 1945
750
9 Payment 6 7/23/2007 2053
840


I wish I can index the closest Principal and Interest amount by
changing my valuation day setting. For example, if valuation day is
5/31/2007, than my principal should be 1866(C7) and my interest should
be 690(D7). If I change my valuation day to 3/24/2007, then my
principal should be 1500(C4) and Interest should be 500(D4).

Has any could give me a favor, tell me how to get the formula by this
criteria?

Thanks a lot

Vincent
please email to:

On Jun 12, 9:44 am, wrote:
if valuation day is
5/31/2007, than my principal should be 1866(C7) and my interest should
be 690(D7). If I change my valuation day to 3/24/2007, then my
principal should be 1500(C4) and Interest should be 500(D4).

For what you ask for, try:

C1: =index(C3:C9, match(B1, B3:B9))

D1: =index(D3:D9, match(B1, B3:B9))

But I quibble with your terminology. I'm not sure that truly gives
you the results you need. I also do not understand the -3000 figure
in D3; that is, I think it is nonsensical.


----- previous posting -----

On Jun 12, 9:44 am, wrote:
I have an issue for index the closest date's data(cash flow). There is
my table below,

A B
C D
1 Valuation Day 5/31/2007 Principal $
Interest $
2
3 Purchase 1/29/2007 (100000)
(3000)
4 Payment 1 2/20/2007 1500
500
5 Payment 2 3/25/2007 1625
526
6 Payment 3 4/21/2007 1738
610
7 Payment 4 5/26/2007 1866
690
8 Payment 5 6/15/2007 1945
750
9 Payment 6 7/23/2007 2053
840

I wish I can index the closest Principal and Interest amount by
changing my valuation day setting. For example, if valuation day is
5/31/2007, than my principal should be 1866(C7) and my interest should
be 690(D7). If I change my valuation day to 3/24/2007, then my
principal should be 1500(C4) and Interest should be 500(D4).

Has any could give me a favor, tell me how to get the formula by this
criteria?

On Jun 12, 9:44 am, wrote:
if valuation day is
5/31/2007, than my principal should be 1866(C7) and my interest should
be 690(D7). If I change my valuation day to 3/24/2007, then my
principal should be 1500(C4) and Interest should be 500(D4).

For what you ask for, since your events seem to be in order, try:

C1: =index(C3:C9, match(B1, B3:B9))

D1: =index(D3:D9, match(B1, B3:B9))

But I quibble with your terminology. I'm not convinced those formulas
give you what you truly want. (GIGO.) Also, I do not understand the
-3000 in D3. That is, it seems nonsensical.


----- previous posting -----

On Jun 12, 9:44 am, wrote:
Dear all,

I have an issue for index the closest date's data(cash flow). There is
my table below,

A B
C D
1 Valuation Day 5/31/2007 Principal $
Interest $
2
3 Purchase 1/29/2007 (100000)
(3000)
4 Payment 1 2/20/2007 1500
500
5 Payment 2 3/25/2007 1625
526
6 Payment 3 4/21/2007 1738
610
7 Payment 4 5/26/2007 1866
690
8 Payment 5 6/15/2007 1945
750
9 Payment 6 7/23/2007 2053
840

I wish I can index the closest Principal and Interest amount by
changing my valuation day setting. For example, if valuation day is
5/31/2007, than my principal should be 1866(C7) and my interest should
be 690(D7). If I change my valuation day to 3/24/2007, then my
principal should be 1500(C4) and Interest should be 500(D4).

Has any could give me a favor, tell me how to get the formula by this
criteria?

Thanks a lot

Vincent
please email to:

On Jun 12, 1:36 pm, "Roger Govier"
wrote:
Hi Vincent

I would use a helper column - Column E. In E3
=$B$1-B3
Set cell Format to General, then copy down.
Then use the following array entered formulae
For Capital
{=INDEX($C$3:$C$9,MATCH(MIN(IF($E$3:$E$90,$E$3:$E $9)),$E$3:$E$9,0))}
For Interest
{=INDEX($D$3:$D$9,MATCH(MIN(IF($E$3:$E$90,$E$3:$E $9)),$E$3:$E$9,0))}

Array formulae must be committed and edited using Control, Shift, Enter
(CSE) not just Enter.
Do not type the curly braces { } yourself. When you use CSE, Excel
will insert them for you.

--
Regards

Roger Govier

wrote in message

oups.com...

Dear all,

I have an issue forindextheclosestdate'sdata(cash flow). There is
my table below,

A B
C D
1 Valuation Day 5/31/2007 Principal $
Interest $
2
3 Purchase 1/29/2007 (100000)
(3000)
4 Payment 1 2/20/2007 1500
500
5 Payment 2 3/25/2007 1625
526
6 Payment 3 4/21/2007 1738
610
7 Payment 4 5/26/2007 1866
690
8 Payment 5 6/15/2007 1945
750
9 Payment 6 7/23/2007 2053
840

I wish I canindextheclosestPrincipal and Interest amount by
changing my valuation day setting. For example, if valuation day is
5/31/2007, than my principal should be 1866(C7) and my interest should
be 690(D7). If I change my valuation day to 3/24/2007, then my
principal should be 1500(C4) and Interest should be 500(D4).

Has any could give me a favor, tell me how to get the formula by this
criteria?

Thanks a lot

Vincent
please email to:


Thanks guy, you help me a lot. I will always come to see the solution
and share with people in this group.

On Jun 12, 1:51 pm, joeu2004 wrote:
On Jun 12, 9:44 am, wrote:

if valuation day is
5/31/2007, than my principal should be 1866(C7) and my interest should
be 690(D7). If I change my valuation day to 3/24/2007, then my
principal should be 1500(C4) and Interest should be 500(D4).

For what you ask for, since your events seem to be in order, try:

C1: =index(C3:C9, match(B1, B3:B9))

D1: =index(D3:D9, match(B1, B3:B9))

But I quibble with your terminology. I'm not convinced those formulas
give you what you truly want. (GIGO.) Also, I do not understand the
-3000 in D3. That is, it seems nonsensical.

----- previous posting -----

On Jun 12, 9:44 am, wrote:

Dear all,

I have an issue forindextheclosestdate'sdata(cash flow). There is
my table below,

A B
C D
1 Valuation Day 5/31/2007 Principal $
Interest $
2
3 Purchase 1/29/2007 (100000)
(3000)
4 Payment 1 2/20/2007 1500
500
5 Payment 2 3/25/2007 1625
526
6 Payment 3 4/21/2007 1738
610
7 Payment 4 5/26/2007 1866
690
8 Payment 5 6/15/2007 1945
750
9 Payment 6 7/23/2007 2053
840

I wish I canindextheclosestPrincipal and Interest amount by
changing my valuation day setting. For example, if valuation day is
5/31/2007, than my principal should be 1866(C7) and my interest should
be 690(D7). If I change my valuation day to 3/24/2007, then my
principal should be 1500(C4) and Interest should be 500(D4).

Has any could give me a favor, tell me how to get the formula by this
criteria?

Thanks a lot

Vincent
please email to:


Thanks guy, you help me a lot. I will always come to see the solution
and share with people in this group.