arrays problems

Could you post the actuall matrix witd desired result?

Robert Mika said:

Could you post the actuall matrix witd desired result?

Click to expand...

Thanks for replaying !

--------John--------coef-------Inga--------coef------------Alise---------coef------------today()
John---1/1/2012--1.6887104-1/6/2012---1.371252155-----1/3/2012-----1.040553927-----2/14/2012
Alise---2/15/2012 -0.9871740-2/2/2012---0.807606883-----2/4/2012-----0.602202203--------
-------1/25/2012--0.0797611-3/31/2012--0.11607469------3/15/2012----0.575847398


i'm trying to make one formula (in fact i have no choice), that:
1. Looks for the names aside and finds these names in the table.
2. Looks for the date aside and finds the earliest date in each "dates column" of the table that which is still greater then than the date of today.
3. Then finds all coefficients, than stand next to the dates from 2 and correspond to the names from


=INDEX($B$1:$G$4,{3;4},MATCH($A$2:$A$4,$B$1:$G$1,0)+1) provides desirable result, but right now uses manual arguments ({3;4}), which is not acceptable:
0.98717401
0.575847398


{3;4} are rows numbers, can be received from :

=IF(INDEX(B1:G4,ROW(B2:G4),TRANSPOSE(MATCH(A2:A3,B1:G1,0))))>=H2,ROW(INDEX(B1:G4,ROW(B2:G4),(TRANSPOSE(MATCH(A2:A3,B1:G1,0))))))

which produces:FALSE FALSE
-----------------3-----FALSE
---------------FALSE-----4

And i need it as {3;4}. You see the problem?..

it were different questions, but now it's the same discussion

Could be considered as cross post
- http://www.excelforum.com/excel-general/830811-arrays-question.html

Maybe

Array formula in J2
=INDIRECT(TEXT(MIN(IF($B$1:$G$1=$A2,IF(INDEX($B$2:$G$4,0,MATCH($A$2,$B$1:$G$1,0))>$H$2,ROW($B$2:$G$4)*10^4+COLUMN($B$2:$G$4)+1))),"R0000C0000"),0)

confirmed with Ctrl+Shift+Enter

copy down

M.

oops...

The formula above works only if the dates in each column are ordered (ascending)

M.

oops again (sorry)

There is a typo in the formula (wrong absolute reference)

This is ok (if dates are ascending ordered in each column)

J2
=INDIRECT(TEXT(MIN(IF($B$1:$G$1=$A2,IF(INDEX($B$2:$G$4,0,MATCH($A2,$B$1:$G$1,0))>$H$2,ROW($B$2:$G$4)*10^4+COLUMN($B$2:$G$4)+1))),"R0000C0000"),0)

Ctrl+Shift+Enter

copy to J3

M.

This new version doesn't require that the dates are in order.

=INDIRECT(TEXT(MIN(IF($B$1:$G$1=$A2,IF($B$2:$G$4=MIN(IF($B$1:$G$1=$A2,IF($B$2:$G$4>$H$2,$B$2:$G$4))),ROW($B$2:$G$4)*10^4+COLUMN($B$2:$G$4)+1))),"R0000C0000"),0)

Ctrl+Shift+Enter

M.

Marcelo Branco said:

This new version doesn't require that the dates are in order.

=INDIRECT(TEXT(MIN(IF($B$1:$G$1=$A2,IF($B$2:$G$4=MIN(IF($B$1:$G$1=$A2,IF($B$2:$G$4>$H$2,$B$2:$G$4))),ROW($B$2:$G$4)*10^4+COLUMN($B$2:$G$4)+1))),"R0000C0000"),0)

Ctrl+Shift+Enter

M.

Click to expand...

Thank you for your solution, i'm sure now i underestimated indirect function. The problem is that this formula works only if is displayed on the worksheet. I have to get these coefficients as array and use it as array-variable in a regression. This regression will be run 500 times with different names and dates. so i have no option to display the output of our formula. It will be used only as argument inside other functions. Do you see the problem?? But i really appreciate your help.

i showed you a way on the other forum the offset can be used inside any other function,you could make it a named formula to cut down size..unless you mean something else..