Using binary variables in the Excel solver without making the problem unsolvable

Sam Hamels

New Member
Joined
Mar 20, 2018
Messages
49
This is a bit of an unusual post, aimed at people familiar with optimization problems solved by the Excel solver (or Add-ins like OpenSolver)

In the attached Excel file, I simulate the following simple scenario:


  • A house consumes electricity from the grid for 24 hours.

  • The amount of electricity taken from the grid is shown for every hour (it fluctuates throughout the day)

  • The price of electricity is also shown for every hour (it also fluctuates throughout the day)

  • The amount paid for electricity is (for each hour) the amount consumed multiplied by the price in that hour

  • The house has a battery that is used to change the amount of electricity that is taken from the grid during each hour

  • The point of this is to decrease the amount of electricity taken from the grid during hours with a high electricity price, and to increase the amount taken from the grid during hours with a low electricity price

  • This way, the total amount paid for electricity can be reduced

  • This is an optimization, solved by the basic Excel solver (although I could also use the free Add-in called 'OpenSolver')

  • The optimization minimizes the total amount paid, *after* the battery has done its thing.

  • Some constraints must me obeyed within the optimization, to be realistic:


  1. The battery cannot be charged beyond its maximum energy storage capacity
  2. The amount of energy stored in the battery cannot be lower than zero
  3. The battery cannot be (dis)charged faster than its maximum (dis)charge rate
  4. After the optimization is completed (in other words, "when the battery has done its thing"), the total amount of electricity taken from the grid throughout the day cannot be changed
  5. The 'new' amount of electricity taken from the grid during each hour cannot be negative

The problem I have is that I need to add the following detail to the simulation, but I don't know how:

The charging and discharing of the battery causes some 'losses' of electricity. For example, if the battery consumes 10 units of electricity, only 9 actually end up in the battery. Likewise, when the battery delivers 10 units of electricity to the home, 11 units actually go out of the battery. In this example, both the charge efficiency and the discharge efficiency are 90%.

I have tried playing around with binary variables to disconnect charging from discharging, and apply the losses to the battery charging and discharging, but I can't manage to make anything work. It always makes my problem unsolvable.

Intuitively, I basically want this to be to formula of the column with the 'energy stored inside the battery':


  • IF the change in electricity taken from the grid is positive, then multiply that change by the battery charging efficiency to arrive at the amount actually stored in the battery

  • IF the change in electricity taken from the grid is negative, then multiply that change by the battery discharging efficiency to arrive at the amount actually going out of the battery

The problem is that I can't use an IF function because the solver doesn't allow for that. Hence my experimentation with binary variables (without success).

Any advice would be extremely welcome.

Excel file: http://s000.tinyupload.com/index.php?file_id=03390715579981265136
 
Last edited:

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
Interesting problem. I'd have thought software would exist for this by people that make household batteries, like Tesla.

I think a solution needs to consider several days, so it can stabiiize; starting with an empty battery at peak electricity cost isn't going to work well for a single day.
 
Upvote 0
In reality I have many hundreds of days of data (home electricity use and electricity prices). The single day in the file is just a sample.

I could easily have the simulation run over periods of two or more days instead of one (this is arbitrary). It would just take longer to solve.

The core problem would remain that I can't find a way to include battery losses without making the problem unsolvable. It seems unlikely to me that it really is impossible to do though. I must just be looking over some obvious way to do it.
 
Upvote 0
I think that's straightforward:

