Freeway Volume Balancing Tool
Using the freeway volume balancing tool generally follows this workflow:
Prepare Files
Plan Network
Enter Raw/Initial Volumes
Balance Volumes
Inspect & Adjust
Initialize Worksheets
Make a copy of the “Freeway
Volume Balancing Tool.xlsm”
Excel workbook file for your
project. Each scenario should be
a separate file (ex. AM peak and
PM peak in separate files).
Suggest working completely
through 1 scenario, then copying
it to use as a start for the next.
The Excel workbook contains
worksheets that should be filled
out from left to right. Many
Column headings include a note
describing their contents.
Worksheet Contents
Corridor Lists each ramp and mainline
location in the freeway
network.
Raw-Initial
Volumes
Lists actual volume counts, or
forecasts, for each location.
Balancing Shows computations and
provides tools for performing
volume balancing
Diagnostics Provides a sortable list of all
locations and compares the
raw/initial and balanced
counts
Export to
Intersection
Balancer
Provides a table for exporting
the freeway balanced volumes
to the intersection balancing
tool.
Look at a map of the freeway network
and devise a plan to separate the
network into multiple corridors for
volume balancing (ex. separate I-39 to
balance NB and SB corridors
separately).
• On the Corridor worksheet, fill out
the project & scenario name, list
the name of each corridor, ramp
and mainline location, the number
of lanes, and the type of location.
Example Types:
There are 3 macros to run after filling
out the data on the Corridor
worksheet. These macros help prepare
the remaining worksheets:
• “Format Row Colors…” – Applies
formatting based on the corridor
names and mainline/ramp type.
• “Set affects 2+ corridors…” – Uses
the Link ID’s to determine
identical ramps in more than one
corridor. Formulas are set up on
the other worksheets so that
multiple listings of the ramp have
formulas referring to the first
listing.
• “Copy Location Information to
other Worksheets” – Copies data,
formats, and sets up formulas on
the other worksheets.
The Raw-Initial Volumes worksheet is divided into
3 sections:
• Location Information
• Raw/Initial Traffic Volumes
• Initial Input to Volume Balancing
Mainline and ramp names, location, and other
information transferred from the Corridor
worksheet.
Contains placeholder columns to place AADT,
ATR, Tube Count, and any other data about
each location.
If a location has more than 1 tube count, for
example on separate days or years, each count
should be in a separate section (Tube 1, Tube 2,
etc…)
Many columns might not be needed. The user
should add and delete columns as necessary.
Delete rather than hide unused columns to
minimize error/clutter.
If working with forecasted traffic volumes, add
a section & columns to indicate the forecast
source and volume. The worksheet could also
be used to show work, for example, converting
from forecasted AADT to hourly volumes using
k-factors.
Enter the source and volume selected as an
input to the volume balancing process.
The Balancing worksheet looks up the
information in these columns.
The Balancing worksheet allows the
user to show their work for balancing
the raw/initial volumes.
The automated balancing process uses
Excel Solver to iteratively minimize an
objective function. Manual or pro-rata
balancing methods may be necessary in
complex networks when the
automated process fails.
Because volume balancing has an
infinite amount of solutions, the
objective function penalizes iterations
that are not desirable. The penalty
functions section of the Balancing
worksheet includes calculations to
incorporate these desirable
characteristics of a balancing solution
that should:
• Minimize the difference between
the raw/initial and balanced counts
(minimize total network RNSE)
• Keep differences between
raw/initial and balanced to a
reasonable amount (minimize
individual RNSE)
• Spread adjustments between many
locations, rather than adjusting
only one location to balance the
network. (minimize standard
deviation of RNSE)
Fill out data and define formulas in the
following columns:
• Ramp Sign
• Volume Type (Hourly / AADT)
• Include as Solver Variable
• FINAL Balanced Volumes
Click the Run Automated Balancing
Macro Button on the worksheet
Inspect the RNSE values and Global Weight
Rank
• Make any manual Weight Adjustments to
prioritize locations in balancing. (Higher
weight means that solver tries to keep
balanced volumes closer to raw/initial
volumes)
• Mark any locations as Fixed Anchor
locations if desired.
• As a drastic measure, the user can directly
enter a value in the Balanced Volumes
column, then mark this location as blank in
the “Solver variable” column. Solver will no
longer modify the volume for this location.
Iterate until satisfied
with the Balanced Volume Results
Make any manual adjustments
to the FINAL Balanced Volumes
The Diagnostics worksheet lists the
raw/initial and balanced volumes, the
difference between these volumes, the
WisDOT RNSE comparison, and the
difference between the automated
volume balancing and final user-
adjusted volumes. This worksheet can
be sorted and filtered to find locations
with high RNSE or differences to
determine where the volume balancing
may need to be refined.
All volume balancing, regardless of
automatic or manual methods should
be inspected to ensure the results are
logical and reasonable.
Automated Balancing Workflow
General rules of thumb include:
- Differences of RNSE less than 3.0 are
typically acceptable,
- RNSE 3.0 to 4.9 may be acceptable,
- RNSE 5.0 or greater require further
investigation.
Sometimes high RNSE values are
unavoidable, especially if lowering the
RNSE negatively affects higher priority
locations. Notes should be included to
explain the reason for any high RNSE
values remaining in the final balanced
volumes.
High RNSE values may also indicate an
error in the raw/initial counted values
that should be noted, corrected, and
the network re-balanced.
Export
If also balancing ramp terminal or
other intersection, copy data from
the Export To Intersection Balancer
worksheet to the intersection
balancing tool.
Balancing freeways and intersections
can be iterative. Intersection
balancing may reveal information
that requires re-balancing the
freeways and vise-versa.
10.10.2019
Example Network
Schematic showing Link ID’s Corridor Location Information
Example ramps Affecting 2+
Corridors (1090, 1110). These
ramps are listed first in the NB
I-39/90/94 corridor, so they
are marked primary on
I-39/90/94, and secondary in
the NB US 151 corridor.
I-39/90/94 and USH 151 freeway corridors. Only the NB direction is shown on each for demonstration purposes. Not all worksheets or columns are shown.
• Assign a unique Link ID to each
location. The same ramp listed in
two or more corridors must have
the same Link ID. (There is a macro
that will use the Link ID to fill out
the “affects 2+ corridors column”
• Include a sketch, schematic, or
marked-up map of the analysis
limits with the Link ID’s labeled.
Types for off, on, and mainline are automatically
colored by the macros. To prevent a mainline location
from being colored, use Type “mainline.” or
“mainline ” (using a dot or space after mainline)
1 2 3 4 5
Column Brief Description
Selected raw/initial volume used as a starting point for volume balancing. This volume is
carried over from the Raw-Initial Volumes worksheet, so it will show 0 if no volume was
selected.
Contains the balanced volume output from Solver and user adjustments. Only cells marked
as TRUE in column #3 are changed by Solver. Formulas need to be manually entered in this
column to compute the cumulative sum of the volumes for locations not marked TRUE.
For locations marked TRUE, Solver will change the number in the Balanced Volume column
#2 at this location during its optimization process. These are typically ramp locations and the
first location in a corridor.
User adjustments to the “importance,” or weight that each location has within Solver. The
adjustment should initially be 1.0. Inspect the Global Weight Rank column when adjusting
to ensure that higher ranked locations are meaningful to the project and the user. See #5,
Global Weight Rank.
Lists the rank of each location based on the Global Weight column. 1 = the location with the
most weight. Solver will try to minimize the RNSE of higher ranked locations before
minimizing the RNSE of lower ranked locations. Ranks 1-10 are highlighted.
Provides an estimated "importance" of the location based on the hourly volume per lane
with respect to an assumed capacity of 2000 vph/ln. The value ranges from 0 to 1.0. The
capacity weight gets multiplied by the user weight-adjustment to determine the global
weight rank.
Automatic Balancing Worksheet Columns (not all worksheet columns shown)
1
2
3
4
5
6
6
User
Inputs
Auto.
Calculated
User Inputs
