Maximum Total Optimization - VBA - Need Help

canernas · Mar 18, 2025

Dear MrExcel Family,

I am working on a project. The goal of this project is to minimize the SUM formulas in the column totals located in row 14 of the table found in the attached Excel file. Rows 3 to 12 specify the total demand for each 5-minute interval, cell by cell. To minimize the column totals in row 14, the numbers in the rows need to be shifted. By shifting these numbers, I will obtain the most optimal subtotals. There are some constraints while performing this operation:

The numbers to be shifted can only be moved as groups. (For example, a group of numbers written as 3 - 3 - 3 in one row must remain 3 - 3 - 3 when moved.)
The shifts should only occur between the blue cells and should not go outside the blue cells.
The number group in one row cannot be moved to a different row; the shifting must occur within the same row.

I have been trying to implement this optimization using VBA code for days, but I couldn't find a solution. I would appreciate your support, as I really haven't been able to find a way out!

RoryA · Mar 18, 2025

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at:

Maximum Total Optimization - VBA - Need Help

Dear Chandoo Family, I am working on a project. The goal of this project is to minimize the SUM formulas in the column totals located in row 14 of the table found in the attached Excel file. Rows 3 to 12 specify the total demand for each 5-minute interval, cell by cell. To minimize the column...

chandoo.org

There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

StephenCrump · Mar 18, 2025

Welcome to the Forum!

canernas said:
The goal of this project is to minimize the SUM formulas ...

Not sure what you mean here, as the total sum won't vary if you slide the row values left or right.

I suspect you want column totals to be as uniform as possible, i.e. to solve for minimum column total variance, or minimum difference between min and max column totals?

What exactly is a group? Does each row have one group, so that in your row 9, for example, there are only four possibilities:



2	2	2	1	1
	2	2	2	1	1
		2	2	2	1	1
			2	2	2	1	1

canernas · Mar 19, 2025

StephenCrump said:
Welcome to the Forum!

Not sure what you mean here, as the total sum won't vary if you slide the row values left or right.

I suspect you want column totals to be as uniform as possible, i.e. to solve for minimum column total variance, or minimum difference between min and max column totals?

What exactly is a group? Does each row have one group, so that in your row 9, for example, there are only four possibilities:

2 2 2 1 1
2 2 2 1 1
2 2 2 1 1
2 2 2 1 1

Hello Stephen,

Firstly, thank you for your response.

Yes I am trying to reduce the column totals as much as I can to optimize the solution. But the main focus is to have the minimum of column totals because everytime we move a number between blue cells, some columns totals will change. By chaning number positions, we will be trying to find the minimum number.

By group I mean the number of cells together in a single row. For example, as your screenshot, the 2-2-2-1-1 is one number group. and we can only move this number of group between the blue cells.

2

1

I hope I was able to answer your questions .

Thanks again

StephenCrump · Mar 19, 2025

canernas said:
By chaning number positions, we will be trying to find the minimum number ....

I think you're saying you don't want large numbers in any column, as this will mean small numbers in other columns, and hence too much variability in the column totals.

Try this:

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

W

X

Y

Z

AA

AB

AC

AD

AE

AF

AG

AH

AI

AJ

AK

AL

AM

AN

AO

AP

AQ

AR

AS

AT

AU

1

Grp

Vals

Len

Min

Max

Select

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

2

1

3

5

1

11

1

3

2

3

7

10

8

2

4

3

2

4

18

25

18

2

5

4

3

5

11

18

11

3

6

5

3

1

7

6

11

7

3

1

7

6

3

5

2

12

3

8

7

2

1

5

12

2

1

9

8

1

2

24

27

1

10

9

2

1

5

21

24

22

2

1

11

10

3

13

7

16

3

12

13

Total

14

15

Solve1

3

16

Solve2

0.97

Sheet1

