Excel Formula and 310.16.

Status
Not open for further replies.
energymv said:
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.
Click to expand...
Yeah... but if an exact value in the lookup column is not found an error is returned—#N/A—and "approximate" lookup always uses the row having a value equal to or largest less than the lookup value.
 
Last edited:
Thanks smart. I had just come to the decision that I had to do exactly what you did. I waited for your post to see if you had it different. I appreciate the help esp. with the file converter.
 
peter said:
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].
Click to expand...
Hmm... that don't work for me.

If I sort my table's leftmost column largest to smallest and do a VLOOKUP(<value>,<table>,2) all I get is a bunch of #N/A's in Excel 2007. In viewing Help On This Error, it specifically says the data needs to be sorted in ascending order.
 
Smart $,
Sorry about that. I was posting from my laptop which didn't have the Excel program. Now I'm in from my terrace and at my desk and I looked at the program.
It wasn't VLOOKUP after all.
"Breaker size" = INDEX (U8:U36, MATCH (J22,U8:U36,-1))
I'm not sure what all this means since I did it a couple of years ago. But U8:U36 is the inverted list. J22 refers to the input. I dunno what the -1 is but it's probably important.
~Peter
 
peter said:
Smart $,
Sorry about that. I was posting from my laptop which didn't have the Excel program. Now I'm in from my terrace and at my desk and I looked at the program.
It wasn't VLOOKUP after all.
"Breaker size" = INDEX (U8:U36, MATCH (J22,U8:U36,-1))
I'm not sure what all this means since I did it a couple of years ago. But U8:U36 is the inverted list. J22 refers to the input. I dunno what the -1 is but it's probably important.
~Peter
Click to expand...
Yeah the -1 is important!

You used the MATCH function and the -1 is the match type. It's similar to VLOOKUP but it finds the smallest value equal to or larger than the <lookup_value> (your J22). For this to work, the range (your U8:U36) has to be sorted in descending order.

Now that would work on a two-column inverted ampacity table, coupled with the INDEX function.
 
Last edited:
I would suggest www.openoffice.org (Oo_O)for those having any issues opening xls type files.

You can read about Oo_O on there website or here: http://en.wikipedia.org/wiki/OpenOffice.org

It's free, works with common MS files (*.*doc; *.*xls; etc) up to Office 2007.
The wave of the future.

********
Interesting thought you have there iwire ~ if I find the time, maybe I'll take a stab at it (it would make a nice side car to my estimating spreadsheet). I use Office97 ~ but the newer editions are all backwards compatible :smile:
 
iwire said:
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:
Click to expand...
Use a sub-panel! :D


If there are no reliability issues where, for example, they'd rather have partial capacity during a parts failure, or practicality issues where, for example, there's no convenient way to join several feeders at the load end, then I'd let material and labor cost comparisons be the deciding factor.
 
celtic said:
I would suggest www.openoffice.org (Oo_O)for those having any issues opening xls type files.

The wave of the future.
Click to expand...

Such an interesting thread!

Thanks for the Spreadsheet example.

Very clear, and exactly NEC compliant.

I would have simply used an equation,
and done a little rounding in my head.
That's what engineer types do.

IMHO, The Open Office approach, which is 'Forward Compatible',
is a better approach than MS philosophy,
which is to make things 'Backward Compatible' !!!
 
LarryFine said:
Use a sub-panel! :D
Click to expand...

We will be ...... more or less, a lot of them. Only it works in reverse and it's called a combiner box.

The 'branch circuits' are the supply and the 'feeder' is the load.


I have been pretty quiet about the scale of the installation but one area of the roof might require per the NEC 850 amps worth of conductors from the inverter to the combiner box. Or I can use more combiner boxes and more feeders. Once I pass about 300 amps I will have to run parallel feeders anyway so I might as well go the Larry route and opt for two or three combiner boxes spread out to keep the branches short. :cool:
 
Last edited:
glene77is said:
IMHO, The Open Office approach, which is 'Forward Compatible',
is a better approach than MS philosophy,
which is to make things 'Backward Compatible' !!!
Click to expand...
Actually, regarding Office programs, it had been [fairly] forward compatible until the release of Office 2007.
 
iwire said:
We will be ...... more or less, a lot of them. Only it works in reverse and it's called a combiner box.

The 'branch circuits' are the supply and the 'feeder' is the load.


I have been pretty quiet about the scale of the installation but one area of the roof might require per the NEC 850 amps worth of conductors from the inverter to the combiner box. Or I can use more combiner boxes and more feeders. Once I pass about 300 amps I will have to run parallel feeders anyway so I might as well go the Larry route and opt for two or three combiner boxes spread out to keep the branches short. :cool:
Click to expand...
Do you derate the wires on the roof as per table 310.15(B)(2)(c)?
 
tkb said:
Do you derate the wires on the roof as per table 310.15(B)(2)(c)?
Click to expand...

Big time.

By the time I take the panels Isc add 25% per 680.8(A)(1) and another 25% per 680.(B)(1) and use the 30 F adder from Table 310.15(B)(2)(c) on top of a 100 F ambient temp I end up with 4/0s for a 'real load' of about 80 amps. (Excel speeds all that up :) )
 
celtic said:
Interesting thought you have there iwire ~ if I find the time, maybe I'll take a stab at it .....
Click to expand...

Well...THAT didn't happen...but maybe this will help:
Excel Table 310.16 Reader [.xls file!]
This Excel spreadsheet reads Table 310.16 for copper conductors only. This program is similar to the JavaScript program that reads Table 310.16.
Click to expand...

Half the work is done [conductor sizing]...it could probably be tweaked for conduit sizes.


**************
The above file is from: http://www.electrician2.com/calculators/elcal.html
They have a bunch more stuff for low cost as well
 
Status
Not open for further replies.
Top