Need help building an autonomy formula, able to self-arrange itself

Question

Level 1

2 points

Need help building an autonomy formula, able to self-arrange itself

Hi again, folks! I've recently been helped by Barry in some of my problems with Numbers and formulas, and he helped me a lot, but I need a new help. Please, notice that I have no idea about spreadsheets, mathematics and such, but a creative mind 🙂 As well, English is my second languaje, so expect some errors in my grammar and the like 😊

I'm now needing some numbers to re-arrenge by themselves taking some criterias as reference. I don't know what I have to do, I just know the basic formulas and a very basic knowledge of 'if statements', so I'm naked on this.

So... I have 3 cells, for example, A, B and C.

· Cell A has an amount.

· Cell B has an amount which is handled by Cell A through a formula in Cell B.

· Cell C displays the total amount from Cell B (and other cells, but I think that doesn't matter right now), so values in Cell C dependes on a formula which sums from other cells.

So, the criteria is this:

1. If value in Cell C is under 200, Cell B should increase its value till Cell C reaches amount = 200.

2. If value in Cell C is above 200, Cell B should decrease its value till Cell C reaches amount = 200.

3. Amount in Cell A should remain unaffected by these changes.

The problems are: amount in Cell B is determined by the amount in Cell A plus a formula in Cell B.

This is:

1. Cell A has a number, for example, 20.

2. Cell B has a formula which states that any value in Cell A must be multiplied by 2 and summed by 40 (=2*B4+40) without parentesis. The reason of this is because each increaement in Cell A should apply 2 increments in Cell B. So for example:

Cell A = 20.
Cell B = 50.

If I increase A to 21; B is 52; if A=22; B=54; etc.

Now the problem is that the number in Cell B increases a final amount in Cell C. And here is the main problem. Amount in Cell C must not go beyond 200. So the criteria is what I wrote above, in So, the criteria is this: "..."

So well... I'm literally stuck there because I cannot make this work so if anybody knows, I'm all eyes.

Ø3 🙂

MacBook Pro, Mac OS X (10.7.4), 8 GB RAM ATI 7770 HD 1 GB DDR5

Posted on Sep 12, 2012 12:37 PM

Reply

Answer 1

Badunit

Level 7

25,796 points

Sep 12, 2012 1:06 PM in response to Quantum3

The problem you will find is that this is a circular reference. The value of C depends on the value of B while, at the same time, the value of B depends on the value of C. So you cannot do it the way you are thinking.

If I understand what you said,

A = an amount you enter by hand

B = 2*A+10 + whatever it takes to get C to be exactly 200.

C = B + some other cells. I'll use C=B+D.

So, the result will be:

A = an amount you enter by hand

C = 200 , also entered by hand. You want it to always be 200 so put that number in the cell.

B = C-D

However, this totally negates your original purpose of having B=2*A+10 (this formula is based on your example numbers for A and B). The value in A has no affect whatsoever on the value in B or C.

Reply

Answer 2

Badunit

Level 7

25,796 points

Sep 12, 2012 1:18 PM in response to Badunit

If, however, you still require B = 2A+10 and at the same time require C = 200,

C = 200, entered by hand.

B = C-D

A = (B-10)/2 , not a value entered by hand

You cannot meet the exact criteria you specified in your original problem statement. You have two criteria for determining B and they are most likely not going to come to the same result.

Reply