Spreadsheet Formulas: 3% drop and Ampacity

[scherzoja]

I'm rewiring my boat and am struggling to develop a formula in Excel that outputs the wire gauge necessary to maintain < 3% voltage drop. In addition, I want to have a formula that will give the the wire gauge for the ampacity of the wire. I am off by one wire gauge.

I've found threads discussing how to calculate the voltage drop, reference ABYC tables, and websites with calculators I can use to get the information I need, but those are all time-consuming because they require repeated input of each wire (length, current, etc...). I'd like to have a small number of cells to calculate these values. I have also found a thread that had a spreadsheet attached that did vlookups to the tables to give me the exact information I am after, but again, it requires repeated input. With the 100+ individual wire runs I have, that is tedious.

My spreadsheet calculates these values, but when I cross-check individual runs with other tools, my answers do not match these online calculators, so something is wrong. My calculated wire gauge is always smaller. For example, if my spreadsheet outputs 10 ga, the online calculators output 8 ga. The c Mil formula output in my spreadsheet matches the online calculators, so there must be something wrong with my vlookups

For every wire run, I have several fields, but my focus with the formulas are: C mils, Wire Gauge 3%, Actual Wire ga, Wire Gauge Ampacity, Actual Ampacity ga

C mils: CM=10.75*I*(L/E) This seems to be an accurate calculation of the C mils needed
Wire Gauge 3%: takes the c mils calculated and vlookup on the CMTable to return the wire gauge that overs that c mls
*This is that seems to be off sometimes, often reporting smaller wire gauge
*This formula also an If/Then statement that increases the wire gauge to 16 if calc'd wire gauge is > 16. I did this because if it returns 18 or 20 gauge, I'm going to run 16 anyway for that stretch.

Actual Wire ga: the actual calculated wire gauge that was returned before the If/Then > 16 in "Wire Gauge 3%" formula
*As expected, this is usually a smaller (larger, i.e 18, 20, etc...) wire gauge than "Wire Gauge 3%"

Wire Gauge Ampacity: another Vlookup that takes the amps of the wire to the Ampacity Table ( ABYC Table VI) and returns the recommended gauge based on ampacity.
*This also has an If/Then that bumps the calculated wire gauge to 16 if gauge is smaller than 16

Actual Ampacity ga: This is another cross check that shows me that actual calculated wire gage before the If/Then


Online calculators used to cross-check:
https://www.pacergroup.net/voltage-drop-calculator/
https://baymarinesupply.com/calculator

I've attached a sample of my spreadsheet and the spreadsheet I found in another thread (url unknown) that calcs individually

If anyone can spot my error, please let me know or if you have a better spreadsheet, please attach it.

Thanks y'all

[valhalla360]

Ideally, you don't want 20 different wire sizes, so don't try to size each circuit independently.

Pick a few categories and select the worst case and use that as the standard:
- 12v to the instruments, pick the combination of longest run and highest amps and the cable size becomes your standard for feeding the instruments.
- Anchor winch and bow thruster may be it's own category because they take such high amperages.
- Light & fan circuits may be another.

Looking up 3-4 (even a dozen) combinations negates the need to create your own spreadsheet.

[smac999]

the blue sea phone app is pretty easy. you can just go back and change 1 thing. no need to refill each time.

maintaining 3% voltage drop is going to be very expensive and heavy.. only the battery cables to switch and panel are 3%. normally everything else is 10%


there is really no point looking at ampacity. the voltage drop size cable will always be bigger anyways. and handle the amps,

[SeanPatrick]

I took a quick look at the sheet which I am guessing is yours (Wiringdetails-4a.xls). Here's my first WAG about what might be going wrong:

I think the online calculators are selecting the gauge which has a CM value greater than the input CM value. However, if the "VLOOKUP" function in Excel does not find an exact match for the lookup value, it will default to the next lower value. IMHO, the best solution would be to use the "XLOOKUP" function instead. This function can be told to default to the next higher value if no exact match is found. So, for example, instead of using the current formula:

=IF(VLOOKUP(AJ2,DATA!G$5:H$19,2)>16,16,(VLOOKUP(AJ 2,DATA!G$5:H$19,2)))

... you could use the formula:

=IF(XLOOKUP(AJ2,DATA!G$5:G$19,DATA!H$5:H$19,,1)>16 ,16,(XLOOKUP(AJ2,DATA!G$5:G$19,DATA!H$5:H$19,,1)))

"XLOOKUP" is a newer function which may not be available in your version of Excel. If it isn't, let me know and I will try to work out another method.

Hope that helps. Cheers!

