If then loop (non VBA)

[ScorpionKing]

How can I create an if then function (non VBA) to manipulate (increase or decrease in increments of 10) one number (A) until another number is less than 100 (C). Please keep in mind that (A) is part of the formula of (C) so I can possibly run into circular reference issues. I also have more than 7 possible increments of (A) so the If then function will have to be defined as a name.

 

[Boller]

I think you will need to post some actual examples of what you want to do.

 

[ScorpionKing]

I think you will need to post some actual examples of what you want to do.

I don't know what code to write since I don't know where to start from....but this is theoretically what I want to do...

A1 being cell A1
C1 being cell C1.....

Case 1

A1 = 20 C1 = 150

Case 2

A1 = 30 C1 = 120

Case 3

A1 = 40 C1 = 99

As you can see, A increases in increments of 10 until C is less than 100.

 

[ScorpionKing]

I want to do something like this until the condition is met....

If(C<100,A={10,20,30,40,50,60},"NO")

basically keep changing the value of A in increments of 10 until C is less than 100

 

[Boller]

Do you mean that each time there is a change in C1, increase the value in A1 by 10, unless C1 changes to a value less than 100 in which case display "NO" in A1 ?

If so, go to Tools>Options>Calculation and put a check mark against Iterations.

Then try this formula in A1 :-

<TABLE style="WIDTH: 186pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=248 border=0 x:str><COLGROUP><COL style="WIDTH: 186pt; mso-width-source: userset; mso-width-alt: 9069" width=248><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 186pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=248 height=17 x:str="'=IF(C1<100,"No",IF(A1="No",10,A1+10))">=IF(C1<100,"No",IF(A1="No",10,A1+10))</TD></TR></TBODY></TABLE>

 

[ScorpionKing]

Thanks for helping but that is not what I'm looking for....

I want it to keep checking in increments of 10 until C1 is less than 100. Not a one time check but a continuous check from 10 to 200 in increments of 10 until C1 is less than 100.

 

[Boller]

I don't understand what you want to do.

 

[JasonC71]

is there a relationship between A and C? Is A1 changing C1 or is C1 independent of A1? In other words, what makes C1 become less than 100?

 

[ScorpionKing]

A1 changes C1 and C1 is not independent of A1. C1 contains a formula that decreases as A1 increases. C1's formula is too long to list and has other variables that input into it as well but A1 is the only number I am allowed to change to get C1 less than 100. All other variables can not be changed.

What I am trying to do is like a for next loop. Keep increasing A1 until C1 is less than 100. A1's increments only go 16 long. So my choices for A1 would be:

10,20,30,40,50,60,70,80,90,100,110,120,130,140,150,160

If you have any more questions, please let me know.

 

[mikerickson]

Have you looked at Solver or Goal Seek.
Also, you might be able to write a formula if the differential dC/dA is more reasonable than the formula for C.