Nested IF/AND or Something else??

Anthony Flora

Hi all,

I am working on a formula to determine the Duration of a Build Phase so that I can use it to calculate the Status of a build.

There are several criteria required to determine what Schedule to use. Once I have the Schedule I just need to know the Duration for the Phase it’s in. I’ve built nested IF/AND statements in the past, but I think this will be a pain to maintain due to the number of criteria and options. 

I use an intake sheet to capture the data and this is the current order of the criteria:

(Used to determine the Schedule)

1.    Order Type- (New, Used)

2.    Vehicle Type- (10 options)

3.    Cost- (3 ranges)

4.    Commercial use- (yes/no)

(Used to determine the Duration)

5.    schedule- (10 Options)

6.    Build Phase- (4 options)

7.    Duration- (number of days)

I can then use the duration to determine if the build is “on time”, “risk of delay” or “overdue”.

Thank you all for any help.

 

Best,

Tony

Paul Newcome

Are you able to mock something up with sample data for screenshots and provide some examples?

Anthony Flora

Hi Paul,

Example logic for the Schedule:

1. IF Order Type = New AND Vehicle Type = Ford AND Cost = <$25k AND Commercial Use = No Then Schedule 3
2. IF Order Type = Used AND Vehicle Type = Chevy AND Cost = $25k-$50k AND Commercial Use = Yes, Then Schedule 5

Once I have the Schedule I would like to use Build Phase and the corresponding Duration to calculate the Status.

Each Schedule has a different Duration for each Phase.

Thanks for assistance!

Tony

Paul Newcome

Are you able to outline which schedule is applicable to every variation of options? I know that's going to be a rather long list, but it would go a long way in helping to set something up.

So basically...

New - Ford - <$25k - Yes = 1

New - Ford - <$25k - No = 2

New - Ford - $25k-$50k - Yes = 1

New - Ford - $25k-$50k - No = 3

New - Ford - $50k+ - Yes = 5

New - Ford - $50k+ - No = 7

so on and so forth until every variation is covered?

Paul Newcome

Or at least outline certain patterns that would drive the schedule. So for example, New will always be Schedules 1 - 5 and used is 6 - 10. Then Ford is always 1 - 3. The cost determines 1 - 3, but then the Yes would bump it up 1 additional.

Anthony Flora

I can do that. I have a lookup table I was working on as a possible solution. Is that what you're thinking?

Paul Newcome

Worst case scenario yes. But if there are patterns we may be able to condense the table a little bit.

For example, if everything is exactly the same between two projects except that one is new and the other is used and that means the schedule goes from 1 to 2 or from 5 to 6 (bumps up one), we may be able to work that into the formula to save some effort on building out the table.

Same with Commercial yes vs no. If there is a pattern that can be used then it could potentially make things a little less effort.