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

ck4242

New Member
Joined
Apr 8, 2019
Messages
8
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?
 

Some videos you may like

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

bs0d

Well-known Member
Joined
Dec 29, 2006
Messages
622
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

New Member
Joined
Apr 8, 2019
Messages
8
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

Well-known Member
Joined
Dec 29, 2006
Messages
622
What if, in the formula logic, instead of +1, you increment by the difference necessary to make them equal?
 

ck4242

New Member
Joined
Apr 8, 2019
Messages
8

ADVERTISEMENT

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

Well-known Member
Joined
Dec 29, 2006
Messages
622
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

New Member
Joined
Apr 8, 2019
Messages
8

ADVERTISEMENT

Yes, I did modify the max parameter as needed
 

ck4242

New Member
Joined
Apr 8, 2019
Messages
8
So thinking VBA may be only way of doing this.
here's what I have so far (never done this :)

Code:
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

Well-known Member
Joined
Dec 29, 2006
Messages
622
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

Well-known Member
Joined
Dec 29, 2006
Messages
622
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.

Code:
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
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,122,539
Messages
5,596,754
Members
414,097
Latest member
FaeFen

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
Top