How to count how many rails we need for a solar panel system

Hello, I have a complicated formula that I'm trying to create and can't figure out how to do it. I don't fully know how to even start this, but here's the first part. I want a formula where there are 3 numbers that are set. So the numbers are 3,4, and 5. I want to input a random number and what would populate would be a configuration of those 3 numbers to add up to the number inputted. So for example, if I put in the number 21, the configuration would be 5,4,4,4,4 (5+4+4+4+4). If the input number is 9, the output is 5,4 (5+4). The parameter would be 5, then 4, then 3. So for example for the number 21, you can also do 3 (7X), but the configuration is better with the number 5 rather than 3.


Now for some explanation. Essentially this formula is to count how many rails we need for a solar panel system. So each solar panel is about 66" X 39". Panels can be laid out in portrait (short distance 39") or in landscape (long distance 66") side by side. When the panels are laid side by side in a row it's called an array. So if a house had 8 modules in a row, then under 7 modules in a row and under that 3 modules in a row, that would be 3 arrays. One array of 8, one of 7 and one array of 3. Each row of modules have a gap of .4" between each module. Now the rails only come in 3 sizes. 132", 168" and 204". Each array needs 2 rows of rails under it. One row of rail at the top of the array and one row of rail at the bottom of it that goes all the way through the array. We splice the rails together to make longer rails than the 204" rails. At the end of the array there's 1" of rail on both ends. So essentially we are trying to get a formula that can tell us what size rails we need and how many of each size. We need have the least amount of waste. If the spliced rails come out longer than the 1" extension after the array, then the rail is cut. The minimum amount of rail that we could use if it's cut is 36". That's if the rail extends 36" from the previous array, then it's cut and we can use that for the next array to splice it. The system can be laid out all in portrait, it can be all in landscape or part portrait and part landscape.

Now back to the formula. When the system is laid out in portrait, the parameters are this. 132" rails are for 3 panels, 168" rails are for 4 panels, and 204" rails are for 5 panels. Again, each array requires 2 rails. So for 3 panels, it requires 2 rails of 132". So back to the original example. If there are 21 panels, the best configuration for the rails are 4 rails of 168" and 1 rail of 204". Multiply that by 2 for the top and bottom rails. If there are more arrays, then it's the same situation. If there's an array of 7 and an array of 2, then the configuration would be rails of 204" and rails of 168". Total rails, 2 rails of 204" and 2 rails of 168", one for the top and one for bottom.

When the system is laid out in landscape, the parameters are this. The parameters are based on length of the array which is the amount of panels, plus the gaps, plus the 1" on each side of the array. Instead of the simple 132" is 3 panels and so on.

When the system is laid out in portrait and landscape the parameters are first to try to keep the parameters of the portrait layout, but incorporate the landscape parameters for the landscape panels.



Please help me create this formula. It's a complicated formula, but even if only the first part can be made it will be very helpful.


Thank you!

Hi,

1) Can you give a more precise description in your profile as to your Excel version? There are significant differences in functionality between some versions.

2) Can you attach a workbook with a few examples together with expected results? The yellow box at the top of this page tells you how to attach a workbook.

Regards

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however the thread title does not really convey what your request is about. Tell us what you are trying to do, not how you think it should be done.

Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

(Note: this change is not optional. As you are new, I shall do it for you this time.

Please look at the splash screen when you launch Excel and update your profile with the version yo are using. What you have in your profile at the moment really isn't of any use to us.

Mod edit says that the mod has made the title change - no need to delete your post.

@acristianban, your problem seems to be a kind of optimization problem.
My first idea about is the following.
Let x1 be a number of 204" rails, x2 of 168" rails, x3 of 132" rails.
You prefer longer rails to shorter ones. I don't know reasons - longer are cheaper per inch, you want avoid splicing operations - it doesn't matter now.
You want to join P panels in the potrait layout
The model is
minimize (x1+x2+x3)
subject to
5*x1+4*x2+3*x3=P
x1, x2, x3 >=0
x1, x2, x3 - integer

This model provides implicit preference for longer rails since the minimum will be achieved rather for a small number x1 than larger x2 or even more x3.

I took example 2 from your file - 32 panel in arrays: 8, 8, 7, 5, 3, 1.

I optimized it in Excel by using Solver add-in and obtained the following result:
4 * 204" + 3 * 168" + 0 * 132".
This is "automatical" part of the solution.
Next, I made some calculations "manually" (but of course in Excel). I show the solution without doubling number of rails per panel. I rounded inches of rail lengths per array up to integers.
Let's cut one rail 204"=116"+88" (a "new" cut), one rail 168"=121"+42"+5" ("old" cuts, the same as in your solution)
The assigment of rails to arrays is the following
8 2*168
8 1*204+1*116 splice with different share of rails
7 1*204+1*88 splice with different share of rails
5 1*204
3 1*121
1 1*42

Your solution was 1*204"+6*168"+1*132"
8 2*168
8 2*168
7 1*168 +1*132
5 1*204
3+1 1*168

My solution requires one more cut. Is it any better to be interesting to you? If so, please let me know.

Edited after remarks.

I'm sorry about confusing you but it was late evening in my place when I was typing the post (already edited). I was a little bit tired then and the words amout a new splice were a mistake, of course.
What makes a real difference between your and my result is that, according to my result, you can "cover" 8- and 7-pannel arrays considered together with 3 204" rails (what requires cutting of one rail and splicing the resulting pieces to each of two full-length rails). Or, you can use 3 168" rails and 1 132" rail to "cover" arrays without cutting any of them (2 168" rails for 8-pannel array and 168"+132" for 7-pannel array).
If you prefer using longer rails, you should be satisified. If you also want to avoid cutting, not quite.