Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Bucketing data based on DATE Range criteria
Hi all. Here's the situation... I have a list of numbers that I need to place
in one of four columns based on on the expiration date of the numbers. The date ranges a I. WIthin 1 Year II. 1 to 3 years III. 3 - 5 Years IV. Over 5 Years So basically if the exp. date for the number is within 1 year, it should be in Category A, if its between 1-3 years then category B and so on.. The numbers are in col. A and exp. dates are in col. B. The buckets (I,II,III,IV) are in col. C thru E. I have already calculated what the actual dates should be for buckets I - IV. I had to make that the formula since the date ranges are calculated based on the current day (as of when the analysis is done). Would appreciate any suggestions. Thanks in advance. sk |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Bucketing data based on DATE Range criteria
Are you saying that you have a column of numbers, and an adjacent column with their associated expiration dates that could range from 0 to five years from the current date? ______________________________ Assume: A1: NNNNNN B1: Expiration Date C1: "I" class numbers D1: "II" class numbers E1: "III" class numbers, & F1: "IV" class numbers _________________________________ Insert equations such that: G:G =IF(INT(YEAR(B1)-YEAR(Now()))=0,A1,"") H:H =IF(AND(INT(YEAR(B1)-YEAR(Now()))0,INT(YEAR(B1)-YEAR(Now()))<3),A1,"") I:I =IF(AND(INT(YEAR(B1)-YEAR(Now()))2,INT(YEAR(B1)-YEAR(Now()))<6),A1,"") J:J =IF(INT(YEAR(b1)-YEAR($B3))5,A1,"") ____________________________________ Ben -- protonLeah ------------------------------------------------------------------------ protonLeah's Profile: http://www.excelforum.com/member.php...o&userid=32097 View this thread: http://www.excelforum.com/showthread...hreadid=543843 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Limiting the range of a lookup function | Excel Worksheet Functions | |||
Matching data using date criteria... | Excel Worksheet Functions | |||
MIN within range based on criteria | Excel Discussion (Misc queries) | |||
Update cell based on date range | Excel Discussion (Misc queries) | |||
How do I count data in range A:A that is dependent upon criteria . | Excel Worksheet Functions |