Need cell to perform 'Loop until' calculation and then set final value to a cell

[ck4242]

Scenario looks like this:
3 cells involved:

A1 - Formula - Percentage value based on the SUM of three other % value cells (A1 does a simple SUM of three other cell % values)
B1 - Formula - Integer value based on simple arithmetic calculation of another cell's value (Looks at another cell's Integer value and does *2)
C1 - User input Integer - Percentage value - Statically defined by user

I need to compare A1's percentage against C1 percentage value (compare the statically defined % value against a resultant % value). The goal is for the value of B1 to be increased by 1 until A1 is less than or equal to C1. If the resultant A1 % value is GREATER than static C1 % value, then ADD 1 to B1 (I can do this much so far)....

But then I need a way to for the A1 vs C1 comparison to keep happening...and continue adding 1 to B1's value until A1 is no longer greater than C1.

What I have so far:
=IF(A1>B1,B1+1,B1) No idea how to put this into a loop UNTIL condition though.

I want this to work w/o any extra user input. Initially, the user will input certain values for things that are used to make calculations within the spreadsheet.
I know about the Solver, but this appears to be something that brings up a new window.


Is this achievable w/o using VBA or macros?

 

[bs0d]

Have you looked into iterative calculations in Excel? You must enable in the options (not enabled by default). Otherwise you get circular reference errors.

 

[ck4242]

Apparently I have....the option was enabled, so I must have run across something at some point that led me to it.

So, based on this, I have the simple statement:
=IF(E29+E30+E31>B17,B31+1)


What I'm doing here is running the test:
If the final % value (B29:B31) is greater than the desired % value B17), then increment a cell value (B31) (which when incremented will cause the final % value to drop. It should keep adding 1 to B31 until the final % value is lower than the desired.

When I use this on a cell though, it appears to just run once. The value is incremented by 1....but only adding 1 isn't enough, so I know then that the statement is not being evaluated multiple times.

 

[bs0d]

What if, in the formula logic, instead of +1, you increment by the difference necessary to make them equal?

 

[ck4242]

Not really possible.
B31 is an integer value
The other two cells are % values that are being compared to each other. One is user-defined, the other is the result of a calculation.
If B31 is increased in value, it causes the % value of (E29:E31) to go down....until it eventually is <= to B17 (user defined % value)

Playing around with this, I don't think that the iterative functionality will even work here....

 

[bs0d]

Ok. Any time I've needed to iterate, I use VBA or solver. I was thinking the iterative functionality might work here, but haven't played much with it. My default iteration max was pre-defined to a very low number. Did you make sure the common increment value necessary is within the bounds defined in the iteration options setting in Excel?

 

[ck4242]

Yes, I did modify the max parameter as needed

 

[ck4242]

So thinking VBA may be only way of doing this.
here's what I have so far (never done this

Sub FindOptimized_APs()

    Dim MaxSat As Integer
    Dim TotSat As Integer
    
    MaxSat = Range("B17").Value
    CalcSat = Range("E32").Value
    APs = Range("B31").Value
    OptimizedAPs = Range("B33").Value
    OptimizedAPs = APs.Value
    ActiveSheet.EnableCalculation = True

    Do Until CalcSat <= MaxSat
        APs = APs + 1
        Range("B31").Value = APs.Value
    Loop
End Sub

So what I'm trying to do again:
If E32 value is greater than B17, increase value of B31 by 1 and then re-evaluate. Once it's less than or equal, stop.

I've declared my variables, setup the loop.
The "ActiveSheet.EnableCalculation = True" is because I think I need this so that within the loop, after the +1 is performed, I need the sheet to refresh so that the updated E32 value is generated.

Sanity check? Couldn't find what the syntax is for assigning value of one variable to another variable.

 

[bs0d]

I'm trying to figure out how incrementing Cell B31 (APs, or "B1") decreases E32 (CalcSat, or "A1")? Then it will be easier for me to test a solution.

Thanks,

 

[bs0d]

Here's some code you can play around with and replace with some of the actual formulas you're working with that i'm unaware of. For instance, i subtracted to get a reduction in CalcSat.

Private Sub Worksheet_Change(ByVal Target As Range)
    'Macro triggered if user changes Max Sat Value:
    If Target = Range("B17") Then
        'Is Calc Sat > Max Sat?
        If Range("E32").Value > Range("B17").Value Then  
            x = 1
            Do Until Range("E32").Value <= Range("B17").Value
            Cells(31, 2).Formula = "=(K26*2)-" & x
            x = x + 1
            Loop
        End If
    End If
End Sub