Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help needed on date matching and cell reference.
Hi,
I need a formula to return the value of a cell from a nearby column, based on a matching of the months and years within a range of dates to the months and years within a given date. Here is the setup: Cell BA58 contains the given date. Column AP, starting in Cell AP59 and going down to AP2000 contains the array of dates that need to be evaluated to find which date matches the given date in Cell BA58 Column AR, starting in Cell AR59 and going down to AR2000 contains the array of numbers from which the result must be displayed. If a match is found between the given date in Cell BA58 and a date within the array of dates in AP59:2000, then the result should be the cell value taken from the same row from column of numbers AR59:2000. The dates from AP59 and down look like this: 12 Dec 2009 14 Jan 2010 12 Feb 2010 12 Mar 2010 12 Apr 2010 12 May 2010 Etc. The given date in BA58 looks like this: 03/2009 The numbers from AR59 and down look like this: 0 9 8 7 4 3 Etc. This is probably easy when one knows how, but I don't -- so I am stuck. Help appreciated. GBExcel -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/201001/1 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help needed on date matching and cell reference.
Will the date in BA58 appear *only once* (or possibly, not at all) in
AP59:AP2000? -- Biff Microsoft Excel MVP "GBExcel via OfficeKB.com" <u55438@uwe wrote in message news:a273c2bcb0391@uwe... Hi, I need a formula to return the value of a cell from a nearby column, based on a matching of the months and years within a range of dates to the months and years within a given date. Here is the setup: Cell BA58 contains the given date. Column AP, starting in Cell AP59 and going down to AP2000 contains the array of dates that need to be evaluated to find which date matches the given date in Cell BA58 Column AR, starting in Cell AR59 and going down to AR2000 contains the array of numbers from which the result must be displayed. If a match is found between the given date in Cell BA58 and a date within the array of dates in AP59:2000, then the result should be the cell value taken from the same row from column of numbers AR59:2000. The dates from AP59 and down look like this: 12 Dec 2009 14 Jan 2010 12 Feb 2010 12 Mar 2010 12 Apr 2010 12 May 2010 Etc. The given date in BA58 looks like this: 03/2009 The numbers from AR59 and down look like this: 0 9 8 7 4 3 Etc. This is probably easy when one knows how, but I don't -- so I am stuck. Help appreciated. GBExcel -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/201001/1 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help needed on date matching and cell reference.
I'm not sure that I understand your question, but I hope that this helps.
The date in BA58 is derived as follows: =TEXT(TODAY(),"mm/yyyy") It could also be any date such as =TEXT([Any date],"mm/yyyy") I am using BA58 as a control so that I can call data and produce reports based on the date in BA58. Thanking you. GBExcel T. Valko wrote: Will the date in BA58 appear *only once* (or possibly, not at all) in AP59:AP2000? Hi, [quoted text clipped - 46 lines] GBExcel -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/201001/1 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help needed on date matching and cell reference.
Here's what you posted:
---------- Cell BA58 contains the given date. Column AP, starting in Cell AP59 and going down to AP2000 contains the array of dates that need to be evaluated to find which date matches the given date in Cell BA58 ---------- My question to you is: Will the date in cell BA58 appear *only once* in the range AP59:AP2000? The date in BA58 is derived as follows: =TEXT(TODAY(),"mm/yyyy") Let's assume BA58 = 012010 In the range AP59:AP2000, will there be *only one instance* of a date in January 2010? Based on your posted sample data: ---------- 12 Dec 2009 14 Jan 2010 12 Feb 2010 12 Mar 2010 12 Apr 2010 12 May 2010 Etc. ---------- There is *only one* date that meets the condition. However, the amount of sample data posted might not be a true representative sample of your real data. -- Biff Microsoft Excel MVP "GBExcel via OfficeKB.com" <u55438@uwe wrote in message news:a2741157ebca5@uwe... I'm not sure that I understand your question, but I hope that this helps. The date in BA58 is derived as follows: =TEXT(TODAY(),"mm/yyyy") It could also be any date such as =TEXT([Any date],"mm/yyyy") I am using BA58 as a control so that I can call data and produce reports based on the date in BA58. Thanking you. GBExcel T. Valko wrote: Will the date in BA58 appear *only once* (or possibly, not at all) in AP59:AP2000? Hi, [quoted text clipped - 46 lines] GBExcel -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/201001/1 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help needed on date matching and cell reference.
Will the date in cell BA58 appear *only once* in the range AP59:AP2000?
Yes, that is correct. GBExcel -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/201001/1 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help needed on date matching and cell reference.
Try this...
=SUMPRODUCT(--(TEXT(AP59:AP2000,"mmyyyy")=BA58),AR59:AR2000) -- Biff Microsoft Excel MVP "GBExcel via OfficeKB.com" <u55438@uwe wrote in message news:a274c36192884@uwe... Will the date in cell BA58 appear *only once* in the range AP59:AP2000? Yes, that is correct. GBExcel -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/201001/1 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help needed on date matching and cell reference.
Thank you. Will try it.
The double minus (as in "--") is new to me. What does it do? GBExcel T. Valko wrote: Try this... =SUMPRODUCT(--(TEXT(AP59:AP2000,"mmyyyy")=BA58),AR59:AR2000) Will the date in cell BA58 appear *only once* in the range AP59:AP2000? Yes, that is correct. GBExcel -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/201001/1 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help needed on date matching and cell reference.
=SUMPRODUCT(--(TEXT(AP59:AP2000,"mmyyyy")=BA58),AR59:AR2000)
The double minus (as in "--") is new to me. What does it do? The SUMPRODUCT function works with numbers. It multiplies arrays of numbers together then returns the sum total of that multiplication. For example: 1...3 2...4 6...2 =SUMPRODUCT(A1:A3,B1:B3) = 23 (1*3)+(2*4)+(6*2) = 23 In this formula: =SUMPRODUCT(--(TEXT(AP59:AP2000,"mmyyyy")=BA58),AR59:AR2000) We have one array of numbers in the range AR59:AR2000. So, we need to come up with another array of numbers in order for the SUMPRODUCT function to do what it does. This expression will return an array of either TRUE or FALSE: TEXT(AP59:AP2000,"mmyyyy")=BA58 Based on your posted sample data we get: BA58 = 012010 12 Dec 2009 = 122009 = 012010 = FALSE 14 Jan 2010 = 012010 = 012010 = TRUE 12 Feb 2010 = 022010 = 012010 = FALSE 12 Mar 2010 = 032010 = 012010 = FALSE 12 Apr 2010 = 042010 = 012010 = FALSE 12 May 2010 = 052010 = 012010 = FALSE etc etc We need to convert those logicals to numbers. One way to do that is to use the double unary minus --. --TRUE = 1 --FALSE = 0 --(TEXT(AP59:AP2000,"mmyyyy")=BA58) Then we get an array of 1s or 0s: 12 Dec 2009 = 122009 = 012010 = --FALSE = 0 14 Jan 2010 = 012010 = 012010 = --TRUE = 1 12 Feb 2010 = 022010 = 012010 = --FALSE = 0 12 Mar 2010 = 032010 = 012010 = --FALSE = 0 12 Apr 2010 = 042010 = 012010 = --FALSE = 0 12 May 2010 = 052010 = 012010 = --FALSE = 0 etc etc Now we have 2 arrays of numbers, the array of 1s and 0s and the array of numbers in the range AR59:AR2000. Those arrays might look something like this: 0...10 1...14 0...0 0...7 0...22 0...19 etc etc The arrays are multiplied together: 0*10 = 0 1*14 = 14 0*0 = 0 0*7 = 0 0*22 = 0 0*19 = 0 etc etc SUMPRODUCT then sums the results of that multiplication: SUMPRODUCT({0;14;0;0;0;0}) = 14 So, lookup "012010" in the range of dates AP59:AP2000 and return the corresponding numeric value from AR59:AR2000: =SUMPRODUCT(--(TEXT(AP59:AP2000,"mmyyyy")=BA58),AR59:AR2000) =14 For more on SUMPRODUCT see this: http://xldynamic.com/source/xld.SUMPRODUCT.html -- Biff Microsoft Excel MVP "GBExcel via OfficeKB.com" <u55438@uwe wrote in message news:a275058b31ae0@uwe... Thank you. Will try it. The double minus (as in "--") is new to me. What does it do? GBExcel T. Valko wrote: Try this... =SUMPRODUCT(--(TEXT(AP59:AP2000,"mmyyyy")=BA58),AR59:AR2000) Will the date in cell BA58 appear *only once* in the range AP59:AP2000? Yes, that is correct. GBExcel -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/201001/1 |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help needed on date matching and cell reference.
Thank you,
You've gone more than the extra mile. Appreciate it. GBExcel -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/201001/1 |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help needed on date matching and cell reference.
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "GBExcel via OfficeKB.com" <u55438@uwe wrote in message news:a2766573e1fb8@uwe... Thank you, You've gone more than the extra mile. Appreciate it. GBExcel -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/201001/1 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help needed with cell reference | Excel Discussion (Misc queries) | |||
find cell with matching text, then reference cell in same row | Excel Worksheet Functions | |||
vlookup/matching? much help needed! | Excel Discussion (Misc queries) | |||
Finding the cell reference of a matching search value | Excel Worksheet Functions | |||
Look Up and Cell Reference - Formula Help Needed | Excel Worksheet Functions |