[Table="width:, class:grid"][tr][td="bgcolor:#C0C0C0"][/td][td="bgcolor:#C0C0C0"]
A​
[/td][td="bgcolor:#C0C0C0"]
B​
[/td][td="bgcolor:#C0C0C0"]
C​
[/td][td="bgcolor:#C0C0C0"]
D​
[/td][td="bgcolor:#C0C0C0"]
E​
[/td][td="bgcolor:#C0C0C0"]
F​
[/td][td="bgcolor:#C0C0C0"]
I​
[/td][/tr][tr][td="bgcolor:#C0C0C0"]
1​
[/td][td][/td][td][/td][td][/td][td][/td][td="bgcolor:#F3F3F3"]
Chg Eff
[/td][td]
90%​
[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
2​
[/td][td][/td][td][/td][td][/td][td][/td][td="bgcolor:#F3F3F3"]
Disch Eff
[/td][td]
90%​
[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
3​
[/td][td][/td][td][/td][td][/td][td][/td][td="bgcolor:#F3F3F3"]
Max Sorage
[/td][td]
50​
[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
4​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
5​
[/td][td="bgcolor:#F3F3F3"]
Hour
[/td][td="bgcolor:#F3F3F3"]
Demand
[/td][td="bgcolor:#F3F3F3"]
Unit Cost
[/td][td="bgcolor:#F3F3F3"]
Cost
[/td][td="bgcolor:#F3F3F3"]
(Dis)Charge
[/td][td="bgcolor:#F3F3F3"]
Storage
[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
6​
[/td][td]
7​
[/td][td]
3​
[/td][td]
128​
[/td][td]
384​
[/td][td]
10.0​
[/td][td="bgcolor:#CCFFCC"]
9.0​
[/td][td="bgcolor:#CCFFCC"]F6: =IF(E6 > 0, MIN(F$3, SUM(F5, E6 * F$1)), MAX(0, SUM(F5, E6 / F$2)))[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
7​
[/td][td]
8​
[/td][td]
4​
[/td][td]
130​
[/td][td]
520​
[/td][td]
10.0​
[/td][td="bgcolor:#CCFFCC"]
18.0​
[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
8​
[/td][td]
9​
[/td][td]
4​
[/td][td]
136​
[/td][td]
544​
[/td][td]
10.0​
[/td][td="bgcolor:#CCFFCC"]
27.0​
[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
9​
[/td][td]
10​
[/td][td]
4​
[/td][td]
142​
[/td][td]
568​
[/td][td]
10.0​
[/td][td="bgcolor:#CCFFCC"]
36.0​
[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
10​
[/td][td]
11​
[/td][td]
3​
[/td][td]
139​
[/td][td]
417​
[/td][td]
10.0​
[/td][td="bgcolor:#CCFFCC"]
45.0​
[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
11​
[/td][td]
12​
[/td][td]
3​
[/td][td]
136​
[/td][td]
408​
[/td][td]
-3.0​
[/td][td="bgcolor:#CCFFCC"]
41.7​
[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
12​
[/td][td]
13​
[/td][td]
3​
[/td][td]
137​
[/td][td]
411​
[/td][td]
-3.0​
[/td][td="bgcolor:#CCFFCC"]
38.3​
[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
13​
[/td][td]
14​
[/td][td]
3​
[/td][td]
134​
[/td][td]
402​
[/td][td]
-3.0​
[/td][td="bgcolor:#CCFFCC"]
35.0​
[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
14​
[/td][td]
15​
[/td][td]
3​
[/td][td]
127​
[/td][td]
381​
[/td][td]
10.0​
[/td][td="bgcolor:#CCFFCC"]
44.0​
[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
15​
[/td][td]
16​
[/td][td]
3​
[/td][td]
127​
[/td][td]
381​
[/td][td]
10.0​
[/td][td="bgcolor:#CCFFCC"]
50.0​
[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
16​
[/td][td]
17​
[/td][td]
4​
[/td][td]
122​
[/td][td]
488​
[/td][td]
10.0​
[/td][td="bgcolor:#CCFFCC"]
50.0​
[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
17​
[/td][td]
18​
[/td][td]
5​
[/td][td]
125​
[/td][td]
625​
[/td][td]
10.0​
[/td][td="bgcolor:#CCFFCC"]
50.0​
[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
18​
[/td][td]
19​
[/td][td]
3​
[/td][td]
130​
[/td][td]
390​
[/td][td]
9.0​
[/td][td="bgcolor:#CCFFCC"]
50.0​
[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
19​
[/td][td]
20​
[/td][td]
9​
[/td][td]
132​
[/td][td]
1188​
[/td][td]
-9.0​
[/td][td="bgcolor:#CCFFCC"]
40.0​
[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
20​
[/td][td]
21​
[/td][td]
10​
[/td][td]
136​
[/td][td]
1360​
[/td][td]
-10.0​
[/td][td="bgcolor:#CCFFCC"]
28.9​
[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
21​
[/td][td]
22​
[/td][td]
16​
[/td][td]
143​
[/td][td]
2288​
[/td][td]
-10.0​
[/td][td="bgcolor:#CCFFCC"]
17.8​
[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
22​
[/td][td]
23​
[/td][td]
23​
[/td][td]
173​
[/td][td]
3979​
[/td][td]
-10.0​
[/td][td="bgcolor:#CCFFCC"]
6.7​
[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
23​
[/td][td]
24​
[/td][td]
19​
[/td][td]
169​
[/td][td]
3211​
[/td][td]
-10.0​
[/td][td="bgcolor:#CCFFCC"]
0.0​
[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
24​
[/td][td]
1​
[/td][td]
13​
[/td][td]
159​
[/td][td]
2067​
[/td][td]
0.0​
[/td][td="bgcolor:#CCFFCC"]
0.0​
[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
25​
[/td][td]
2​
[/td][td]
1​
[/td][td]
158​
[/td][td]
158​
[/td][td]
6.0​
[/td][td="bgcolor:#CCFFCC"]
5.4​
[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
26​
[/td][td]
3​
[/td][td]
2​
[/td][td]
159​
[/td][td]
318​
[/td][td]
-2.0​
[/td][td="bgcolor:#CCFFCC"]
3.2​
[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
27​
[/td][td]
4​
[/td][td]
1​
[/td][td]
159​
[/td][td]
159​
[/td][td]
-1.0​
[/td][td="bgcolor:#CCFFCC"]
2.1​
[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
28​
[/td][td]
5​
[/td][td]
1​
[/td][td]
163​
[/td][td]
163​
[/td][td]
-1.0​
[/td][td="bgcolor:#CCFFCC"]
1.0​
[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
29​
[/td][td]
6​
[/td][td]
2​
[/td][td]
159​
[/td][td]
318​
[/td][td]
-2.0​
[/td][td="bgcolor:#CCFFCC"]
0.0​
[/td][td][/td][/tr]
[/table]
 
Upvote 0
Thanks!

I eventually figured out that I could quite easily include battery losses without using any IF-formula (or MIN or MAX for that matter),
but I simply had to relax the constraint that the 'old' and 'new' electricity consumption (i.e. before and after the battery has done its thing) should always be equal.
They can no longer be equal since the losses of the battery requires a slight increase in the overall electricity consumption.
 
Upvote 0

Forum statistics

Threads
1,223,884
Messages
6,175,174
Members
452,615
Latest member
bogeys2birdies

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top