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.

 

[ScorpionKing]

I'm looking at the Solver and Goal Seeker now...still would like to use a formula if possible....any idea???

 

[mikerickson]

Any single formula answer would depend on your current formula in C1.
You could drag the formula in C1 down, so C2 was a depedent of A2, C3 dependent of A3, etc.
and in A2 put

=IF(C1<100,A1,A1+10)

and fill that down until it stopped changing value. If all the references in C1 (except for A1) were absolute, that might work.

 

[ScorpionKing]

Unfortunately, the formula for C1 is in a specific format which does not allow me to drag down the values for C1 because then I would mess up other formulas

 

[mikerickson]

Can you post the formula in C1? I think that it could be made absolute (except for A1)

 

[ScorpionKing]

The formula in C1 feeds off several (maybe 10) different formulas...can't post em' all

 

[ScorpionKing]

In the interim I'm using form controls to manipulate A1. Did anyone get any more ideas?

 

[JasonC71]

Any restrictions on adding say a lookup table on another sheet or somewhere on the Worksheet containing the formula? If not, and If you can copy/paste the formula in C1 to another cell (you need 2 versions of the formula)...I know how to do this.

As an example(doesn't have to be these cells, just an example):

Take C1 formula and copy it EXACTLY AS IS into C2, but REMOVE THE REFERENCE TO A1 FROM THE FORMULA...you need to generate a number that does not yet have A1 subtracted out.

Now, say under A1..from A2:A30 put in your 10's...A2=10, A3=20, A4=30..and so on...

Put the following formula in A1:
A1=VLOOKUP(C2-100,A2:A30,1,FALSE)

That should generate the closest power of 10 needed to bring C1 to 100 or less.

This is not by any means a "beautiful" answer, but it does work.

Hope that helps,

Jason

 

[JasonC71]

Now that I think about it..you don't need the lookup..if you recreate c1's formula somewhere else without the A1 refrence, all you need to do is subtract 100 and round the answer..that is the number that needs to be in A1.

 

[ScorpionKing]

Thanks Jason....I'll give this a try.

 

[JasonC71]

So in the end you should have something like this (I don't know how to do one part, but i know it's possible...someone help please).

So to build on the idea:

In cell A1:

1. construct the exact same formula that is in C1, but replace every reference to A1 with -100. This should generate the number that would be nessesary to make C1 100 after subtraction.

2. once that works, you would encase the formula with (formula here), and add in something that will round the answer up to the nearest 10..this would be the part that I don't know how to do but do know it's possible..


Hope that makes sense,

Jason