Controller Load Calculator

[Guest guest]

I have created an XLS file that will assist you in channel layout and calculations for up to 10 controllers. Each controller sheet can handle up to 40 line items that can be assigned to any of the 16 channels. This sheet works for me since I have several lights in several locations on a single channel giving me a specific inventory and load of each location. The sheet will calculate each line, channel, bank 1-8 and 9-16 and controller for up to 10 controllers.

Test it out and let me know if I have made any errors or you would like to see any modification.

The excel file was too large to upload, so here is the link.

http://www.quartzhillchristmas.com/12.html

NOTE: The light inventory page is intended as examples ONLY, please check your inventory for the proper wattage and light quantity and change accordingly. You only have to enter this data once so be cautious to put the correct data since it propagates through the entire workbook.

Rick C Williams
Quartz Hill Christmas

[Guest guest]

That looks sweet. Cant wait to get home and load it up with my figures. Thanks a lot for the information.

[Guest guest]

Rick:

You are the man! that's an awesome spreadsheet, I was just thinking yesterday how I was going to manage all my channels so I don't blow a triac.

You just answered that for me! I like how you made a seperate worksheet for each controller, and have provisions for well documented and granular descriptions of each light type an dpower consumption.

Congrats on a job well done. This should be used by everyone to protect their investment.

One recommendation, on the main worksheet, you could create a legend that lists the top 10 lighting types, and what their average power is per string, and amperage, and volts per bulbs, etc.

[Guest guest]

Wow, I have seen several people post their load calculators, but this one is certainly the best. Thanks!

Here is my suggestion: The "controller totals" tab doesn't have any error checking on it. For example, I put 1226.62 amps on controller 1. The "controller totals" tab does infact show the board is running at 1226.62 amps, but it doesn't turn red to indicate there is a problem. I know it's not hard to compare cell D9 to H9 make sure it isn't over 40amps (or whatever your board capacity is.)

My only other suggestion is to have a "catch all" error on the "controller totals" tab. If there is an overload ANYWHERE on any board, then the "controller totals" tab should have a flag that turns red. Is that difficult? It's a safety feature incase you miss/forget the controller with an overload.

I checked out the formulas to see how it works. Where is the programming to turn the cells red? I'd like to change some of the threshold values. Some users' applications won't have the same thresholds depending on their boards, wiring, and heatsinks. What about having a configuration spreadsheet to set all the threshold values?

Really, if you can just give me a clue as to where the programming is, I can do it myself. From what I can tell, the cells turning red is a macro...? But I can figure out where the VB programming is for it.

[Guest guest]

Google for "conditional formatting" excel and you'll have a good head start.

Tom

[Guest guest]

Rick

Very nice, I will be playing with it tonight, but from what I have seen so far, good job.

Bill

[Guest guest]

Very nice job I will be using it aswell.



Roman

[Guest guest]

contactmike1 wrote:

Here is my suggestion: The "controller totals" tab doesn't have any error checking on it. For example, I put 1226.62 amps on controller 1. The "controller totals" tab does infact show the board is running at 1226.62 amps, but it doesn't turn red to indicate there is a problem. I know it's not hard to compare cell D9 to H9 make sure it isn't over 40amps (or whatever your board capacity is.)

Mike - The Controllers Total tab was not intended to identify overloads, since I felt it was redundant when the overloads are actually identified on the controller worksheet at several levels: String, Channel, Bank and controller - The MAX amperage is entered on the Controller Total worksheet and referenced from the Controller # worksheets to determine capacity (idea being: if controller #1 is only 20 amps and controller #2 is 40 amps) If you want to add this, it's just a conditional checksum

I will modify the worksheet to have this.

My only other suggestion is to have a "catch all" error on the "controller totals" tab. If there is an overload ANYWHERE on any board, then the "controller totals" tab should have a flag that turns red. Is that difficult? It's a safety feature incase you miss/forget the controller with an overload.

It's already built in, change line one of controller 1 to a string qty of 15, this will show the overloads immediately, but if you have 1226 amps on the Controller Totals tab for controller #1, 14 amps will overload the channel but not the bank or board. I did it this way to identify overloads on the controller # worksheet so that as you build your controller schedule any overloads will be idetified as you build it and it would be seen before even looking at the Controllers Total tab. Not a bad idea though.

BTW, where can I get a 1226 AMP single board controller?

I checked out the formulas to see how it works. Where is the programming to turn the cells red? I'd like to change some of the threshold values. Some users' applications won't have the same thresholds depending on their boards, wiring, and heatsinks. What about having a configuration spreadsheet to set all the threshold values?

