balancing panelboard loads in excel?

[sepanolo]

Does anyone have a spreadsheet to balance the load of a three phase panelboard? I am currently using a simple spreadsheet to total the loads on the three phases but I need to modify my spreadsheet to balance the loads per phase to within 10 percent of one another.

Any information would be helpful.
Thanks

 

[IRstuff]

Seems like your spreadsheet already has everything you need.

You probably need to come up with a merit equation; something like the rms variation of each phase from the mean and let Excel's solver find load values that minimize the rms variation.

TTFN

 

[chakorules]

Try this one:

http://www.mrplc.com/cgi-bin/code/filedisplay.cgi?category=excel

Download "Control Panel Power 1.5 SR"

See if you can use this to your likening.

Chris Elston
Automation & Controls Engineer

http://www.mrplc.com

Download Sample PLC Ladder Logic Code
at MrPLC.com

 

[Ares]

Go to

http://members.rogers.com/g.cygalski/download.htm

at the bottom of page point to "Last updated...) link
and click or use the save us option and download the spreadsheet
It is in Excell 2000

 

[hbendillo]

I have a panelboard schedule sheet for which I am always looking for new functions and this may be a good one for me. I have a worksheet in my panelboard spreadsheet the audits values. For example if my bus size is less than the demand amps of the panelboard it colors text red. In your case you could set up a table that compares Phase A with Phase B, Phase A with Phase C and Phase B with Phase C and alerts you when the percent difference exceeds 10%. You could then go to the offending panel and move the loads around until the warning goes away. You could get your % difference values using some If statements and simple division.

 

[sepanolo]

thanks hbendillo, I will add your suggestion to my spreadsheet. I am also looking to automate the whole process. I would like to setup the spreadsheet so that it would automatically rearrange the loads so that the panel is balanced, but I don't know how to do it.

I have projects that have as many as thirty panels and checking each panel manually is time consuming.

Do you have any suggestions on how to setup the spreadsheet so it will balance the loads automatically?

 

[hbendillo]

Setting it up to work automatically would be quite a chore. Anything's possible I guess but how do you get the circuits in a panel to rearrange automatically? Your just not moving numbers but descriptions, wire sizes, breaker sizes. How do you get the program to decide which loads to move? Maybe I am wrong but it seems like a major undertaking. Doesn't seem worth it? Does anybody know of any commercially available program that does this?

 

[briand2]

I've played around a little to see if there is a "quick and dirty" way of doing this, and this is what I've come up with.

Start with a worksheet having four columns:

Phase, Way_No, Amps, Load_Name
Red 1 98 Kitchen
Yellow 1 7 AC Fan
Blue 1 44 Motor
Red 2 1 Small Motor
Yellow 2 150 Big Heater
Blue 2 22 Compressor

etc.

If you now select just the data in "Amps" and "Load_Name" columns and sort in either ascending or descending (doesn't matter which) magnitude of "Amps", the load will become reasonably balanced across all phases. You can have three cells displaying the individual phase totals as a check.

This really only works if the loads are spread fairly evenly across a range. If, for instance, the highest load is ten times larger than the next highest load, then the balance obtained will not be acceptable (probably).

I would suggest that you try it on several of your own typical real load figures and see how it turns out.

Let me know how you get on!!

(I'd probably need to use VBA to do anything much more sophisticated).

Brian

 

[hbendillo]

Thanks briand2. I have already produced a chart and a macro for calculating percent differences between the phases and it works OK but still has some bugs. One of the bugs is that the macro that I created has too many procedures and I am trying to figure out how to split it up. The difficult thing about this is that I have spreadsheets that have 19, 53 and 87 panelboards. Creating all of the formulas and procedures for balancing loads would be a nightmare I think. And as you said your rough solution would probably take some tweaking including a macro. I wish this were easy to do but I think I will try the old fashion way for this one.

 

[briand2]

hbendillo,

I might not have understood your requirements properly. I thought you were trying to balance the load across three phases on one panel board. Are you, in fact, trying to balance the load across more than one board?

If the boards are all adjacent to one another, that is just an extension of my suggested solution. If the boards are remote from each other, then voltage drop, etc will have a role to play (the board will really need to serve its nearby final circuits).

If you like, you can email me one of the spreadsheets and I'll have another look: my email address is brian.doherty@ntlworld.com.

Regards,

Brian

 

[gadero]

Just joined eng-tips...great idea!!!!

My 2 cents regarding balancing of phases....

It seems to me that the panel/circuit identification process should be proactive. If all panels are balanced automatically, does that mean someone would have to go back and change circuit designations on plans? That sounds like a laborious process and reactive in nature.

Hbendilo's suggestion of changing cell color sounds like a great idea. The individual panel schedule would constantly update phase totals and change color when balancing exceeds preset criteria. When the color changes, additional loads added to the panel would be adjusted to bring the panel into balance.

It seems to me that one wouldn't know if an upstream panel goes out of balance because of circuit entry on a downstream panel. Not sure how best to address that condition. Unless there is some sort of link between the 2 worksheets...

Good Luck