Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to use a very big formula
I have to use a formula for due date in which there are constraints such as,
If item is of type I or type II, then, {If an item arrives between 11:30 AM - 4:30 PM, it moves exactly after 4 hrs from arrival. If it arrives between 4:30 PM - 8:30 PM, it moves out the next day, 11:30 AM. If it arrives after 8:30 PM, next day 4:30 PM. If it arrives before 11:30 AM, 4:30 PM. If it arrives on a Saturday, Sunday or on Friday after 8:30 PM, it leaves at 4:30 PM on Monday. If it arrives on Friday after 4:30 PM, leaves on Monday - 11:30 AM.} If item is of type III, then, {If an item arrives between 11:30 AM - 8:30 PM, it moves out exactly after 24 Hrs. If it arrives after 8:30 PM, 11:30 AM the next alternate working day.E.G., if at 9:30 PM on Tuesday, 11:30 AM on Thursday. If it arrives before 11:30 AM, 11:30 AM next day. If it arrives on a Saturday or Sunday or after 8:30 PM on Friday, 24 hrs calculated from 11:30 AM Monday, i.e., 11:30 AM on Tuesday.} If item is of type IV, {If an item arrives between 11:30 AM - 8:30 PM, it moves out exactly after 48 Hrs. If it arrives after 8:30 PM, 11:30 AM after 2 working day.E.G., if at 9:30 PM on Tuesday, 11:30 AM on Friday. If it arrives before 11:30 AM, 11:30 AM next alternate day. If it arrives on a Saturday or Sunday or after 8:30 PM on Friday, 48 hrs calculated from 11:30 AM Monday, i.e., 11:30 AM on Wednesday.} If item is of type V, {If an item arrrives between 11:30 AM - 8:30 pm, it moves after 6 working days, i.e., 144 hrs. If it arrives after 8:30 PM or before 11:30 AM, 144 hours calculated from 11:30 AM the immediate working day. In case of Saturday, Sunday & Friday after 8:30 PM, 144 hrs from 11:30 AM Monday} If item is of type VI, {If an item arrives between 11:30 AM & 8:00 PM, it moves out exactly after 1/2 an hour from arrival. If it arrives between 8:00 PM - 8:30 PM, moves at 11:30 AM next day. If it arrives after 8:30 PM, 12:00 PM next day If it arrives before 11:30 AM, 12:00 PM If it arrives on a Saturday or on Sunday, 12:00 PM on Monday} If there are any holidays, it should be incorporated. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to use a very big formula
Looks like a lot of IF functions. You might want to nest them, e.g.
If(A3="type I",If(...)) but you can only go so far - I think seven levels is the limit. Alternatively (or in combination), with so many conditions you may be best constructing some tables and using VLOOKUP(). "Revathi" wrote: I have to use a formula for due date in which there are constraints such as, If item is of type I or type II, then, {If an item arrives between 11:30 AM - 4:30 PM, it moves exactly after 4 hrs from arrival. If it arrives between 4:30 PM - 8:30 PM, it moves out the next day, 11:30 AM. If it arrives after 8:30 PM, next day 4:30 PM. If it arrives before 11:30 AM, 4:30 PM. If it arrives on a Saturday, Sunday or on Friday after 8:30 PM, it leaves at 4:30 PM on Monday. If it arrives on Friday after 4:30 PM, leaves on Monday - 11:30 AM.} If item is of type III, then, {If an item arrives between 11:30 AM - 8:30 PM, it moves out exactly after 24 Hrs. If it arrives after 8:30 PM, 11:30 AM the next alternate working day.E.G., if at 9:30 PM on Tuesday, 11:30 AM on Thursday. If it arrives before 11:30 AM, 11:30 AM next day. If it arrives on a Saturday or Sunday or after 8:30 PM on Friday, 24 hrs calculated from 11:30 AM Monday, i.e., 11:30 AM on Tuesday.} If item is of type IV, {If an item arrives between 11:30 AM - 8:30 PM, it moves out exactly after 48 Hrs. If it arrives after 8:30 PM, 11:30 AM after 2 working day.E.G., if at 9:30 PM on Tuesday, 11:30 AM on Friday. If it arrives before 11:30 AM, 11:30 AM next alternate day. If it arrives on a Saturday or Sunday or after 8:30 PM on Friday, 48 hrs calculated from 11:30 AM Monday, i.e., 11:30 AM on Wednesday.} If item is of type V, {If an item arrrives between 11:30 AM - 8:30 pm, it moves after 6 working days, i.e., 144 hrs. If it arrives after 8:30 PM or before 11:30 AM, 144 hours calculated from 11:30 AM the immediate working day. In case of Saturday, Sunday & Friday after 8:30 PM, 144 hrs from 11:30 AM Monday} If item is of type VI, {If an item arrives between 11:30 AM & 8:00 PM, it moves out exactly after 1/2 an hour from arrival. If it arrives between 8:00 PM - 8:30 PM, moves at 11:30 AM next day. If it arrives after 8:30 PM, 12:00 PM next day If it arrives before 11:30 AM, 12:00 PM If it arrives on a Saturday or on Sunday, 12:00 PM on Monday} If there are any holidays, it should be incorporated. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Dynamic Range with unused formula messing up x axis on dynamic graph | Charts and Charting in Excel | |||
2 Nesting questions | Excel Worksheet Functions | |||
Formula Problem - interrupted by #VALUE! in other cells!? | Excel Worksheet Functions | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
Match / Vlookup within an Array formula | Excel Discussion (Misc queries) |