Spreadsheet to determine position of a vehicle as it traverses a surface in a straight line and a corresponding factor without using VBA code

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 p

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.

StackExchange Code Review Q#126494, answer score: 4