Objective
Determine a factor (A) based on the leading and trailing axles that are present on a length of a surface based on an iteration from 3rd party software.
Setup
- Provide an area for user to configure vehicle axle position.
- User provides surface length (straight line).
- User provides initial iteration number from 3rd part software.
- User provides distance vehicle moves between iteration in 3rd party software.
- User provides if vehicle travels forward only, backward only, or in both directions.
- User provides iteration number to find the factor (A)
Factor (A) Determination
- Any 1 axle - 0.4
- Any 2 axles OR axles 1, 2 and 3 - 0.3
- Any 3 or more axle, except axles 1, 2 and 3 - 0.25
Colour Coding
- Blue text - user defined inputs
- Black text - constants or value that rarely require user to change
- Green text - values calculated by cell formulas
- Red text or red background yellow text - error when value out of range
- Yellow background - areas where user inputs are placed that are ranges (copy paste ranges)
Layout
This box of information lays out the standard vehicle. The user could technically enter their own vehicle here. For the most part this information should not change unless design manuals change which the user could maintain without new formulas potentially.
The user enters 3 values from 3rd party software for each of the 3 vehicles. usually this information is the same but technically speaking it can be different.
Flow/Though Process/Sequence
Based on the length of the vehicle, the length of the surface and the supplied step increment, the number of iterations to get the rear axle off the surface is calculated. It is important to note that in the 3rd party software the initial iteration number is a blank iteration and the 2nd iteration positions the lead axle in the direction of travel at distance 0 on the surface. This is counted as being on the surface. When both directions is selected, the iteration that would put the last axle off the bridge is actually the iteration where the truck is flip, and the rear axle become the leading axle at distance 0 on the start of the surface. When the rear axle is on the end of the surface, then similar to the start of the surface and the lead axle, it is still consider to be on the surface
I use the following formula to calculate that:
=(ROUNDDOWN((span1+$AH$158)/AF169,0)+1)
span1 is a named cell holding the length of the surface. This result is used to determine the direction the vehicle is travelling when the 3rd party software is set for both directions.
The max steps is twice the one way steps and is provided for comparison purposes with the 3rd party software. It helps the user to confirm if 3rd party software and Excel sheet are setup the same to work with each other.
The three vehicles are shown at the same time as opposed to selecting as 3rd party software only runs one vehicle at a time and each has differing results and the results of for each vehicle need to be compared at the same time on another worksheet.
And here is the bulk of what this sheet does. The rows corresponding to the yellow background have the same formulas copied down; it's just the information in yellow that changes. The leading and trailing axle formulas differ between vehicles and the rest of the formulas remain the same.
The distance traveled is the first column calculated. It's based on the aforementioned setup information and calculated from the iteration # in the first column of the respective vehicle. The formula has also been wrapped in some formatting statements so as to display "" when not all the information has been provided yet in the respective vehicles 1st and 2nd column.
The Distance Traveled equation is as follows:
=IF(OR(AC178="",AD178=""),"",
IF(AND(AC178<AF$168+AF$172,AF$170<>"Backward"),
(AC178-(AF$168+1))*AF$169,
((AC178-AF$168)-(AF$172+1))*AF$169))
It is supposed to calculate the distance the lead axle has traveled from the start of the surface.
The direction of travel is based on the setup information and on the iteration number. Again it has been wrapped in some formatting to display "" when not all the info has been supplied.
The equation in the Direction column is:
=IF(OR(AC178="",AD178=""),"",
IF(AC178<AF$172+1,
IF(AF$170="Backward","Backward","Forward"),
IF(AF$170="Both","Backward","Logic error with this formula")))
The leading axle calculation gets a bit ugly, the gist is it's suppose to compare the distance traveled by the lead axle and determine if it is still on the surface. If it's not check the next axle. It will display whatever axle is the closest to the lead axle and still on the bridge. Part of the reason it's so ugly is it has to do it for the truck driving forward or reversing across the surface. The formula is unique to each vehicle as the number of axles in each vehicle is different.
In the Leading Axle column the following is placed:
Vehicle 1
=IF(OR(AC178="",AD178=""),"",
IF(AF178="Forward",
IF(AE178<span1+$AC$158,$AC$157,
IF(AE178<span1+$AD$158,$AD$157,
IF(AE178<span1+$AE$158,$AE$157,
IF(AE178<span1+$AF$158,$AF$157,$AG$157)))),
IF(AE178<span1+$AH$158-$AG$158,$AG$157,
IF(AE178<span1+$AH$158-$AF$158,$AF$157,
IF(AE178<span1+$AH$158-$AE$158,$AE$157,
IF(AE178<span1+$AH$158-$AD$158,$AD$157,$AC$157))))))
Vehicle 2
=IF(AN178="Forward",
IF(AM178<span1+$AC$159,$AC$157,
IF(AM178<span1+$AD$159,$AD$157,
IF(AM178<span1+$AE$159,$AE$157,$AF$157))),
IF(AM178<span1+$AH$159-$AF$159,$AF$157,
IF(AM178<span1+$AH$159-$AE$159,$AE$157,
IF(AM178<span1+$AH$159-$AD$159,$AD$157,$AC$157))))
Vehicle 3
=IF(AV178="Forward",
IF(AU178<span1+$AC$160,$AC$157,
IF(AU178<span1+$AD$160,$AD$157,$AE$157)),
IF(AU178<span1+$AH$160-$AE$160,$AE$157,
IF(AU178<span1+$AH$160-$AD$160,$AD$157,$AC$157)))
The trailing axle column is similar to the lead axle calculation except it is dealing with the trailing end of the vehicle and determining which axle of the truck that is actually on the surface is the one closest to the rear of the truck. The trailing axle formulas are as follow:
Vehicle 1
=IF(OR(AC178="",AD178=""),"",
IF(AF178="Forward",
IF(AE178<$AD$158,$AC$157,IF(AE178<$AE$158,$AD$157,
IF(AE178<$AF$158,$AE$157,IF(AE178<$AG$158,$AF$157,$AG$157)))),
IF(AE178<$AH$158-$AF$158,$AG$157,IF(AE178<$AH$158-$AE$158,$AF$157,
IF(AE178<$AH$158-$AD$158,$AE$157,
IF(AE178<$AH$158-$AC$158,$AD$157,$AC$157))))))
Vehicle 2
=IF(AN178="Forward",
IF(AM178<$AD$159,$AC$157,
IF(AM178<$AE$159,$AD$157,
IF(AM178<$AF$159,$AE$157,$AF$157))),
IF(AM178<$AH$159-$AE$159,$AF$157,
IF(AM178<$AH$159-$AD$159,$AE$157,
IF(AM178<$AH$159-$AC$159,$AD$157,$AC$157))))
Vehicle 3
=IF(AV178="Forward",
IF(AU178<$AD$160,$AC$157,
IF(AU178<$AE$160,$AD$157,$AE$157)),
IF(AU178<$AH$160-$AD$160,$AE$157,
IF(AU178<$AH$160-$AC$160,$AD$157,$AC$157)))
After finding both the leading and trailing axles which I personally found the hardest task and still not happy with how I did it. I set up the equation for determining the number of axles on the surface along with the special axle 1, 2 and 3 case in the Factor (A) column:
=IF(OR(AC178="",AD178=""),"",
IF(ABS(AG178-AH178)=0,0.4,
IF(OR(AND(AG178=1,AH178=3),
AND(AG178=3,AH178=1),
ABS(AG178-AH178)=1),
0.3,0.25)))
There is also a Second Check that repeats the same process exactly but get supplied with a different set of iteration numbers and corresponding values. There potential for a Third check too would be the same set up but different iteration numbers and corresponding values but thus far they have not been a factor to be considered.
Restrictions and Considerations
- No code allowed. I admit this would be much easier in code and I would prefer to do it with excel VBA, however not an option.
- Must be able to work on MS 2013.
- 3rd party initial iteration may not always be 1.
