Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Match + Index(?) Question
I have a worksheet as follows:
March 30, 2005 Mar Jan 1 Feb 2 Mar 3 Apr 4 May 5 Jun 6 Jul 7 Aug 8 Sep 9 Oct 10 Nov 11 Dec 12 where A1 is =today() and delivers the current date where B1 = text(A1,"mmm") which extracts "MAR" from the date in another cell, say D1, I have a formula =MATCH(B2,A2:A12,0) to deliver the month "number" to use elsewhere in a formula (an annualization formula where the YTD value is divided by the month number, then multiplied by 12). In this case, it delivers 3. Now the puzzle: I only want the month number to increase, say from 2 to 3, only after the 30th of the month. Example: March 29 delivers "2", and March 30 delivers "3". I tried using text to extract the "dd" but it failed. Any ideas? Kem |
#2
|
|||
|
|||
It looks like you've created the Jan - Dec table in cells A2:A13 to
provide a "lookup" capability that returns the month number. If that's the case, then you've duplicated an existing Excel function: the MONTH() function returns the integer month number of a date. Another function that may help resolve your problem is the DAY() function, which returns the day specified in a date. To solve your problem, could you use an IF that says "if the day is 30 or greater, then return the month number; if not, return the month number minus one". To extend your example, suppose March 30 2005 is in cell A1. The formula in B1 might be: =IF(DAY(A1)=30,MONTH(A1),MONTH(A1)-1) However, I see some problems with this: February has only 28 days and will never return its actual month number, and January 15, 2005 in cell A1 will return the value 0. Is this consistent with your needs, or is the month-end cutoff a certain number of days before the end of the month? Should the Jan 15 entry return the previous month (12) or zero? Either scenario can be accommodated- it's a matter of how you need the results to display. Please let us know, and we can help. |
#3
|
|||
|
|||
Dave,
The formula works. The "30" was always an approximate date since the OLAP update is never an exact date so I think changing that test to 28 works fine. As far as delivering the 0 for January I can solve that with an IF/THEN statement in the annualization formula so I don't get a "multiplied by zero" result. In that case IF 0, use *12. Thanks for the help and the tutorial on DAY MONTH. Fortunately it is far more intuitive than the MATCH/CHOOSE learning curve. Thanks again, Kem "Dave O" wrote: It looks like you've created the Jan - Dec table in cells A2:A13 to provide a "lookup" capability that returns the month number. If that's the case, then you've duplicated an existing Excel function: the MONTH() function returns the integer month number of a date. Another function that may help resolve your problem is the DAY() function, which returns the day specified in a date. To solve your problem, could you use an IF that says "if the day is 30 or greater, then return the month number; if not, return the month number minus one". To extend your example, suppose March 30 2005 is in cell A1. The formula in B1 might be: =IF(DAY(A1)=30,MONTH(A1),MONTH(A1)-1) However, I see some problems with this: February has only 28 days and will never return its actual month number, and January 15, 2005 in cell A1 will return the value 0. Is this consistent with your needs, or is the month-end cutoff a certain number of days before the end of the month? Should the Jan 15 entry return the previous month (12) or zero? Either scenario can be accommodated- it's a matter of how you need the results to display. Please let us know, and we can help. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Match, Index, Vlookup, Large....Help Please | Excel Worksheet Functions | |||
Match, Index, Vlookup, Large....Help Please | Excel Worksheet Functions | |||
Match & Index | Excel Worksheet Functions | |||
Find a match that;s not exact | Excel Worksheet Functions | |||
Vlookup, Index & Match | Excel Worksheet Functions |