← Back Published on

Universal Ampacity Chart for Wire Sizing in Excel

This calculator can be found for free at my GitHub as well as in a publicly shared google sheets document. Feel free to use it for any of your own projects!

Many times when I was working on projects for myself, or for student orgs, the question of buying the correct wire size came up. It's not completely intuitive, and I found the commonly accessible tools like ampacity charts to be confusing and often limited to standard use cases like 12V or 120V. I know there are more standardized systems such as ones with conductor temperature rating, like this website provides, however when optimizing for efficiency in electric vehicles or for doing simple baseline checks on unusual DC systems, these more standard models tend to be confusing or useless due to the conditions they are intended to be used for.

Figure 1: A typical ampacity chart geared towards commercial/housing wiring

The intended goal of this calculator was to be able to input an operating voltage, a corresponding acceptable percent voltage drop across the wire, a length range to calculate over, and an optional spot for resistivity of the wire (generally assumed to be copper). The chart would then automatically fill itself out with a spread of current ratings set for each row, and length values for each column.

Based on some correlations and simple electrical property equations I was able to construct a formula that given specified voltage drop in V, current in A, and length in ft would provide the adequate AWG value to perfectly meet those requirements. 

The following three equations can be substituted into one another and solved for n, where n is the AWG value. The AWG correlation used is found in the ePLAN harness pro AWG to mm2 library.

Figure 2: The three fundamental equations used to build the AWG calculator

After algebraic manipulation to solve for n and unit conversion from m to ft we are left with the following equation which drives the entire spreadsheet:

Figure 3: Primary equation responsible for sheet functionality

Converting this into a usable excel formula with a bunch of extra if statements for formatting related calculations (e.g. to take n=-4 and turn it into 4/0), we are left with the following:

Figure 4: The giant formula that runs each calculated cell in the entire program

To use the chart, all you need to do is pick your nominal voltage, the maximum percent voltage drop you are willing to see on the wire (with factor of safety included), the step size for current after 10A to get whatever high current values you might be interested in (by default I have it set to a 10A step which reaches 500A by the end of the page), and the wire length range in ft you are interested in. I have also provided a standard resistivity for copper wire that should work for most calculations, but if you are using a different conductor material, you are able to change that parameter as well.

Figure 6: A demonstration of the input settings for the calculator

Final notes:

The sheet should calculate very quickly (<0.5s) despite the huge formula, since most of the extra calculations don't run if the conditional statements aren't met.

It's also intended to be the correct size to print at a readable quality on letter paper, but it's absolutely possible to expand the boundary of the sheet for larger format analysis (although the practicality/reasons to do so drop off quickly).

Hopefully this can be a useful tool to others who have been looking for something like this!