Dynamically Turned Off Utilities for New House Construction - Based on Distance & Schedule

Hi:

I am attempting to solve a problem in the attached excel example file to dynamically turned off a water schedule to Old Houses based on when New Houses are being built. However I only want to turn off the set water schedule if the Old House is ALSO within "X" of less miles from the New House being built.

I have made an attempt at the formula, but I keep getting the wrong result. I have created a "Hard Coded" example of intended outputs (yellow boxes). The formulas in the "yellow boxes" are not meant to be dynamic and do not consider all variables, they simple get the intended answer and I try to show how to get to the answer. My dataset will end up being 300 Old Houses by 300 New Houses, but I have only included 3 Old Houses and 3 New Houses in this simplified example.

Just to recap: I am trying to make a dynamic formula which gives the "New Water Schedule" based on shutting in each "Old House" water line schedule but only during the times of the New House construction AND only if the Old House is within "X" (Cell C2) or less miles away from any New House under construction.

I have given the following as inputs (blue font cells):

1. Matrix of each Old House to each New House distance in miles

2. Time-frame of New House Construction ((1=New House is under construction (Shut off Old House waterlines during this timeframe and if less than or equal to "X" (Cell C2) miles from ANY New House under construction), 0=No construction planned during this time (Keep normal Water Schedule))

3. Normal Planned Water Schedule to Old Houses in Gallons (without any shut-ins due to construction)

My desired Outputs:

Updated Water Schedule which "shuts-off" when New Houses are under construction AND the Old House is "X" (Cell C2) or Less Miles away from the New House being built

I appreciate the time to look into this for me and let me know if I can clarify anything.

Hi

Try this array formula in C35:H37
You must select C35:H37, type the formula (paste the copied formula) and use Ctrl+Shift+Enter

Formula:

Please Login or Register  to view this content.

See the file

Thank you Jose, this is very helpful and works perfectly. Very much appreciated