solver binary variables

[methody]

Hi there I am using solver to address a problem and basically it solves the problem i want it to solve.

Within the problem there are 6 columns and I am using sum at the bottom of each column. I want solver to indicate how many times those columns add up to 5. I was think of adding in binary values below each total so that a 1 would appear below the total which sum to 5. But I can't get it to work. I know it could be done simply with an if formula but that isn't possible.
Sorry could be clearer but any help appreciated.

 

[BSALV]

what kind of problem are you dealing with ?
I don't understand the picture.

 

[methody]

Hi sorry about that.
I'm using binary variables to create a schedule for teachers in school. There are 6 teacher names across the top and there are 8 periods down the left hand side. I have include constraints about the total for each teacher. That is all fine. So below each teacher there might be 1 1 1 0 1 1 0 1.
I am interested in the number of times a teacher teaches 3 periods in a row. I create a range of cells below which sums 3 cells. So in the example above there would be one case where the sum is 3.
I could use a countif to count the number of times teacher teaches 3 in a row but if a want to make that part of the model and try to the minimise that number solver wont let me as it doesn't like countifs

That's why i was trying to think of another way of effectively counting the number of cells which have 3 in them. I wondered if there would be a way of using an additional range of binary variables to effectively generate a 1 for every time that range had a 3 and a 0 everywhere else.

Probably still not very helpful

But it is effectively a question about any range of cells with a formula and is there a way of counting the number of cells with a particular value without using countif.

 

[BSALV]

what excel-version are you using ? 365, 2021 or older ?
how much is 11111010 ?

 

[methody]

Hi
I'm on Excel 365
Yes good question. That would yield 3 instances of 3 consecutive periods. I would hve envisaged that showing 3 '1's as binary variables

 

[BSALV]

methody.xlsx
something like this ?

methody.xlsx
	A	B	C	D	E	F	G	H	I	J
1			3000	4000	5000	6000	7000	8000
2			teacher1	teacher2	teacher3	teacher4	teacher5	teacher6
3	30	period1	1	0	0	0	0	1	2
4	40	period2	0	1	0	0	0	1	2
5	50	period3	0	0	1	1	1	1	4
6	60	period4	0	0	1	1	1	1	4
7	70	period5	0	0	1	1	1	1	4
8	80	period6	0	1	1	1	1	1	5
9	90	period7	1	1	1	1	1	0	5
10	100	period8	1	1	1	1	1	0	5
11			10000011	01000111	00111111	00111111	00111111	11111100
12			0	1	4	4	4	3
13		min	3	3	3	3	3	3
14		actual	3	4	6	6	6	6	31	31
15		max	6	6	6	6	6	6
16
17		goal	220	4.310	20.450	24.450	28.450	24.330	102.210
Blad1

Cell Formulas
Range	Formula
C1:H1	C1	=COLUMN()*1000
A3:A10	A3	=ROW()*10
I3:I10,I17,I14	I3	=SUM(C3:H3)
C11:H11	C11	=TEXTJOIN(,,C3:C10)
C12:H12	C12	=SUM(--(MID(C11,ROW($B$2:$B$7),3)="111"))
C14:H14	C14	=SUM(C3:C10)
C17:H17	C17	=+SUMPRODUCT($A$3:$A$10,C3:C10)+C1*C12

Named Ranges
Name	Refers To	Cells
solver_adj	=Blad1!$C$3:$H$10	I3, C17, C14, C11
solver_lhs2	=Blad1!$C$14:$H$14	I14
solver_lhs3	=Blad1!$C$3:$H$10	I3, C17, C14, C11
solver_rhs1	=Blad1!$C$14:$H$14	I14

 

[methody]

Thanks BSALV
That's very clever and does what I asked but solver doesn't like TEXTJOIN when I ask it to solve a problem with it involved.
I'm not sure there's a way to do it as i think it has to use only pretty simple formulas. That's why I thought it might need to involve the use of creation of other binary variables.
thanks again

 

[BSALV]

i didn't use the simplex method, instead the evolutionary, but then you need to add more constraints.