Conditional recalculating 1

[realm174]

Good morning,

I am trying to figure out if there is anyway to recalculate a cell if and until a certain value is met... here's the idea:

Cell P3 contains a rand() formula that grabs the value of a cell in a different workbook. From that value, there's a long complicated boring calculation done in Q3.

What I need to do is have P3 (the random value) "refreshed" or recalculated until such time that the result in Q3 is less than 1000. And I guess, as a safety measure, we should add a maximum number of recalculations so that we don't fall into an endless loop.

I know this is sorta not all that clear, I hope this makes sense... if not, I'll give the whole story, it'll make more sense that way.

Any idea anyone?

thanks!!


Cheers,

Realm174

 

[byundt]

Realm174,
You might consider turning on iterations in the Tools...Options...Calculations menu item. You can also specify the maximum number of recalculations that the code will perform in an effort to get the "right" value in Q3.

My test workbook used the following formulas:
=IF(Q3<1000,0.1*RAND(),RAND()) in cell P3
=RAND()*10000*P3+RAND() in cell Q3

Every time I hit the F9 key, the workbook would flash through some recalculations before settling on a value in Q3 less than 1000.
Brad

 

[realm174]

Thank you Brad. I copied and pasted the formulas you have give, and it works beautifully... however, I am having difficulties understanding the logic behind it, and therefore, I cannot figure out how to apply the formula to my existing spreadsheet... in P3 I have the following:

=OFFSET(AIRPORT2!A1,RAND()*5521,0)

This displays the ICAO airport abbreviation.

In Q3 I have the following:

=6377*ACOS(COS(RADIANS(90-I3))*COS(RADIANS(90-VLOOKUP(P3,AIRPORT2!$A$2:$D$5522,3)))+
SIN(RADIANS(90-I3))*SIN(RADIANS(90-VLOOKUP(P3,AIRPORT2!$A$2:$D$5522,3)))*COS(RADIANS(H3-VLOOKUP(P3,AIRPORT2!$A$2:$D$5522,2))))

Which calculates the distance between coordinates of an airport's lat-long (Cell I3 and H3)

So the goal, here, find an airport in a list which will be less than 1000 km from the one in I3,H3. If the random airport it picked in P3 is farther than 1000km (displayed in Q3, then it needs to try a different value in P3, until such time that the long calculation in Q3 is less than 1000.

Does this make sense?

I (possibly falsly) assumed that the example you gave in your response could be just copied and pasted into a different cell, and that it would repeat itself until such time that Q3 would be less than 1000, but that did not seem to happen.

Thanks again, and if you could let me know where I have gone wrong, it would be highly appreciated.

thank you,

Cheers,

Realm174

 

[byundt]

Realm174,
Solving this problem will probably require you to post the workbook. But first try the following formulas for P3 and Q3:
=IF(Q3<1000,0.1*RAND(),OFFSET(AIRPORT2!A1,RAND()*5521,0))
=0*RAND() + 6377*ACOS(COS(RADIANS(90-I3))*COS(RADIANS(90-VLOOKUP(P3,AIRPORT2!$A$2:$D$5522,3)))+
SIN(RADIANS(90-I3))*SIN(RADIANS(90-VLOOKUP(P3,AIRPORT2!$A$2:$D$5522,3)))*COS(RADIANS(H3-VLOOKUP(P3,AIRPORT2!$A$2:$D$5522,2))))

Many ISP allow their customers to create a personal web page. If so, you can post your file there, and then put the link in a Comment. This is the approach that I take.

You can post your workbook for free at Geocities:

http://geocities.yahoo.com/ps/learn2/HowItWorks4_Free.html

Another popular free site is AngelFire

http://www.angelfire.lycos.com/doc/subscriptions/index.html

If none of the above work out for you, I would be willing to post a link to your file if you e-mail it to me. My address is my Tek-Tips screen name at vbaexpress.com
Brad

 

[realm174]

Hi Brad,

good idea to make the xls available... I have put 2... one being the original at:

http://www.realmsworld.com/FS Distance.xls

And the one where I've replaced P3 and Q3 with your suggestions at:

http://www.realmsworld.com/FS Distance2.xls

I see that it recalculates a whole bunch of times, but rarely comes with a value in Q3 that is at or below 1000...

Thank you again for helping me out!


Cheers,

Realm174

 

[byundt]

Realm174,
I played with the formula in cell P3 in your second workbook, and was able to get an answer less than 1000 very consistently.

If you promise never to let your formulas return an error value and want only one possible answer, then try:
=IF(Q3<1000,P3,OFFSET(AIRPORT2!A1,RAND()*5521,0))

If you want one and only one answer and need to recover from errors, then try this in P3:
=IF(ISERROR(P3),AIRPORT2!A2,IF(Q3<1000,P3,OFFSET(AIRPORT2!A1,RAND()*5521,0)))

And if you want the ability to change to a different airport when the worksheet recalculates, then try:
=IF(ISERROR(P3),AIRPORT2!A2,IF(Q3<1000,INDEX(AIRPORT2!A:A,MATCH(P3,AIRPORT2!A:A,0)+0*RAND()), OFFSET(AIRPORT2!A1,RAND()*5521,0)))

To recover from an error value if you are using the first formula, change it to the second formula then switch it back.

To make the third formula recalculate, select P3, click in the formula bar and hit Enter.
Brad