6
\$\begingroup\$

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.

Vehicle Information

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.

Iteration information

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.

Main Calculations

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.
\$\endgroup\$
  • 2
    \$\begingroup\$ Prior to posting this spreadsheet process for review I did inquire if this was the right place for it in this link.If you feel this is not the right place for it, as per the link, where should I post for review? \$\endgroup\$ – Forward Ed Apr 23 '16 at 4:19
  • \$\begingroup\$ I was just going through reformatting the equations to display in a cascading fashion instead of one long line requiring scroll bars. Draw back is you cannot easily cut and paste if someone was inclined to do so. I am not sure which way people prefer. One thing I did notice is I did not wrap my equations for the second and third vehicle in an if statement to display "" when the lead in information has not been totally supplied. Thankfully an easy fix. \$\endgroup\$ – Forward Ed Apr 23 '16 at 10:47
  • \$\begingroup\$ I think it reads better like that as cascading, it's easy enough to reformat them to put them in Excel. \$\endgroup\$ – Phrancis Apr 23 '16 at 12:03
  • \$\begingroup\$ I dont particularly care for the way I am determining the lead/trailing axle. I think its ok for the 3 to 5 axles I am dealing with, but If I was dealing with say a train and had 15 axles (which I am not) that formula would get long and repetitive with the IF statements. Just wondering if there is a better approach maybe using offset, match, index, vlookup, hlookup, etc? Or add some columns/rows to make some function easier. That is one nice thing, I am free to add columns and rows and rearrange cells! \$\endgroup\$ – Forward Ed Apr 23 '16 at 23:40
4
\$\begingroup\$

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.

\$\endgroup\$
  • \$\begingroup\$ I suppose I could do the same thing for a fixed column reference too. Just need to be careful with setting up the name V1_Leading_Axle defined as $AG178 while the a cell in row 178 is selected. I totally agree that it is so much easier to read and it still has pattern to it. Part of the reason I hated that particular equation was it length and difficulty to read it. \$\endgroup\$ – Forward Ed Apr 23 '16 at 20:14
  • \$\begingroup\$ I was just going through updating the formulas using search and replace to change cell reference to the named ranges. I even implemented what I said in my previous comment. But I realized that if I copied the named range over to vehicle 2 and vehicle 3 then I would have to update their names. At which point I am exchanging formula readability for a bit of editing every time I copy over. Which is why Zak only reference it for double locked cells ie $A$1 as opposed to $A1, A$1, or A1. \$\endgroup\$ – Forward Ed Apr 23 '16 at 22:50
  • \$\begingroup\$ Once thing about putting in long names for the named ranges, is you need to be aware of the maximum character limit for an equation in excel. \$\endgroup\$ – Forward Ed Apr 23 '16 at 23:35
  • 1
    \$\begingroup\$ @forward ed I would suggest, if your function is hitting the character limit, then that's a good indicator it should be split over several cells. \$\endgroup\$ – Kaz Apr 23 '16 at 23:56
  • \$\begingroup\$ It has not yet, but looking at it, its getting close! As commented on the main question I was wondering if there was a better way to set this check up than using a series of nested if statements. or a generic if statement that would be the same for all vehicles because it know how to deal with none existing axles \$\endgroup\$ – Forward Ed Apr 24 '16 at 0:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.