The cell programming is only conditional formatting, the threshold is set by the MAX amperage on the Controller Totals worksheet (.00009 AMPS to 999999 AMPS) The workbook was designed with a LOR 16 Channel board in mind and it's various configurable limitations. Changing the MAX Amperage for each controller on the Controller Totals worksheet will automatically adjust these values.

Really, if you can just give me a clue as to where the programming is, I can do it myself. From what I can tell, the cells turning red is a macro...? But I can figure out where the VB programming is for it.

No clue to give, the calcs are done at the cell level / sheet level - No macros, No VB. Just basic calculations for calculating watts into amps........Sorry

I am making suggested modifications now and hope to have the update posted tonight.

Thanks for the suggestions and kind words.

-Rick C Williams-

[Guest guest]

tsmith35 wrote:

Google for "conditional formatting" excel and you'll have a good head start.

Tom

Perfect. Thanks!

[Guest guest]

OK!, Here they are in version B:

1) Added a light types worksheet with all the general lights and watts I could locate. This will allow the user to pre-configure the inventory of lights that can now be selected from the Controller # worksheets and will populate the wattage for that light - Major time saver!

2) Added drop down validation to controller # worksheets.

3) Added Overload flags to Controller Total worksheet.

I think that's it, if you find something let me know.
Thanks a bunch for the modification ideas.


http://www.quartzhillchristmas.com/12.html

[Guest guest]

Very nice! Much faster than I could have done it. Can the columns be moved around without messing up the formulas? I'll have to try that out. For me, I'd rather have all the user editable field together.

I think I might work on a statistics page too. Max capacity draw, total # bulbs, total watts, maybe an extension cord length category to calculate miles, # of lights of each color? If you take the time to fill out the whole worksheet correctly, it'll make display stats easy! Thanks for the tool. It works great!

Will you mind if I post this on my website? I'll leave your credit on the controller total tab. I'll let you know if I come up with any edits.




**edit grammar**

[Guest guest]

I love it. Sorry everyone that is using the one I shared awhile back. But I am switching. This one is great extremely easy to use. I will just add all my storage data into this one and I am set. Rick thanks again for adding the pull down feature for the light types with there wattage already set up.

[Guest guest]

Very Nice ! Thank You. :waycool:

Tim

[Guest guest]

It looks great. Thanks!

I'll need to adapt it to handle more controllers. So far I'm using 34 controllers for this years' light show. The most difficult part of my show is power management. This will help.

[Guest guest]

Say, Dan ....

Would it be possible to write a utility that would extract the channel data out of a sequence? For folks like myself that have one set up from last year it would be a time saver.

At the very least if it could extract the Channel Name, Controller Number and Controller Channel and dump it to a text file.

[Guest guest]

Although what Rick did is totally great and plan to use it, Don, that's a great request as it would save in retyping everything from each channel (lights, channel name & such) to a program to keep track of everything (especially Marty's 34 controllers). But until then, I will put Rick's to heavy use.

Tom

[Guest guest]

One thing I did observe is that this work sheet is as well for 120v usage only.. Don't know how many of our across the pond users may even use this. but the formula being used to the watts to amp conversion is

=IF(I6>0,((J6*F6)/120)*K6,0)

Now if you wanted to just use this in a 240 application you would have to go and change all the /120 to /240) watts/fixedvoltage=amps.

May or may not be worthy of mentioning just something I happened to see.

[Guest guest]

This is great for me I cant wait to use it tonight.

I didn't notice Icicle Lights in the drop down box, so I just added extra 100 minis to take care of it this will be a huge help for me.



Dale L

[Guest guest]

STICKY!

Very Nice!

Zac

[Guest guest]

Wow!, isn't Christmas GREAT!

For version C, I will try to have the following modifications done to the Controller Light Calculator:

1) Add a total AMPS on Controller Totals page.

2) With the idea of folks having MANY controllers, I will be creating two other versions, one with 25 controllers and the other with 50 controllers.

3) Need to fix the light bulb QTY on Controller worksheets, should populate automatically.

4) Move all editable columns to the left of the worksheet.

5) Add option on Controller Total page for Voltage selection (120/240).

6) Possible service circuit selection on 10/15/20 AMP with option of selecting 80% load safety.

I would have had it done today, but they made me do real work! plus I have the SoCal mini-PLUS to plan for, I will try and get this out ASAP.

Thanks everyone!