Jul 08 2021 04:34 PM
Jul 08 2021 04:34 PM
Ok here is the deal. I need excel to do some simple math using complex logic.
I am currently working on a transportation database. I need to track 2 separate metrics here.
1. Passenger Mileage
2. Passenger Time
These metrics need to be tracked as "Shared".
I.E. If a passenger is picked up at 9AM and dropped off at 10AM, any mileage and time spent with this passenger is noted to that particular ride. However any passengers that are picked up or dropped off within the timeframe of another or within the mileage window of another is not counted.
Now, if we were to pick up a passenger within the mileage and time window, but drop them off outside of that window, I would need to quantify the difference in mileage and time.
Here is the complex logic that I need excel to use...
I have the mileage formula figured out.
=IF(E5>MAX($F$3:F4), SUM(F5-E5), IF(F5>MAX($F$3:F4), SUM(F5-MAX($F$3:F4)), "0"))
This provides me with the results that I need as our mileage is constantly accumulating.
This formula does not work in regards to tracking passenger time as the nature of time being cyclical doesn't lend well to the formula above. I am not a psychic and will never be able to know exactly how many riders will be provided services a day, so hard setting a formula to check a certain number of cells wont work.
I know that I can set a logic function to check if we are counting miles for the passenger, and base a formula on that. However I have spent well over 6 HOURS today on trying to figure out how to tell excel to ignore shared time and provide me with the difference after time is no longer being shared with another rider during a specific date.
For all I know what I am trying to do could be impossible for excel, (which I highly doubt).
If anyone has any clue as to what I am trying to get excel to do, or if anyone has direct experience with this same concept of "Shared time" and "Shared Miles" I would greatly appreciate any help that you may be able to provie.
Jul 09 2021 01:44 PM
I'm not sure I fully understand but here is my try at it.
First off, I think your equation has some minor issues. in the attached you refer to C$3 instead of C$2 and it is bad idea to use "0" since that is text instead of a number.
However, since the data is always increasing with respect to pickup ODO and pickup time I think you can do this a little easier. For the mileage I used:
basically the mileage is the destination ODO - the maximum any ODO recorded before or the pickup ODO but not less than 0
for the time the equation is the exact same except I add the date to the time so that it keeps increasing day after day (and for this one it needed to start on row 3):