Excel Formula and 310.16.

[iwire]

I am making an Excel spread sheet for my solar work and I have it set up to spit out NEC minimum ampacity for conductors. I would like to make it also indicate minimum wire sizes and maybe conduits as well.

Anyone have any ideas?

I imagine I will have to make a list and use some less than or greater than commands?

 

[Dennis Alwon]

I am making an Excel spread sheet for my solar work and I have it set up to spit out NEC minimum ampacity for conductors. I would like to make it also indicate minimum wire sizes and maybe conduits as well.

Anyone have any ideas?

I imagine I will have to make a list and use some less than or greater than commands?

Can you be more specific. Do you want to type a wire size and have an ampacity show up in the next cell?

 

[iwire]

Can you be more specific. Do you want to type a wire size and have an ampacity show up in the next cell?

No, flip that around.

When the MCA is listed in one cell I want the minimum conductor size to pop into another cell.

 

[Dennis Alwon]

No, flip that around.

When the MCA is listed in one cell I want the minimum conductor size to pop into another cell.

Okay but do you want all three values for 60c 75c & 90C. For copper and alum???

 

[iwire]

Okay but do you want all three values for 60c 75c & 90C. For copper and alum???

No, just 75 C, and for now just copper.

No derating issues, no temp correction issue, just the bare bones minimum for easy job planing. I may want to even get it to spit out conduit sizes for RMC or schedule 80 PVC.

 

[Dennis Alwon]

No, just 75 C, and for now just copper.

No derating issues, no temp correction issue, just the bare bones minimum for easy job planing. I may want to even get it to spit out conduit sizes for RMC or schedule 80 PVC.

You would have to type the table and there values in a part of the spreadsheet and you could do a vlookup formula. I haven't done one in awhile but I will try and figure it out for you. I am use to using Lotus not excel-- I'll give i a try.

 

[480sparky]

Try VLOOKUP.

 

[Dennis Alwon]

Try VLOOKUP.

I wrote that in my post before that but the problem I am having is if you don't type an exact value it will look for the closest lower value. Thus if you type in 35 amps it will look for 30 amps and use that as it's lookup value.

I can do it but you would have to have a table that had all the amp values which is tedious but doable.

Like this

amps wire size
1 14
2 14
3 14
.

30 10
31 8
32 8


This can be done by using fill functions but the table goes to 665 amps. Not practical.

There has to be a way with the "if function but I have not been able to figure out how to make a formula look for the value greater when the number is higher than a given ampacity.

 

[iwire]

Thanks guys, so far I am still having some fun setting up the layout and the basic math functions. :smile:

 

[Smart $]

Thanks guys, so far I am still having some fun setting up the layout and the basic math functions. :smile:

Download the excel file at the following address to see how I might do it...

http://oy2yza.blu.livefilestore.com...nimum 75°C Conductor Calculator.xlsx?download

Let me know if you can't open an excel 2007 file (.xlsx extension). I'll resave as .xls and repost.

 

[iwire]

Thanks. I got it open no problem, can't figure out how to view the table.

I see a formula in B2 but I don't see where it gets it's data?

 

[LarryFine]

No, just 75 C, and for now just copper.

No derating issues, no temp correction issue, just the bare bones minimum for easy job planing. I may want to even get it to spit out conduit sizes for RMC or schedule 80 PVC.

It seems that you could just make a simple hand-written chart for this small amount of data. All you really need is the ampacity break point for each conductor size.

 

[iwire]

It seems that you could just make a simple hand-written chart for this small amount of data. All you really need is the ampacity break point for each conductor size.

I know I have to make a chart or a table but I want it to be paper free.

Laying out a solar install is a bit different. :smile:

Imagine figuring feeder and branch circuit loads and sizes if every outlet in the building had exactly the same characteristics.

Automating the conductor sizes as I break up groups of modules will be very handy as I can instantly see what effect a different panel count will have on the conductor sizes.

For example if I have a roof that has 300 panels, should connect them altogether and run one large 'feeder' back to the inverter or should I break it up into two groups of 150, or maybe 3 groups of 100? :smile:

 

[Dennis Alwon]

Download the excel file at the following address to see how I might do it...

http://oy2yza.blu.livefilestore.com/y1pG0TA3zFXXnOZ4Lu5efTWhTmaOBYfhpW-4IJxU6bl71vnTfxHP7fvNV1C6Pi_m8aPGWQCozAgsjgcTOmhyY-tsg/Minimum%2075%C2%B0C%20Conductor%20Calculator.xlsx?download

Let me know if you can't open an excel 2007 file (.xlsx extension). I'll resave as .xls and repost.

Smart I would love to see the file with the .xls. I only have 2003 excel

 

[tkb]

I have a sheet that I can share with conduit fill.
It uses vlookup for the conduit tables.
Can I attach an .xls file to a post?

 

[iwire]

I have a sheet that I can share with conduit fill.
It uses vlookup for the conduit tables.
Can I attach an .xls file to a post?

I really don't know, I never tried. :smile:

 

[Dennis Alwon]

I have a sheet that I can share with conduit fill.
It uses vlookup for the conduit tables.
Can I attach an .xls file to a post?

No, you have to upload the file to a website.

 

[peter]

I made a scattered Excel program to do this sort of thing. Indeed, VLOOKUP will round down instead of up.
But I discovered a work around:
What I did was reverse the table so that the highest [800 amps] was at the top of the list and the lowest [15Amp = 15 Amp breaker] was at the bottom. There is a sort button for that [A Z or ZA].
You could also put a pricing chart in there and various variations or combinations of conduit fill.
The book for Excel is: The Excel Bible by John Walkenbach.
~Peter
Note to Iwire: I didn't know you smoked, but judging by your picture, I see a little black cigar in your mouth.

 

[Smart $]

Thanks. I got it open no problem, can't figure out how to view the table.

I see a formula in B2 but I don't see where it gets it's data?

Look down at the bottom left of the file window. There will be tabs for each sheet in the file. One is labeled Target Sheet, the other is labeled Lookup Table. This is typical of how you keep look up tables off the main view sheet.

 

[energymv]

I wrote that in my post before that but the problem I am having is if you don't type an exact value it will look for the closest lower value. Thus if you type in 35 amps it will look for 30 amps and use that as it's lookup value.

I can do it but you would have to have a table that had all the amp values which is tedious but doable.

Like this

amps wire size
1 14
2 14
3 14
.

30 10
31 8
32 8


This can be done by using fill functions but the table goes to 665 amps. Not practical.

There has to be a way with the "if function but I have not been able to figure out how to make a formula look for the value greater when the number is higher than a given ampacity.

I use VLOOKUP all the time. The fourth parameter of the VLOOKUP function will determine whether to look for an exact or an approximate match. Use "0" for exact. Use "1" for approximate.