Finding the lowest whole number that allows other cells to meet < conditions

[Grootka]

Basically I have a series of data that is dependent on what number is in this cell.
Right now I enter a whole number until I see that two other cells in the series meet < conditions
I was trying to write a formula that would determine what that integer is without me just guessing until it is right.

[MrShorty]

Not much to go on. In order to give good, specific suggestions, we probably need to know something about how all of the cells are interrelated.

As a reasonable guess, have you tried Excel's built in Solver utility? If the spreadsheet is well designed for it, Solver might able to find the solution with its algorithms. https://support.office.com/en-us/art...n-US&ad=US#bm8

[Grootka]

I honest don't see how it changes much but:
A1= a whole number x that makes (B3<= y and C3:R3<=z)
B3:R3=u-(r+A1)

[MrShorty]

It might make a huge difference. If that really is as simple as the problem is, simple algebra should come very close to solving.

Looking at B3, for example:

B3=u-(r+A1) B3<=y so u-(r+A1)<=y, solve the inequality for A1 (algebra tutorial if you need to remember how to solve inequalities: https://www.purplemath.com/modules/ineqlin.htm ).
If I did it right in my head, the result should be A1>=u-r-y With the different values for u, r, and y, I can fairly easily calculate the smallest integer value for A that will meet the B3 criteria. C3:R3 is similar, so it should just be a matter of calculating the appropriate possible values for A1 and choosing the correct value.

[Grootka]

I would still get the notice that B3 is dependent on A1 to solve. And i over simplified it a little for the sake of this.

A1= a whole number x that makes (B4<= y and C4:R4<=z)
J1= 110-A1
B1=J1-B2 C1=J1-C2 etc for B1:I1 and K1:R1
B2=b C2=c D2=d... etc (B2:R2 is the data I collected)
B3=s C3=t D3=u... etc (B3:R3 are fixed)
B4=B2-(B1+A1) C4=C2-(C1+A1)... etc

How do I make the equation for A1? The conditions make it more than a simple "Solve for X" equation. Right now I change A1 until B4 and C4:R4 meet the conditions.

[MrShorty]

Something seems off, because, if I did my algebra correctly, A1 will not change B4 (or any of the other values in row 4).

B4=B3-(B1+A1)
B1=J1-B2 -> substitute into above
B4=B3-(J1-B2+A1)
J1=110-A1 substitute into above
B4=B3-(110-A1-B2+A1) -A1+A1 is 0, so A1 drops out of the equation leaving:
B4=B3-110+B2 --> Obviously you've been doing this manually, so A1 must be changing the values in row 4 somehow, but I don't see how A1 carries through to the results in row 4. But, none of the algebra seems more complex than what was described before, so I am still inclined towards the same approach -- solve the inequality to find the value(s) for A1 that will satisfy the desired inequality.

As for the programming part, if I assume that J1 is backwards so that A1 will carry through to row 4. I get, for B4 B3-2*$A$1+110+B2
B4 should be less than or equal to y (whatever y is and whereever you are storing it), so:

B3-2*$A$1+110+B2<=y
-2*$A$1<=y-B3-110-B2
$A$1>=(y-B3-110-B2)/-2 rounded up to the nearest integer.

With that done, in B5, I would enter that formula for A1 =ROUNDUP((y-B3-110-B2)/-2),0) where y is a reference to the cell containing y. Copy into C5 and replace the reference for y with the reference for z and finish copying across. Row 5 now has several possible choices for A1 (that fit my modified calculation scheme). If I choose the smallest value in row 5 (=MIN(B5:R5)) then I will get the value that meets only one of the criteria. If I choose the largest value in row 5 (=MAX(B5:R5)) then I will get the value that causes all values to meet the criteria. I could use the SMALL() or LARGE() functions to get anything in between (the value that meets 2 or 5 or 10 criteria). In this approach, A1 can end up being a suitable SMALL() or LARGE() function.

Unless there is something more complicated to the algebra, I still see it as a "spend some time with the algebra, then solve the inequality, then enter the appropriate sequence of calculations in the spreadsheet" kind of problem.

[Grootka]

That was on me, I made an error transposing numbers in my head instead of just pasting it on a new page

A1= a whole number x that makes (A2<= y and B4:R4<=z)
J1= 110-A1
B1=J1-B2 C1=J1-C2 etc for B1:I1 and K1:R1
B2=b C2=c D2=d... etc (B2:R2 is the data I collected)
B3=s C3=t D3=u... etc (B3:R3 are fixed)
B4=B2-(B3+A1) C4=C2-(C3+A1)... etc
A2=SUMIF(B4:R4,">0")

Sorry about that. You're right all the math is very simple especially compared to the 6 line equations I had to use to compile the numbers for B2:R2, but the Excel code for making it find the lowest number that meets the qualifications is not.
I know A1= a whole number between 1 and 110
Do I have to make a new Sheet2 that solves for all 110 possibilities and somehow program the Sheet1 to show the one I want?

[Grootka]

Sorry messed up again, i updated it if you're already trying to help me. I know I'm being a pain.

[MrShorty]

Have you tried Solver, yet? As near as I can tell, the problem should be solvable algebraically, and we can continue to explore that if you want, but the easiest solution, now that I understand the problem might be to use Solver. Call Solver and tell it to:

Set target cell: A2
To a Maximum
By changing: A1
Subject to Constraints:
A2<=y
B4:R4<=z
A1 is integer

At this point, I might try that to see if Solver can reliably find a solution.

[Grootka]

Thanks, it really looks like this should do it.

Unfortunately for some reason I can't get Solver to actually change the answer. No matter what I do It won't vary A1. Even if I leave it blank.

[AliGW]

Will you please attach a sample Excel workbook? We are not able to work with or manipulate a picture of one and nobody wants to have to recreate your data from scratch.

1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired results are also shown (mock up the results manually).

3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

4. Try to avoid using merged cells as they cause lots of problems.

Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.

[MrShorty]

I agree with AliGW -- I have no idea what typical values are in rows 2 and 3, so it is difficult to visualize the problem.

To revisit the algebra solution. It appears that we have two conditions on the row 4 values that we are testing: Condition 1 Row 4<=z and condition 2 row 4 >0, or row 4 is between 0 and z. (The A2 condition says that we only want y columns in row 4 to be >0, but we will deal with that later)
Solving each inequality for A1, I get Condition 1 A1>=row2-row3-z Condition 2 A1>row2-row3 or A1 is between row2-row3-z and row2-row3 (check my algebra here and make sure I got it right).
If I enter these formulas into rows 5 and 6 (B5=B2-B3 and B6=B5-z and copy across), I will get two rows of values. Row 5 represents the maximum value for A1 that will meet that column's criteria 2 and row 6 will be the minimum value for A1 that will meet that column's criteria 1.
But, I want only y values from row 4 to be greater than 0. This is where representative values would help, but, if I have thought it through correctly, that means that I want the yth smallest value from row 5 A1=SMALL(B5:R5,2) or maybe the y+1 smallest. Even if I misunderstood something, a programming sequence like that is what I would expect for the problem described in post #7. Determine the valid range for each column based on the two criteria, then determine which value from all of the valid values for each column will actually meet the A2<y criteria.