Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 -- Ruthki ------------------------------------------------------------------------ Ruthki's Profile: http://www.excelforum.com/member.php...o&userid=24503 View this thread: http://www.excelforum.com/showthread...hreadid=383429 |
#2
|
|||
|
|||
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 -- Ruthki ------------------------------------------------------------------------ Ruthki's Profile: http://www.excelforum.com/member.php...o&userid=24503 View this thread: http://www.excelforum.com/showthread...hreadid=383429 |
#3
|
|||
|
|||
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 <atyahoo<dotcom ---- "Ruthki" wrote in message ... 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. |
#4
|
|||
|
|||
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 -- Ruthki ------------------------------------------------------------------------ Ruthki's Profile: http://www.excelforum.com/member.php...o&userid=24503 View this thread: http://www.excelforum.com/showthread...hreadid=383429 |
#5
|
|||
|
|||
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 <atyahoo<dotcom ---- "Ruthki" wrote in message ... 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 |
#6
|
|||
|
|||
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 <atyahoo<dotcom ---- |
#7
|
|||
|
|||
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 a cell but hadn't throught of doing it to see the array. I also managed to get something similar working using a sumproduct (with no arrays) =SUMPRODUCT((A2:C2<=D1)*A4:C4) - with A4-C4 being the range I wanted to multiply it by. Thanks Again Ruth Cambridge UK -- Ruthki ------------------------------------------------------------------------ Ruthki's Profile: http://www.excelforum.com/member.php...o&userid=24503 View this thread: http://www.excelforum.com/showthread...hreadid=383429 |
#8
|
|||
|
|||
You're welcome !
-- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy cell format to cell on another worksht and update automatical | Excel Worksheet Functions | |||
convert value in word. For Exampe Rs.115.00 convert into word as . | Excel Discussion (Misc queries) | |||
I want to return "0" based on another cell | Excel Worksheet Functions | |||
Formula to return cell contents based on multiple conditions | Excel Worksheet Functions | |||
GET.CELL | Excel Worksheet Functions |