I need to create a spreadsheet in Google Drive that will let me calculate mileage for my employees. I know the exact mileage to the locations they'd have to drive to. What I need to do is have a constant entered when they type in the location code. Ex... If in C1 they enter the code 2 then in D1 it will automatically input the mileage (1.8). I'll only have 2 mileage codes. How can I do this?


If you want to create a drop-down list of your locations, see the instructions on data validation at https://support.google.com/docs/answer/186103 . Presumably you'd use it with a formula to convert location names to numeric values.

If you want to create a custom function to do that conversion, see the quick-start tutorial at https://developers.google.com/apps-script/quickstart/macros . It gives an example JavaScript function that converts meters to miles, also a longer example that calls the Google Maps API to look up the driving distance between two addresses.


If in C1 they enter the code 2 then in D1 it will automatically input the mileage (1.8).

I have a different interpretation of the requirement, for which SWITCH would be very adequate (in D1):


With the above entry of 2 in C1 will return 1.8 in D1, and 6 return 5.6. Text, 0 or blank will return #N/A.

For a lot of mileage codes alternatives such as a lookup table may be more practical, but the above should suit where there are only a few codes.

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.