Microsoft Excel: Multiply mileage based on location

 

1.       Based on Location (Location1 or Location2), then dividing Total Odometer Miles by the the designated Location Round Trip Mileage (In this example, Location1=48; Location2=58) to get approximate loads

 In this example L is the location column. Using the $ sign behind the L makes excel look at the column and go down through each row. 

=IF(($L2="Location1"),($O2/Sheet2!$B$2),IF(($L2="Location2"),($O2/Sheet2!$B$3)))

 

2.       Based on Location (Location1 or Location2), multiplying the given loads by the designated Location Round Trip Mileage (In this example, Location1=46; Location2=56) by Loads, we will call this Estimated Trip Mileage. [Estimated Trip mileage can be used to estimate the number of loads; this is specific to the task at hand] Then subtracting the Total Odometer Miles by Estimated Trip Mileage to get the remaining mileage.

=IF(($L2="Location1"),($O2-(N2*Sheet2!$C$2)),IF(($L2="Location2"),($O2-(N2*Sheet2!$C$3))))

(Not complete; still needs editing to make it make more sense)