return array result in cell based on comparing dates

[Ruthki]

I would like to create an array formula (in order to use the result t
multiply other arrays) to populate a table as below with the answers a
below (eg {0,1,1})


jan05 feb05
mar05 apr05 may05
mar 05 feb05 apr05 {0,0,0} {0,1,0} {1,1,0}
{1,1,1} {1,1,1}
apr 05 feb05 jan05 {0,0,1} {0,1,1} {0,1,1}
{1,1,1} {1,1,1}


The formula should compare the text entries in 3 columns on the righ
and check to see whether the date is less than or equal to the date i
the current column. If true (ie less than or equal to) it needs t
return a 1 in that position else 0.

Can anyone help?

Thank

 

[Ruthki]

When I posted the question some of the formating may have made m
question unclear.

the columns with the array forumla in (eg {0,1,1) should be headed a
the top by the current month. (the headings have slipped over to th
right in the formatting.

Rut

 

[Max]

Not very sure, but here goes one try ..

Assume you have in A1:E1,
the "1st of month" dates formatted as "mmm-yy", viz.:
Jan-05 Feb-05 Mar-05 Apr-05 May-05

And you have *dates* in 3 cols to the right,
i.e. in cols F to H from row2 down, e.g.:

02-Mar-05 12-Apr-05 21-May-05
11-Jan-05 01-Feb-05 31-Mar-05
etc

Put in the formula bar for A2 and array-enter (i.e. press CTRL+SHIFT+ENTER):

=IF($F2:$H2="","",--($F2:$H2<=A$1))

Copy A2 across to E2, fill down as required

 

[Ruthki]

I really need some help with this - so I will try putting it another
way

I have numbers in cells A2,B2 & C2
I want to compare each one of those cells (a2, b2 & c2) to a number in
cell D1, and determine whether it is less than or equal to the value in
D1. and then return a single set of results in an array in a single cell
(eg in cell D2)
The forumla should
return 1 when cell is less than or equal to value in A4
return 0 when cell is not less than or equal to value in A4.

Therefore the resulting array would be something like {1,0,1}
which would mean
A2 is less than or equal to the value in D4
B2 is not
C2 is

Can anyone let me know if this is poss.

Can you also tell me what does - - mean in array forumula - what does
it do.

Thanks

Ruth

 

[Max]

Think what was suggested earlier wasn't that far off. The resulting arrays
will be visible if you highlight the formula in the formula bar and press
F9.

I have numbers in cells A2,B2 & C2
I want to compare each one of those cells (a2, b2 & c2) to a number in
cell D1, and determine whether it is less than or equal to the value in
D1. and then return a single set of results in an array in a single cell
(eg in cell D2)

Suppose A2:C2 contains: 12, 13, 14
and D1 contains: 13

Put in the formula bar in D2, and array-enter:
=IF($A2:$C2="","",--($A2:$C2<=D$1))

Then select the entire formula in the formula bar and press F9
You will see that the formula in D2 evaluates to : ={1,1,0}

... what does - - mean in array forumula - what does it do.

The "--" is what they call a double unary which coerces the TRUE / FALSE
returns in the comparisons evaluated within the parens to numeric 1's / 0's

 

[Max]

Think what was suggested earlier wasn't that far off ..

The interp of your original set-up might have been a bit off earlier, though
<g>. Think it was the flipped way around. If so, then the first response
should have read as:

Assume you have in D1:H1,
the "1st of month" dates formatted as "mmm-yy", viz.:
Jan-05 Feb-05 Mar-05 Apr-05 May-05

And you have *dates* in 3 cols to the left,
i.e. in cols A to C from row2 down, e.g.:

02-Mar-05 12-Apr-05 21-May-05
11-Jan-05 01-Feb-05 31-Mar-05
etc

Put in the formula bar for D2
and array-enter (i.e. press CTRL+SHIFT+ENTER):
=IF($A2:$C2="","",--($A2:$C2<=D$1))

Copy D2 across to H2, fill down as required

 

[Ruthki]

Thanks Max - I've got it working now and have learnt a bit more today.
Thanks for the explanation of the -- and the f2/f9 - I knew this calcd
cell but hadn't throught of doing it to see the array.

I also managed to get something similar working using a sumproduc
(with no arrays) =SUMPRODUCT((A2:C2<=D1)*A4:C4) - with A4-C4 being th
range I wanted to multiply it by.


Thanks Again

Ruth
Cambridge U

 

[Max]

You're welcome !