| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
calculating a pattern
Using the following pattern:
DATE = ANSWER ---------- ------ 12/16/2007 = 1A 12/17/2007 = 2A 12/18/2007 = 3A 12/19/2007 = 1B 12/20/2007 = 2B 12/21/2007 = 3B 12/22/2007 = 1A 12/23/2007 = 2A 12/24/2007 = 3A 12/25/2007 = 1B 12/26/2007 = 2B 12/27/2007 = 3B 12/28/2007 = 1A 12/29/2007 = 2A 12/30/2007 = 3A how can I calculate the "ANSWER" when "DATE" = January 27, 2008? DATE = ANSWER ---------- ------ 1/27/2008 = ? or any other date for that matter? Thank you. Jim O:) |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
calculating a pattern
One thought
Assuming the first 6 lines below are within A2:B7, viz: 12/16/2007 = 1A 12/17/2007 = 2A 12/18/2007 = 3A 12/19/2007 = 1B 12/20/2007 = 2B 12/21/2007 = 3B Select A2:B7, fill down to propagate the series for future dates as far as required. Then just use a simple vlookup on the input date, eg with input date in D2 down: 12/21/2007 Put in E2: =IF(D2="","",VLOOKUP(D2,A:B,2,0)) Copy down -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "jimo" wrote in message ... Using the following pattern: DATE = ANSWER ---------- ------ 12/16/2007 = 1A 12/17/2007 = 2A 12/18/2007 = 3A 12/19/2007 = 1B 12/20/2007 = 2B 12/21/2007 = 3B 12/22/2007 = 1A 12/23/2007 = 2A 12/24/2007 = 3A 12/25/2007 = 1B 12/26/2007 = 2B 12/27/2007 = 3B 12/28/2007 = 1A 12/29/2007 = 2A 12/30/2007 = 3A how can I calculate the "ANSWER" when "DATE" = January 27, 2008? DATE = ANSWER ---------- ------ 1/27/2008 = ? or any other date for that matter? Thank you. Jim O:) |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
calculating a pattern
Enter the date to find in A1, then try this:
=CHOOSE(MOD(A1,6)+1,"1A","2A","3A","1B","2B","3B") -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "jimo" wrote in message ... Using the following pattern: DATE = ANSWER ---------- ------ 12/16/2007 = 1A 12/17/2007 = 2A 12/18/2007 = 3A 12/19/2007 = 1B 12/20/2007 = 2B 12/21/2007 = 3B 12/22/2007 = 1A 12/23/2007 = 2A 12/24/2007 = 3A 12/25/2007 = 1B 12/26/2007 = 2B 12/27/2007 = 3B 12/28/2007 = 1A 12/29/2007 = 2A 12/30/2007 = 3A how can I calculate the "ANSWER" when "DATE" = January 27, 2008? DATE = ANSWER ---------- ------ 1/27/2008 = ? or any other date for that matter? Thank you. Jim O:) |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
calculating a pattern
Looks like you hit it on the head, RagDyer!
This will help create a work shift calculator for firefighters. I'm going to try and understand the "CHOOSE" and "MOD" functions now. On Dec 3, 3:33 pm, "RagDyer" wrote: Enter the date to find in A1, then try this: =CHOOSE(MOD(A1,6)+1,"1A","2A","3A","1B","2B","3B") -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! ---------------------------------------------------------------------------"jimo" wrote in message ... Using the following pattern: DATE = ANSWER ---------- ------ 12/16/2007 = 1A 12/17/2007 = 2A 12/18/2007 = 3A 12/19/2007 = 1B 12/20/2007 = 2B 12/21/2007 = 3B 12/22/2007 = 1A 12/23/2007 = 2A 12/24/2007 = 3A 12/25/2007 = 1B 12/26/2007 = 2B 12/27/2007 = 3B 12/28/2007 = 1A 12/29/2007 = 2A 12/30/2007 = 3A how can I calculate the "ANSWER" when "DATE" = January 27, 2008? DATE = ANSWER ---------- ------ 1/27/2008 = ? or any other date for that matter? Thank you. Jim O:) |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
calculating a pattern
Appreciate the feed-back.
Actually, both the Choose() and Mod() functions are pretty straight forward, as you can find out by looking in the Help files. It's really just the mathematical adjustment to Mod to make it return the number series (1 to 6) that you need to feed the Choose function. -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "jimo" wrote in message ... Looks like you hit it on the head, RagDyer! This will help create a work shift calculator for firefighters. I'm going to try and understand the "CHOOSE" and "MOD" functions now. On Dec 3, 3:33 pm, "RagDyer" wrote: Enter the date to find in A1, then try this: =CHOOSE(MOD(A1,6)+1,"1A","2A","3A","1B","2B","3B") -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! ---------------------------------------------------------------------------"jimo" wrote in message ... Using the following pattern: DATE = ANSWER ---------- ------ 12/16/2007 = 1A 12/17/2007 = 2A 12/18/2007 = 3A 12/19/2007 = 1B 12/20/2007 = 2B 12/21/2007 = 3B 12/22/2007 = 1A 12/23/2007 = 2A 12/24/2007 = 3A 12/25/2007 = 1B 12/26/2007 = 2B 12/27/2007 = 3B 12/28/2007 = 1A 12/29/2007 = 2A 12/30/2007 = 3A how can I calculate the "ANSWER" when "DATE" = January 27, 2008? DATE = ANSWER ---------- ------ 1/27/2008 = ? or any other date for that matter? Thank you. Jim O:) |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Pattern Matching | Excel Discussion (Misc queries) | |||
| Get rid of pattern | Excel Discussion (Misc queries) | |||
| how do i add a pattern to cells | Excel Discussion (Misc queries) | |||
| Calculating recurring date in following month, calculating # days in that period | Excel Worksheet Functions | |||
| weekday pattern | Excel Discussion (Misc queries) | |||
Linear Mode
