Forum Discussion

heathmichelle91's avatar
heathmichelle91
Copper Contributor
Apr 25, 2025

Help Creating an Excel File to Calculate Student Commutes to Clinical Sites and Filter Site Details

Hello,

I’m hoping someone can help me create an Excel document for a fairly complex need.

I oversee a large number of students across my state and am trying to ensure fairness in the clinical rotations they are assigned to. I would like to set up an Excel spreadsheet that can:

  • House student names along with their home addresses.
  • List multiple clinical site addresses that students may rotate to.
  • Calculate and display the commute time and distance (in miles) from each student’s home address to each potential clinical site.

Additionally (if possible), I would love to be able to filter the clinical sites based on certain characteristics, such as:

  • Types of MRI scans performed at the site
  • Patient volume (high volume vs slower paced)
  • Type of location (small town hospital, large city hospital, or mobile MRI unit)

If the filtering features are too complicated, I would at least like help setting up the commute calculations between home addresses and multiple site addresses.

I appreciate any guidance or ideas. Thank you so much in advance for your help!

3 Replies

  • carolineharper's avatar
    carolineharper
    Copper Contributor
    Jan 05, 2026

    You can handle this by using a structured spreadsheet. Keep one sheet for students with names and home addresses, and another sheet for clinical sites with site addresses and details like scan type, patient volume, and location type. Store each attribute in its own column so filtering stays simple. For commute distance, converting addresses to latitude and longitude allows consistent distance calculations. Once distances are calculated, standard filters or tables can be used to narrow sites by the required characteristics.

  • Loren_liz's avatar
    Loren_liz
    Copper Contributor
    Oct 29, 2025

    That question’s pretty old, but I actually made something similar not long ago, so here’s a quick overview.
    You can set this up in Excel using a few tables and an add-in. Create one sheet for students (name and home address) and another for clinical sites (site info, type, volume, etc.). Then use the Bing Maps Excel add-in or a Google Maps API script to calculate commute time and distance between addresses.
    Once that’s working, you can build a simple matrix showing each student’s commute to each site and add filters or slicers to narrow sites by type, volume, or location. The Bing Maps add-in is the easiest option if you just need quick distance and time calculations right in Excel.

Resources