ObjectiveDetermine 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.SetupProvide 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) DeterminationAny 1 axle - 0.4Any 2 axles OR axles 1, 2 and 3 - 0.3Any 3 or more axle, except axles 1, 2 and 3 - 0.25Colour CodingBlue text - user defined inputsBlack text - constants or value that rarely require user to changeGreen text - values calculated by cell formulasRed text or red background yellow text - error when value out of rangeYellow background - areas where user inputs are placed that are ranges (copy paste ranges)LayoutThis 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/SequenceBased 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 surfaceI 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 ConsiderationsNo 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.
Spreadsheet to determine position of a vehicle as it traverses a surface in a straight line and a corresponding factor without using VBA code
excel
My personal advice for spreadsheet functions:Name Everything(within reason)You see this?=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))))))I've got no idea what it's doing, how it's doing it or whether there's an error in there or not.Writing functions is still writing code. Naming is hard, especially in a spreadsheet, but this is why named ranges were invented. Use them as much as you possibly can. Any static reference that looks like $[]$[] is a reference that should be a named range instead.So, let's make up some naming conventions for your spreadsheet. Let's use _ for multi-word names because otherwise they're really hard to read in the function bar. Let's preface every variable with V1|V2|V3 if it relates to one of the vehicles and with Axle[N] if it relates to an axle. Once we've named your setup ranges, that IF function above now looks like this:=IF(OR(AC178=,AD178=),, IF(AF178 = Forward, IF(AE178 < span1 + V1_Axle1_Ordinate, Axle1_Title, IF(AE178 < span1 + V1_Axle2_Ordinate, Axle2_Title, IF(AE178 < span1 + V1_Axle3_Ordinate, Axle3_Title, IF(AE178 < span1 + V1_Axle4_Ordinate, Axle4_Title, Axle5_Title)))), IF(AE178 < span1 + V1_Total_Length - V1_Axle5_Ordinate, Axle5_Title, IF(AE178 < span1 + V1_Total_Length - V1_Axle4_Ordinate, Axle4_Title, IF(AE178 < span1 + V1_Total_Length - V1_Axle3_Ordinate, Axle3_Title, IF(AE178 < span1 + V1_Total_Length - V1_Axle2_Ordinate, Axle2_Title, Axle1_Title))))))And suddenly, checking your function just got really easy. It's referencing the right vehicle, it's referencing the axles in the correct order going forward, and in the correct order going backwards. It's always outputting a title, and that title is always the axle it's currently checking. And I can tell all that just by looking at it.