2 Replies Latest reply: Sep 12, 2012 1:18 PM by Badunit
Quantum3 Level 1 (0 points)

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.



MacBook Pro, Mac OS X (10.7.4), 8 GB RAM ATI 7770 HD 1 GB DDR5
  • Badunit Level 6 (11,615 points)

    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.

  • Badunit Level 6 (11,615 points)

    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.