NB: The forum software seems to have inserted arbitrary spaces in the formulas above which I cannot remove. If you choose to paste the second formula into your spreadsheet, you may need to remove those spaces. The two commas in a row near the middle and end of the formula are intentional and necessary.

[GordMay]

You seem to be more advanced, but others might be interested in "Ohm's Law & Boats" ➥ https://www.cruisersforum.com/forums....html#post1256


You can use column 6 [A/Ft, for 12V system], calculating in your head. Much faster than entering a bunch of numbers.

[PaulCrawhorn]

Just use Circuit Wizard, why reinvent the wheel?

[canyonbat]

I think if you adjust the table to the max cm per wire size, rather than the minimum, it will fix your formula. As stated above, the table is rounding up so to speak.

[Cheechako]

Originally Posted by valhalla360

Ideally, you don't want 20 different wire sizes, so don't try to size each circuit independently.

Pick a few categories and select the worst case and use that as the standard:
- 12v to the instruments, pick the combination of longest run and highest amps and the cable size becomes your standard for feeding the instruments.
- Anchor winch and bow thruster may be it's own category because they take such high amperages.
- Light & fan circuits may be another.

Looking up 3-4 (even a dozen) combinations negates the need to create your own spreadsheet.

THIS^
What's likely more important is standardizing size as much as possible and buying wire in various colors for identification.

[scherzoja]

SeanPatrick,

Thank you for the XLOOKUP function. My version of Excel has that and I did see the errant spaces. Preliminary testing showes that the XLOOKUP output matches then online calculators. It looks like that will work.

For the rest of y'all, the reason I don't use the online calculators is because of the large number of wire runs, all with different current and length values. It would be very tedious to run each through Circuit Wizard or any of the other, especially when I make changes (i.e. get more precise length values or modify the current).

I do plan to consolidate/standardize on a few wire sizes, but before I can consolidate, I need to know the variation in required wire sized to know what my allowable upper and lower gauges are. To find those, I need to look at each run and the spreadsheet seems to be the most effective way to get the overall picture.

I suspect that most runs will be 14 or 16 ga with the minority in the 6 - 12 ga range. I will soon know once I FillDown XLOOKUP and readjust any current and length values.

Thanks again for all the input.

[walbee]

If it were me undertaking a total rewire, I’d seriously consider digital switching devices with built in fuses. This is way of future, reduced cost, labor and improved reliability.
BTW, I’ve always used the ABYC iOS app for calculating wire needs. Essentially, my 42’ GB is wired with 14 awg.

[s/v Jedi]

Originally Posted by scherzoja

SeanPatrick,

Thank you for the XLOOKUP function. My version of Excel has that and I did see the errant spaces. Preliminary testing showes that the XLOOKUP output matches then online calculators. It looks like that will work.

For the rest of y'all, the reason I don't use the online calculators is because of the large number of wire runs, all with different current and length values. It would be very tedious to run each through Circuit Wizard or any of the other, especially when I make changes (i.e. get more precise length values or modify the current).

I do plan to consolidate/standardize on a few wire sizes, but before I can consolidate, I need to know the variation in required wire sized to know what my allowable upper and lower gauges are. To find those, I need to look at each run and the spreadsheet seems to be the most effective way to get the overall picture.

I suspect that most runs will be 14 or 16 ga with the minority in the 6 - 12 ga range. I will soon know once I FillDown XLOOKUP and readjust any current and length values.

Thanks again for all the input.

What people try to say is that instead of doing the math on every circuit, you take the longest worst case for each “class” of circuits. For example lighting. Now you do the math on that circuit and whatever gauge it comes up with, you use for all lighting circuits.

[PaulCrawhorn]

Originally Posted by walbee

seriously consider digital switching devices with built in fuses

links to reco examples, howto's, past discussions?

[valhalla360]

Originally Posted by s/v Jedi

What people try to say is that instead of doing the math on every circuit, you take the longest worst case for each “class” of circuits. For example lighting. Now you do the math on that circuit and whatever gauge it comes up with, you use for all lighting circuits.

Exactly, if the longest run to the instruments is 25ft and the highest amperage is 6amp @ 12v, you run 25ft @ 6amp @ 12v...whatever that comes up with, that's your standard gauge for the instrument circuits. Anything shorter and/or with lower amperage will be fine with the same gauge.

Result, you don't need to run the calculations for 100's of circuits.

Where you would look for different categories would be if there is a major change in length and/or amperage...The bow lights are likely only a few amps but the anchor winch could easily be 75-100amps, so the anchor winch would be a separate cabling category and you might lump that in with the bow thruster as both are high amp draws a long way from the stern where the battery bank is likely located (assuming you don't install a separate small one at the bow).

Trying to provide and manage 20-30 cable sizes with appropriate color coding would be a nightmare.