Cell Formulas
Range	Formula
A2:A11	A2	=SEQUENCE(ROWS(B2:B11))
O2:O11	O2	=COUNT(B2:N2)
T1:AU1	T1	=SEQUENCE(,28)
T2:AU11	T2	=LET(n,T$1#-R2+1,IF((n>0)*(n<=O2),INDEX(B2:N2,n),""))
T13:AU13	T13	=SUM(T2:T11)
T15	T15	=MAX(T13:AU13)-MIN(T13:AU13)
T16	T16	=VAR(T13:AU13)
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
solver_adj	=Sheet1!$R$2:$R$11	T2
solver_lhs1	=Sheet1!$R$2	T2
solver_lhs10	=Sheet1!$R$5	T5
solver_lhs11	=Sheet1!$R$5	T5
solver_lhs12	=Sheet1!$R$5	T5
solver_lhs13	=Sheet1!$R$6	T6
solver_lhs14	=Sheet1!$R$6	T6
solver_lhs15	=Sheet1!$R$6	T6
solver_lhs16	=Sheet1!$R$7	T7
solver_lhs17	=Sheet1!$R$7	T7
solver_lhs18	=Sheet1!$R$7	T7
solver_lhs19	=Sheet1!$R$8	T8
solver_lhs2	=Sheet1!$R$2	T2
solver_lhs20	=Sheet1!$R$8	T8
solver_lhs21	=Sheet1!$R$8	T8
solver_lhs22	=Sheet1!$R$9	T9
solver_lhs23	=Sheet1!$R$9	T9
solver_lhs24	=Sheet1!$R$9	T9
solver_lhs25	=Sheet1!$R$10	T10
solver_lhs26	=Sheet1!$R$10	T10
solver_lhs27	=Sheet1!$R$10	T10
solver_lhs28	=Sheet1!$R$11	T11
solver_lhs29	=Sheet1!$R$11	T11
solver_lhs3	=Sheet1!$R$2	T2
solver_lhs30	=Sheet1!$R$11	T11
solver_lhs4	=Sheet1!$R$3	T3
solver_lhs5	=Sheet1!$R$3	T3
solver_lhs6	=Sheet1!$R$3	T3
solver_lhs7	=Sheet1!$R$4	T4
solver_lhs8	=Sheet1!$R$4	T4
solver_lhs9	=Sheet1!$R$4	T4

I used code to set up Solver more quickly, i.e. rather than adding 30 constraints manually.

VBA Code:

Sub Optimise()

    Dim i As Long, RunTime As Long, SolveOption As Long
   
    SolveOption = 2
    RunTime = 10 'say
   
    SolverReset
    SolverOk SetCell:=IIf(SolveOption = 1, "T15", "T16"), MaxMinVal:=2, ValueOf:=0, ByChange:="R2:R11", Engine:=3, EngineDesc:="Evolutionary"
    SolverOptions MaxTime:=0, Iterations:=0, Precision:=0.000001, Convergence:=0.0001, StepThru:=False, Scaling:=True, AssumeNonNeg:=True, Derivatives:=1
    SolverOptions PopulationSize:=100, RandomSeed:=0, MutationRate:=0.075, Multistart _
        :=False, RequireBounds:=True, MaxSubproblems:=0, MaxIntegerSols:=0, _
        IntTolerance:=1, SolveWithout:=False, MaxTimeNoImp:=RunTime
       
    For i = 2 To 11
        SolverAdd CellRef:="R" & i, Relation:=4, FormulaText:="integer"
        SolverAdd CellRef:="R" & i, Relation:=1, FormulaText:="Q" & i
        SolverAdd CellRef:="R" & i, Relation:=3, FormulaText:="P" & i
    Next i
       
    SolverSolve

End Sub

The results in R2:R11 are one of several similar solutions when SolveOption = 2, i.e. minimising the variance of the column totals.

Maximum Total Optimization - VBA - Need Help

canernas

New Member

Attachments

RoryA

MrExcel MVP, Moderator

Maximum Total Optimization - VBA - Need Help

StephenCrump

MrExcel MVP

canernas

New Member

StephenCrump

MrExcel MVP

Similar threads

Share this page

Maximum Total Optimization - VBA - Need Help

canernas

New Member

Attachments

RoryA

MrExcel MVP, Moderator

Maximum Total Optimization - VBA - Need Help

StephenCrump

MrExcel MVP

canernas

New Member

StephenCrump

MrExcel MVP

Similar threads

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock