return array result in cell based on comparing dates

I would like to create an array formula (in order to use the result to multiply other arrays) to populate a table as below with the answers as 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 right and check to see whether the date is less than or equal to the date in the current column. If true (ie less than or equal to) it needs to return a 1 in that position else 0.

Can anyone help?

Thanks

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

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

Ruth

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

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"Ruthki" <Ruthki.1rfb6d_1120107926.9097@excelforum-nospam.com> wrote in
message news:Ruthki.1rfb6d_1120107926.9097@excelforum-nospam.com...
>
> When I posted the question some of the formating may have made my
> question unclear.
>
> the columns with the array forumla in (eg {0,1,1) should be headed at
> the top by the current month. (the headings have slipped over to the
> right in the formatting.
---
> I would like to create an array formula (in order to use the result to
> multiply other arrays) to populate a table as below with the answers as
> 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 right
> and check to see whether the date is less than or equal to the date in
> the current column. If true (ie less than or equal to) it needs to
> return a 1 in that position else 0.

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

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
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"Ruthki" <Ruthki.1rfrug_1120129557.4345@excelforum-nospam.com> wrote in
message news:Ruthki.1rfrug_1120129557.4345@excelforum-nospam.com...
>
> I really need some help with this - so I will try putting it another
> way
>
> 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
>
>
> --
> Ruthki
> ------------------------------------------------------------------------
> Ruthki's Profile:
http://www.excelforum.com/member.php...o&userid=24503
> View this thread: http://www.excelforum.com/showthread...hreadid=383429
>

> 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
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

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
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"Ruthki" <Ruthki.1rfrug_1120129557.4345@excelforum-nospam.com> wrote in
message news:Ruthki.1rfrug_1120129557.4345@excelforum-nospam.com...
>
> I really need some help with this - so I will try putting it another
> way
>
> 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
>
>
> --
> Ruthki
> ------------------------------------------------------------------------
> Ruthki's Profile:
http://www.excelforum.com/member.php...o&userid=24503
> View this thread: http://www.excelforum.com/showthread...hreadid=383429
>

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
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"Ruthki" <Ruthki.1rfrug_1120129557.4345@excelforum-nospam.com> wrote in
message news:Ruthki.1rfrug_1120129557.4345@excelforum-nospam.com...
>
> I really need some help with this - so I will try putting it another
> way
>
> 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
>
>
> --
> Ruthki
> ------------------------------------------------------------------------
> Ruthki's Profile:
http://www.excelforum.com/member.php...o&userid=24503
> View this thread: http://www.excelforum.com/showthread...hreadid=383429
>

> 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
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

> 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
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

> 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
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

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
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"Ruthki" <Ruthki.1rfrug_1120129557.4345@excelforum-nospam.com> wrote in
message news:Ruthki.1rfrug_1120129557.4345@excelforum-nospam.com...
>
> I really need some help with this - so I will try putting it another
> way
>
> 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
>
>
> --
> Ruthki
> ------------------------------------------------------------------------
> Ruthki's Profile:
http://www.excelforum.com/member.php...o&userid=24503
> View this thread: http://www.excelforum.com/showthread...hreadid=383429
>

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
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"Ruthki" <Ruthki.1rfrug_1120129557.4345@excelforum-nospam.com> wrote in
message news:Ruthki.1rfrug_1120129557.4345@excelforum-nospam.com...
>
> I really need some help with this - so I will try putting it another
> way
>
> 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
>
>
> --
> Ruthki
> ------------------------------------------------------------------------
> Ruthki's Profile:
http://www.excelforum.com/member.php...o&userid=24503
> View this thread: http://www.excelforum.com/showthread...hreadid=383429
>

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
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"Ruthki" <Ruthki.1rfrug_1120129557.4345@excelforum-nospam.com> wrote in
message news:Ruthki.1rfrug_1120129557.4345@excelforum-nospam.com...
>
> I really need some help with this - so I will try putting it another
> way
>
> 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
>
>
> --
> Ruthki
> ------------------------------------------------------------------------
> Ruthki's Profile:
http://www.excelforum.com/member.php...o&userid=24503
> View this thread: http://www.excelforum.com/showthread...hreadid=383429
>

> 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
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

> 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
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

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
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"Ruthki" <Ruthki.1rfrug_1120129557.4345@excelforum-nospam.com> wrote in
message news:Ruthki.1rfrug_1120129557.4345@excelforum-nospam.com...
>
> I really need some help with this - so I will try putting it another
> way
>
> 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
>
>
> --
> Ruthki
> ------------------------------------------------------------------------
> Ruthki's Profile:
http://www.excelforum.com/member.php...o&userid=24503
> View this thread: http://www.excelforum.com/showthread...hreadid=383429
>

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
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"Ruthki" <Ruthki.1rfrug_1120129557.4345@excelforum-nospam.com> wrote in
message news:Ruthki.1rfrug_1120129557.4345@excelforum-nospam.com...
>
> I really need some help with this - so I will try putting it another
> way
>
> 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
>
>
> --
> Ruthki
> ------------------------------------------------------------------------
> Ruthki's Profile:
http://www.excelforum.com/member.php...o&userid=24503
> View this thread: http://www.excelforum.com/showthread...hreadid=383429
>

> 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
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

> 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
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

> 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
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

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
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"Ruthki" <Ruthki.1rfrug_1120129557.4345@excelforum-nospam.com> wrote in
message news:Ruthki.1rfrug_1120129557.4345@excelforum-nospam.com...
>
> I really need some help with this - so I will try putting it another
> way
>
> 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
>
>
> --
> Ruthki
> ------------------------------------------------------------------------
> Ruthki's Profile:
http://www.excelforum.com/member.php...o&userid=24503
> View this thread: http://www.excelforum.com/showthread...hreadid=383429
>

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
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"Ruthki" <Ruthki.1rfrug_1120129557.4345@excelforum-nospam.com> wrote in
message news:Ruthki.1rfrug_1120129557.4345@excelforum-nospam.com...
>
> I really need some help with this - so I will try putting it another
> way
>
> 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
>
>
> --
> Ruthki
> ------------------------------------------------------------------------
> Ruthki's Profile:
http://www.excelforum.com/member.php...o&userid=24503
> View this thread: http://www.excelforum.com/showthread...hreadid=383429
>

> 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
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

> 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
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

> 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
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

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
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"Ruthki" <Ruthki.1rfrug_1120129557.4345@excelforum-nospam.com> wrote in
message news:Ruthki.1rfrug_1120129557.4345@excelforum-nospam.com...
>
> I really need some help with this - so I will try putting it another
> way
>
> 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
>
>
> --
> Ruthki
> ------------------------------------------------------------------------
> Ruthki's Profile:
http://www.excelforum.com/member.php...o&userid=24503
> View this thread: http://www.excelforum.com/showthread...hreadid=383429
>

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
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"Ruthki" <Ruthki.1rfrug_1120129557.4345@excelforum-nospam.com> wrote in
message news:Ruthki.1rfrug_1120129557.4345@excelforum-nospam.com...
>
> I really need some help with this - so I will try putting it another
> way
>
> 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
>
>
> --
> Ruthki
> ------------------------------------------------------------------------
> Ruthki's Profile:
http://www.excelforum.com/member.php...o&userid=24503
> View this thread: http://www.excelforum.com/showthread...hreadid=383429
>

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

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"Ruthki" <Ruthki.1rfb6d_1120107926.9097@excelforum-nospam.com> wrote in
message news:Ruthki.1rfb6d_1120107926.9097@excelforum-nospam.com...
>
> When I posted the question some of the formating may have made my
> question unclear.
>
> the columns with the array forumla in (eg {0,1,1) should be headed at
> the top by the current month. (the headings have slipped over to the
> right in the formatting.
---
> I would like to create an array formula (in order to use the result to
> multiply other arrays) to populate a table as below with the answers as
> 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 right
> and check to see whether the date is less than or equal to the date in
> the current column. If true (ie less than or equal to) it needs to
> return a 1 in that position else 0.

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

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"Ruthki" <Ruthki.1rfb6d_1120107926.9097@excelforum-nospam.com> wrote in
message news:Ruthki.1rfb6d_1120107926.9097@excelforum-nospam.com...
>
> When I posted the question some of the formating may have made my
> question unclear.
>
> the columns with the array forumla in (eg {0,1,1) should be headed at
> the top by the current month. (the headings have slipped over to the
> right in the formatting.
---
> I would like to create an array formula (in order to use the result to
> multiply other arrays) to populate a table as below with the answers as
> 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 right
> and check to see whether the date is less than or equal to the date in
> the current column. If true (ie less than or equal to) it needs to
> return a 1 in that position else 0.

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

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"Ruthki" <Ruthki.1rfb6d_1120107926.9097@excelforum-nospam.com> wrote in
message news:Ruthki.1rfb6d_1120107926.9097@excelforum-nospam.com...
>
> When I posted the question some of the formating may have made my
> question unclear.
>
> the columns with the array forumla in (eg {0,1,1) should be headed at
> the top by the current month. (the headings have slipped over to the
> right in the formatting.
---
> I would like to create an array formula (in order to use the result to
> multiply other arrays) to populate a table as below with the answers as
> 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 right
> and check to see whether the date is less than or equal to the date in
> the current column. If true (ie less than or equal to) it needs to
> return a 1 in that position else 0.

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

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"Ruthki" <Ruthki.1rfb6d_1120107926.9097@excelforum-nospam.com> wrote in
message news:Ruthki.1rfb6d_1120107926.9097@excelforum-nospam.com...
>
> When I posted the question some of the formating may have made my
> question unclear.
>
> the columns with the array forumla in (eg {0,1,1) should be headed at
> the top by the current month. (the headings have slipped over to the
> right in the formatting.
---
> I would like to create an array formula (in order to use the result to
> multiply other arrays) to populate a table as below with the answers as
> 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 right
> and check to see whether the date is less than or equal to the date in
> the current column. If true (ie less than or equal to) it needs to
> return a 1 in that position else 0.

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

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"Ruthki" <Ruthki.1rfb6d_1120107926.9097@excelforum-nospam.com> wrote in
message news:Ruthki.1rfb6d_1120107926.9097@excelforum-nospam.com...
>
> When I posted the question some of the formating may have made my
> question unclear.
>
> the columns with the array forumla in (eg {0,1,1) should be headed at
> the top by the current month. (the headings have slipped over to the
> right in the formatting.
---
> I would like to create an array formula (in order to use the result to
> multiply other arrays) to populate a table as below with the answers as
> 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 right
> and check to see whether the date is less than or equal to the date in
> the current column. If true (ie less than or equal to) it needs to
> return a 1 in that position else 0.

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

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"Ruthki" <Ruthki.1rfb6d_1120107926.9097@excelforum-nospam.com> wrote in
message news:Ruthki.1rfb6d_1120107926.9097@excelforum-nospam.com...
>
> When I posted the question some of the formating may have made my
> question unclear.
>
> the columns with the array forumla in (eg {0,1,1) should be headed at
> the top by the current month. (the headings have slipped over to the
> right in the formatting.
---
> I would like to create an array formula (in order to use the result to
> multiply other arrays) to populate a table as below with the answers as
> 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 right
> and check to see whether the date is less than or equal to the date in
> the current column. If true (ie less than or equal to) it needs to
> return a 1 in that position else 0.

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

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"Ruthki" <Ruthki.1rfb6d_1120107926.9097@excelforum-nospam.com> wrote in
message news:Ruthki.1rfb6d_1120107926.9097@excelforum-nospam.com...
>
> When I posted the question some of the formating may have made my
> question unclear.
>
> the columns with the array forumla in (eg {0,1,1) should be headed at
> the top by the current month. (the headings have slipped over to the
> right in the formatting.
---
> I would like to create an array formula (in order to use the result to
> multiply other arrays) to populate a table as below with the answers as
> 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 right
> and check to see whether the date is less than or equal to the date in
> the current column. If true (ie less than or equal to) it needs to
> return a 1 in that position else 0.

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

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"Ruthki" <Ruthki.1rfb6d_1120107926.9097@excelforum-nospam.com> wrote in
message news:Ruthki.1rfb6d_1120107926.9097@excelforum-nospam.com...
>
> When I posted the question some of the formating may have made my
> question unclear.
>
> the columns with the array forumla in (eg {0,1,1) should be headed at
> the top by the current month. (the headings have slipped over to the
> right in the formatting.
---
> I would like to create an array formula (in order to use the result to
> multiply other arrays) to populate a table as below with the answers as
> 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 right
> and check to see whether the date is less than or equal to the date in
> the current column. If true (ie less than or equal to) it needs to
> return a 1 in that position else 0.

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

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"Ruthki" <Ruthki.1rfb6d_1120107926.9097@excelforum-nospam.com> wrote in
message news:Ruthki.1rfb6d_1120107926.9097@excelforum-nospam.com...
>
> When I posted the question some of the formating may have made my
> question unclear.
>
> the columns with the array forumla in (eg {0,1,1) should be headed at
> the top by the current month. (the headings have slipped over to the
> right in the formatting.
---
> I would like to create an array formula (in order to use the result to
> multiply other arrays) to populate a table as below with the answers as
> 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 right
> and check to see whether the date is less than or equal to the date in
> the current column. If true (ie less than or equal to) it needs to
> return a 1 in that position else 0.

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

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"Ruthki" <Ruthki.1rfb6d_1120107926.9097@excelforum-nospam.com> wrote in
message news:Ruthki.1rfb6d_1120107926.9097@excelforum-nospam.com...
>
> When I posted the question some of the formating may have made my
> question unclear.
>
> the columns with the array forumla in (eg {0,1,1) should be headed at
> the top by the current month. (the headings have slipped over to the
> right in the formatting.
---
> I would like to create an array formula (in order to use the result to
> multiply other arrays) to populate a table as below with the answers as
> 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 right
> and check to see whether the date is less than or equal to the date in
> the current column. If true (ie less than or equal to) it needs to
> return a 1 in that position else 0.

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

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"Ruthki" <Ruthki.1rfb6d_1120107926.9097@excelforum-nospam.com> wrote in
message news:Ruthki.1rfb6d_1120107926.9097@excelforum-nospam.com...
>
> When I posted the question some of the formating may have made my
> question unclear.
>
> the columns with the array forumla in (eg {0,1,1) should be headed at
> the top by the current month. (the headings have slipped over to the
> right in the formatting.
---
> I would like to create an array formula (in order to use the result to
> multiply other arrays) to populate a table as below with the answers as
> 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 right
> and check to see whether the date is less than or equal to the date in
> the current column. If true (ie less than or equal to) it needs to
> return a 1 in that position else 0.