If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Next Date given one date
At this time, I cannot use VBA. Here is my objective:
For a given treasury note security: Cell A1, Date 6/17/2004 - today's date Cell A2, Date 7/15/1997 - first coupon date I want to identify the "previous coupon date" and "next coupon date" from today's date based on the frequency of the "first coupon date". Logically speaking: * Security pays 2 times each year * Based on first coupon date, pay dates a 1/15 & 7/15 * Given today is 6/17/04, the next pay date is 7/15/04. * Given today is 6/17/04, the previous pay date is 1/15/04. Can this be accomplished without VBA. Any help is most appreciated. Thank you. |
#2
|
|||
|
|||
Next Date given one date
If you're willing to use the Analysis Toolpak Add-in (Tools/Add-ins...),
you can use COUPNCD: =COUPNCD(TODAY(),DATE(2029,1,15),2) In article , "Katharine" wrote: At this time, I cannot use VBA. Here is my objective: For a given treasury note security: Cell A1, Date 6/17/2004 - today's date Cell A2, Date 7/15/1997 - first coupon date I want to identify the "previous coupon date" and "next coupon date" from today's date based on the frequency of the "first coupon date". Logically speaking: * Security pays 2 times each year * Based on first coupon date, pay dates a 1/15 & 7/15 * Given today is 6/17/04, the next pay date is 7/15/04. * Given today is 6/17/04, the previous pay date is 1/15/04. Can this be accomplished without VBA. Any help is most appreciated. Thank you. |
#3
|
|||
|
|||
Match & Index Combined Row & Multiple extractions
I am looking up a text string in a1 and seeing if I have a match in a row
For example row B EXAMPLE If I have a match in B2-Z25 I use the index command to exract a value from within the row AA2 to AX2. No problem so far! This I can do.However what happens if the same text string turns up in B2,G2,H2,Y2 or in more cells from columns 1-25, how do I pull out using the index function values in the range AA2-AX2 and combine their values(ie get their sum). Maybe there is another way. What I am doing is looking up a fund name in A1. Clients names are in Column A. If a client holds the fund I extract the number of units he holds by using the index command to pull the number of units he holds for that fund. Columns B-Z contain Fund names. Columns AA-AY contain number of units held fror each fund. eg Col B is Fund 1, Colum AA is number of units held in Fund 1. If the fund in a1 turns up more than once for a client the problem is to determine his total number of units he holds for that fund |
#4
|
|||
|
|||
Match & Index Combined Row & Multiple extractions
This file might be a help:
http://www.bygsoftware.com/examples/.../xindexvba.zip It's in the "Excel for Lotus 123 Users" section on page: http://www.bygsoftware.com/examples/examples.htm In Excel there is no direct equivalent for Lotus 123's XINDEX function. This workbook shows two Excel formula constructions that achieve the same result. The first example uses two additional inputs. It uses the Excel functions: INDEX and MATCH The second example uses the same inputs as the Lotus XINDEX function. It uses the Excel functions: INDEX, MATCH and OFFSET. There are also two additional pieces of VBA showing how to use this in code and capture an error condition. The code is open and commented. There's also an alternative to this using SUMPRODUCT at: http://www.bygsoftware.com/Excel/fun...sumproduct.htm (and please don't cross post ;-) -- Regards Andy Wiggins www.BygSoftware.com Home of "Save and BackUp", "The Excel Auditor" and "Byg Tools for VBA" "Ronald Cayne" wrote in message ... I am looking up a text string in a1 and seeing if I have a match in a row For example row B EXAMPLE If I have a match in B2-Z25 I use the index command to exract a value from within the row AA2 to AX2. No problem so far! This I can do.However what happens if the same text string turns up in B2,G2,H2,Y2 or in more cells from columns 1-25, how do I pull out using the index function values in the range AA2-AX2 and combine their values(ie get their sum). Maybe there is another way. What I am doing is looking up a fund name in A1. Clients names are in Column A. If a client holds the fund I extract the number of units he holds by using the index command to pull the number of units he holds for that fund. Columns B-Z contain Fund names. Columns AA-AY contain number of units held fror each fund. eg Col B is Fund 1, Colum AA is number of units held in Fund 1. If the fund in a1 turns up more than once for a client the problem is to determine his total number of units he holds for that fund |
#5
|
|||
|
|||
Match & Index Combined Row & Multiple extractions
I think I don;t quite understand this!.
Clients are in column A. Eg. Products in B2-Z26. Units held in products are held in Columns Eg. AA2-AX2. where AA2 contains the number of units held by under product 1 etc. If Client holds more than 1 of the same product. eg. XXX Fund in B2 and XXX Fund in G2 and XXX Fund in H2. How do I get the total of units held in this fund?? That is the problem . I do not have a problem in establishing the amount of units on the first match of the fund lookup in A1. The problem occurs if the same fund is held by the same client eg Product G2 or G3 which may have been acquired at a different point in time. Clear as mud ! Andy Wiggins wrote: This file might be a help: http://www.bygsoftware.com/examples/.../xindexvba.zip It's in the "Excel for Lotus 123 Users" section on page: http://www.bygsoftware.com/examples/examples.htm In Excel there is no direct equivalent for Lotus 123's XINDEX function. This workbook shows two Excel formula constructions that achieve the same result. The first example uses two additional inputs. It uses the Excel functions: INDEX and MATCH The second example uses the same inputs as the Lotus XINDEX function. It uses the Excel functions: INDEX, MATCH and OFFSET. There are also two additional pieces of VBA showing how to use this in code and capture an error condition. The code is open and commented. There's also an alternative to this using SUMPRODUCT at: http://www.bygsoftware.com/Excel/fun...sumproduct.htm (and please don't cross post ;-) -- Regards Andy Wiggins www.BygSoftware.com Home of "Save and BackUp", "The Excel Auditor" and "Byg Tools for VBA" "Ronald Cayne" wrote in message ... I am looking up a text string in a1 and seeing if I have a match in a row For example row B EXAMPLE If I have a match in B2-Z25 I use the index command to exract a value from within the row AA2 to AX2. No problem so far! This I can do.However what happens if the same text string turns up in B2,G2,H2,Y2 or in more cells from columns 1-25, how do I pull out using the index function values in the range AA2-AX2 and combine their values(ie get their sum). Maybe there is another way. What I am doing is looking up a fund name in A1. Clients names are in Column A. If a client holds the fund I extract the number of units he holds by using the index command to pull the number of units he holds for that fund. Columns B-Z contain Fund names. Columns AA-AY contain number of units held fror each fund. eg Col B is Fund 1, Colum AA is number of units held in Fund 1. If the fund in a1 turns up more than once for a client the problem is to determine his total number of units he holds for that fund |
#6
|
|||
|
|||
Match & Index Combined Row & Multiple extractions
Unless I misunderstand the exercise (a small illustration, say 3 rows
and 9 columns, would have been immensely helpful), with the client name in BA1 and the product name in BB1, then unless I made an error in converting the formula in my small test range to the one needed in your large stated range =SUMIF(INDIRECT("B"&MATCH(BA1,A2:A26,0)&":Z"&MATCH (BA1,A2:A26,0)),BB1,INDIRECT("AA"&MATCH(BA1,A2:A26 ,0)&":AX"&MATCH(BA1,A2:A26,0))) By the way, you stated 25 columns of products and 24 columns of units. Alan Beban Ronald Cayne wrote: I think I don;t quite understand this!. Clients are in column A. Eg. Products in B2-Z26. Units held in products are held in Columns Eg. AA2-AX2. where AA2 contains the number of units held by under product 1 etc. If Client holds more than 1 of the same product. eg. XXX Fund in B2 and XXX Fund in G2 and XXX Fund in H2. How do I get the total of units held in this fund?? That is the problem . I do not have a problem in establishing the amount of units on the first match of the fund lookup in A1. The problem occurs if the same fund is held by the same client eg Product G2 or G3 which may have been acquired at a different point in time. Clear as mud ! Andy Wiggins wrote: This file might be a help: http://www.bygsoftware.com/examples/.../xindexvba.zip It's in the "Excel for Lotus 123 Users" section on page: http://www.bygsoftware.com/examples/examples.htm In Excel there is no direct equivalent for Lotus 123's XINDEX function. This workbook shows two Excel formula constructions that achieve the same result. The first example uses two additional inputs. It uses the Excel functions: INDEX and MATCH The second example uses the same inputs as the Lotus XINDEX function. It uses the Excel functions: INDEX, MATCH and OFFSET. There are also two additional pieces of VBA showing how to use this in code and capture an error condition. The code is open and commented. There's also an alternative to this using SUMPRODUCT at: http://www.bygsoftware.com/Excel/fun...sumproduct.htm (and please don't cross post ;-) -- Regards Andy Wiggins www.BygSoftware.com Home of "Save and BackUp", "The Excel Auditor" and "Byg Tools for VBA" "Ronald Cayne" wrote in message ... I am looking up a text string in a1 and seeing if I have a match in a row For example row B EXAMPLE If I have a match in B2-Z25 I use the index command to exract a value from within the row AA2 to AX2. No problem so far! This I can do.However what happens if the same text string turns up in B2,G2,H2,Y2 or in more cells from columns 1-25, how do I pull out using the index function values in the range AA2-AX2 and combine their values(ie get their sum). Maybe there is another way. What I am doing is looking up a fund name in A1. Clients names are in Column A. If a client holds the fund I extract the number of units he holds by using the index command to pull the number of units he holds for that fund. Columns B-Z contain Fund names. Columns AA-AY contain number of units held fror each fund. eg Col B is Fund 1, Colum AA is number of units held in Fund 1. If the fund in a1 turns up more than once for a client the problem is to determine his total number of units he holds for that fund |
#7
|
|||
|
|||
Match & Index Combined Row & Multiple extractions
"Alan Beban" wrote...
.... =SUMIF(INDIRECT("B"&MATCH(BA1,A2:A26,0)&":Z" &MATCH(BA1,A2:A26,0)),BB1,INDIRECT("AA" &MATCH(BA1,A2:A26,0)&":AX"&MATCH(BA1,A2:A26,0)) ) .... One problem: if BA1 matched the value in cell A2, then MATCH would return 1, making the formula equivalent to =SUMIF(B1:Z1,BB1,AA1:AX1) Looks like an off-by-one index bug. Aside from this, if the formula above does what the OP wants, so would =SUMIF(INDEX(B2:Z26,MATCH(BA1,A2:A26,0),0),BB1, INDEX(AA2:AY26,MATCH(BA1,A2:A26,0),0)) |
#8
|
|||
|
|||
Match & Index Combined Row & Multiple extractions
Example file is at the following location. I had trouble uploading a file here
ftp://guest.crescentauctions.com:gue...uctions. com/ The file name is test_file Many thanks Alan for your help. If you can solve my problem using the indirect command would you kindly explain how it is working for this solutione. Regards Ron Alan Beban wrote: Unless I misunderstand the exercise (a small illustration, say 3 rows and 9 columns, would have been immensely helpful), with the client name in BA1 and the product name in BB1, then unless I made an error in converting the formula in my small test range to the one needed in your large stated range =SUMIF(INDIRECT("B"&MATCH(BA1,A2:A26,0)&":Z"&MATCH (BA1,A2:A26,0)),BB1,INDIRECT("AA"&MATCH(BA1,A2:A26 ,0)&":AX"&MATCH(BA1,A2:A26,0))) By the way, you stated 25 columns of products and 24 columns of units. Alan Beban Ronald Cayne wrote: I think I don;t quite understand this!. Clients are in column A. Eg. Products in B2-Z26. Units held in products are held in Columns Eg. AA2-AX2. where AA2 contains the number of units held by under product 1 etc. If Client holds more than 1 of the same product. eg. XXX Fund in B2 and XXX Fund in G2 and XXX Fund in H2. How do I get the total of units held in this fund?? That is the problem . I do not have a problem in establishing the amount of units on the first match of the fund lookup in A1. The problem occurs if the same fund is held by the same client eg Product G2 or G3 which may have been acquired at a different point in time. Clear as mud ! Andy Wiggins wrote: This file might be a help: http://www.bygsoftware.com/examples/.../xindexvba.zip It's in the "Excel for Lotus 123 Users" section on page: http://www.bygsoftware.com/examples/examples.htm In Excel there is no direct equivalent for Lotus 123's XINDEX function. This workbook shows two Excel formula constructions that achieve the same result. The first example uses two additional inputs. It uses the Excel functions: INDEX and MATCH The second example uses the same inputs as the Lotus XINDEX function. It uses the Excel functions: INDEX, MATCH and OFFSET. There are also two additional pieces of VBA showing how to use this in code and capture an error condition. The code is open and commented. There's also an alternative to this using SUMPRODUCT at: http://www.bygsoftware.com/Excel/fun...sumproduct.htm (and please don't cross post ;-) -- Regards Andy Wiggins www.BygSoftware.com Home of "Save and BackUp", "The Excel Auditor" and "Byg Tools for VBA" "Ronald Cayne" wrote in message ... I am looking up a text string in a1 and seeing if I have a match in a row For example row B EXAMPLE If I have a match in B2-Z25 I use the index command to exract a value from within the row AA2 to AX2. No problem so far! This I can do.However what happens if the same text string turns up in B2,G2,H2,Y2 or in more cells from columns 1-25, how do I pull out using the index function values in the range AA2-AX2 and combine their values(ie get their sum). Maybe there is another way. What I am doing is looking up a fund name in A1. Clients names are in Column A. If a client holds the fund I extract the number of units he holds by using the index command to pull the number of units he holds for that fund. Columns B-Z contain Fund names. Columns AA-AY contain number of units held fror each fund. eg Col B is Fund 1, Colum AA is number of units held in Fund 1. If the fund in a1 turns up more than once for a client the problem is to determine his total number of units he holds for that fund |
#9
|
|||
|
|||
Match & Index Combined Row & Multiple extractions
The ftp address should have been:
ftp://guest.crescentauctions.com:gue...auctions. com Ronald Cayne wrote: Example file is at the following location. I had trouble uploading a file here ftp://guest.crescentauctions.com:gue...uctions. com/ The file name is test_file Many thanks Alan for your help. If you can solve my problem using the indirect command would you kindly explain how it is working for this solutione. Regards Ron Alan Beban wrote: Unless I misunderstand the exercise (a small illustration, say 3 rows and 9 columns, would have been immensely helpful), with the client name in BA1 and the product name in BB1, then unless I made an error in converting the formula in my small test range to the one needed in your large stated range =SUMIF(INDIRECT("B"&MATCH(BA1,A2:A26,0)&":Z"&MATCH (BA1,A2:A26,0)),BB1,INDIRECT("AA"&MATCH(BA1,A2:A26 ,0)&":AX"&MATCH(BA1,A2:A26,0))) By the way, you stated 25 columns of products and 24 columns of units. Alan Beban Ronald Cayne wrote: I think I don;t quite understand this!. Clients are in column A. Eg. Products in B2-Z26. Units held in products are held in Columns Eg. AA2-AX2. where AA2 contains the number of units held by under product 1 etc. If Client holds more than 1 of the same product. eg. XXX Fund in B2 and XXX Fund in G2 and XXX Fund in H2. How do I get the total of units held in this fund?? That is the problem . I do not have a problem in establishing the amount of units on the first match of the fund lookup in A1. The problem occurs if the same fund is held by the same client eg Product G2 or G3 which may have been acquired at a different point in time. Clear as mud ! Andy Wiggins wrote: This file might be a help: http://www.bygsoftware.com/examples/.../xindexvba.zip It's in the "Excel for Lotus 123 Users" section on page: http://www.bygsoftware.com/examples/examples.htm In Excel there is no direct equivalent for Lotus 123's XINDEX function. This workbook shows two Excel formula constructions that achieve the same result. The first example uses two additional inputs. It uses the Excel functions: INDEX and MATCH The second example uses the same inputs as the Lotus XINDEX function. It uses the Excel functions: INDEX, MATCH and OFFSET. There are also two additional pieces of VBA showing how to use this in code and capture an error condition. The code is open and commented. There's also an alternative to this using SUMPRODUCT at: http://www.bygsoftware.com/Excel/fun...sumproduct.htm (and please don't cross post ;-) -- Regards Andy Wiggins www.BygSoftware.com Home of "Save and BackUp", "The Excel Auditor" and "Byg Tools for VBA" "Ronald Cayne" wrote in message ... I am looking up a text string in a1 and seeing if I have a match in a row For example row B EXAMPLE If I have a match in B2-Z25 I use the index command to exract a value from within the row AA2 to AX2. No problem so far! This I can do.However what happens if the same text string turns up in B2,G2,H2,Y2 or in more cells from columns 1-25, how do I pull out using the index function values in the range AA2-AX2 and combine their values(ie get their sum). Maybe there is another way. What I am doing is looking up a fund name in A1. Clients names are in Column A. If a client holds the fund I extract the number of units he holds by using the index command to pull the number of units he holds for that fund. Columns B-Z contain Fund names. Columns AA-AY contain number of units held fror each fund. eg Col B is Fund 1, Colum AA is number of units held in Fund 1. If the fund in a1 turns up more than once for a client the problem is to determine his total number of units he holds for that fund |
#10
|
|||
|
|||
Match & Index Combined Row & Multiple extractions
Please see a sample file at the following ftp site:
ftp://guest.crescentauctions.com:gue...auctions. com I really want to understand the solution to this problem Ron Alan Beban wrote: Unless I misunderstand the exercise (a small illustration, say 3 rows and 9 columns, would have been immensely helpful), with the client name in BA1 and the product name in BB1, then unless I made an error in converting the formula in my small test range to the one needed in your large stated range =SUMIF(INDIRECT("B"&MATCH(BA1,A2:A26,0)&":Z"&MATCH (BA1,A2:A26,0)),BB1,INDIRECT("AA"&MATCH(BA1,A2:A26 ,0)&":AX"&MATCH(BA1,A2:A26,0))) By the way, you stated 25 columns of products and 24 columns of units. Alan Beban Ronald Cayne wrote: I think I don;t quite understand this!. Clients are in column A. Eg. Products in B2-Z26. Units held in products are held in Columns Eg. AA2-AX2. where AA2 contains the number of units held by under product 1 etc. If Client holds more than 1 of the same product. eg. XXX Fund in B2 and XXX Fund in G2 and XXX Fund in H2. How do I get the total of units held in this fund?? That is the problem . I do not have a problem in establishing the amount of units on the first match of the fund lookup in A1. The problem occurs if the same fund is held by the same client eg Product G2 or G3 which may have been acquired at a different point in time. Clear as mud ! Andy Wiggins wrote: This file might be a help: http://www.bygsoftware.com/examples/.../xindexvba.zip It's in the "Excel for Lotus 123 Users" section on page: http://www.bygsoftware.com/examples/examples.htm In Excel there is no direct equivalent for Lotus 123's XINDEX function. This workbook shows two Excel formula constructions that achieve the same result. The first example uses two additional inputs. It uses the Excel functions: INDEX and MATCH The second example uses the same inputs as the Lotus XINDEX function. It uses the Excel functions: INDEX, MATCH and OFFSET. There are also two additional pieces of VBA showing how to use this in code and capture an error condition. The code is open and commented. There's also an alternative to this using SUMPRODUCT at: http://www.bygsoftware.com/Excel/fun...sumproduct.htm (and please don't cross post ;-) -- Regards Andy Wiggins www.BygSoftware.com Home of "Save and BackUp", "The Excel Auditor" and "Byg Tools for VBA" "Ronald Cayne" wrote in message ... I am looking up a text string in a1 and seeing if I have a match in a row For example row B EXAMPLE If I have a match in B2-Z25 I use the index command to exract a value from within the row AA2 to AX2. No problem so far! This I can do.However what happens if the same text string turns up in B2,G2,H2,Y2 or in more cells from columns 1-25, how do I pull out using the index function values in the range AA2-AX2 and combine their values(ie get their sum). Maybe there is another way. What I am doing is looking up a fund name in A1. Clients names are in Column A. If a client holds the fund I extract the number of units he holds by using the index command to pull the number of units he holds for that fund. Columns B-Z contain Fund names. Columns AA-AY contain number of units held fror each fund. eg Col B is Fund 1, Colum AA is number of units held in Fund 1. If the fund in a1 turns up more than once for a client the problem is to determine his total number of units he holds for that fund |
Thread Tools | |
Display Modes | |
|
|