Minimise the total number of inverters to be used for different electricity load factors

Assume there are two types of inverters – Inverter1 with a capacity of 15kW and Inverter2 with a capacity of 20kW.  For an electricity load factor of, say 60kW, the objective is to determine the minimum number of inverters to be used.  Therefore the number of inverters which can be used for a load factor of 60kW are:

Option1: All 4 of 15kW; or
Option2: All 3 of 20kW

Since the objective is to mimimise the number of inverts to be used, the result should be all 3 of 20kW.  To take another example, for an electricity load of, say 100kW, the number of inverters which can be used are:

Option1: All 5 of 20kW; or
Option2: 2 of 20kW and 4 of 15kW

The result should be Option1 because the number of total inverters used is minimised there i.e. 5 instead of 6 in Option2.

To acheive this via Excel formulas, try this

1. Enter 15 in cell C2 and 20 in cell C3
2. In range A8:A18, enter different load factors such as 45,50,60,100,75,220,240,90,120,160,275
3. Enter 15 in cell B7 and 20 in cell C7
4. In cell B8, enter the following array formula (Ctrl+Shift+Enter) and copy down till cell B18

=SUMPRODUCT(((INDEX(((({0,1,2,3,4,5,6,7,8,9,10})*$C$2)+(({0;1;2;3;4;5;6;7;8;9;10})*$C$3)=$A8)*({0,1,2,3,4,5,6,7,8,9,10}),,)+INDEX(((({0,1,2,3,4,5,6,7,8,9,10})*$C$2)+(({0;1;2;3;4;5;6;7;8;9;10})*$C$3)=$A8)*({0;1;2;3;4;5;6;7;8;9;10}),,))=MIN(IF((INDEX(((({0,1,2,3,4,5,6,7,8,9,10})*$C$2)+(({0;1;2;3;4;5;6;7;8;9;10})*$C$3)=$A8)*({0,1,2,3,4,5,6,7,8,9,10}),,)+INDEX(((({0,1,2,3,4,5,6,7,8,9,10})*$C$2)+(({0;1;2;3;4;5;6;7;8;9;10})*$C$3)=$A8)*({0;1;2;3;4;5;6;7;8;9;10}),,))>0,(INDEX(((({0,1,2,3,4,5,6,7,8,9,10})*$C$2)+(({0;1;2;3;4;5;6;7;8;9;10})*$C$3)=$A8)*({0,1,2,3,4,5,6,7,8,9,10}),,)+INDEX(((({0,1,2,3,4,5,6,7,8,9,10})*$C$2)+(({0;1;2;3;4;5;6;7;8;9;10})*$C$3)=$A8)*({0;1;2;3;4;5;6;7;8;9;10}),,)))))*({0,1,2,3,4,5,6,7,8,9,10}))

5. In cell C8, enter the following array formula (Ctrl+Shift+Enter) and copy down till cell C18

=SUMPRODUCT(((INDEX(((({0,1,2,3,4,5,6,7,8,9,10})*$C$2)+(({0;1;2;3;4;5;6;7;8;9;10})*$C$3)=$A8)*({0,1,2,3,4,5,6,7,8,9,10}),,)+INDEX(((({0,1,2,3,4,5,6,7,8,9,10})*$C$2)+(({0;1;2;3;4;5;6;7;8;9;10})*$C$3)=$A8)*({0;1;2;3;4;5;6;7;8;9;10}),,))=MIN(IF((INDEX(((({0,1,2,3,4,5,6,7,8,9,10})*$C$2)+(({0;1;2;3;4;5;6;7;8;9;10})*$C$3)=$A8)*({0,1,2,3,4,5,6,7,8,9,10}),,)+INDEX(((({0,1,2,3,4,5,6,7,8,9,10})*$C$2)+(({0;1;2;3;4;5;6;7;8;9;10})*$C$3)=$A8)*({0;1;2;3;4;5;6;7;8;9;10}),,))>0,(INDEX(((({0,1,2,3,4,5,6,7,8,9,10})*$C$2)+(({0;1;2;3;4;5;6;7;8;9;10})*$C$3)=$A8)*({0,1,2,3,4,5,6,7,8,9,10}),,)+INDEX(((({0,1,2,3,4,5,6,7,8,9,10})*$C$2)+(({0;1;2;3;4;5;6;7;8;9;10})*$C$3)=$A8)*({0;1;2;3;4;5;6;7;8;9;10}),,)))))*({0;1;2;3;4;5;6;7;8;9;10}))

For each load factor, the combination of inverters of each capacity will be the minimum number of inverters (of all possible combinations) that can be used.

The assumption in both formulas above is that no more than 10 inverters of each inverter type will ever be used i.e. no more than 10 inverters of 15kW and no more than 10 inverters of 20kW will be used.  This assumption can obviously be relaxed by making minor changes to both formulas.