6 Replies Latest reply: Nov 9, 2010 10:01 PM by Barry
Fullastarbucks Level 1 Level 1 (0 points)
I'm new to using numbers (or even the other brand's equivalent) and my last programming experience was in Basic using IBM punch cards (anybody remember?). Anyway, here's what I'm trying to do, where A = the previous column value and B = the current column value:

IF A>100 THEN B=A*.75; IF A<100 THEN B=A-25.

Thanks in advance for your advise.

MacBook, Mac OS X (10.6.4)
  • LarryPGH Level 1 Level 1 (15 points)
    Two thoughts... first, your function leaves one possibility open -- what value should B be set to, if A = 100?

    That being said, here's how to write the function:

    Let's assume that A is in call A3 and B is cell B3. Then, in cell B3, the function would be:

    =if(a3>100, a3*.75, if(a3<100, a3-25))

    Note that the "else" part of the nested if statement is blank. Try entering 100 in A3, and see what you get... ;^)

    Cheers,

    Larry
  • Fullastarbucks Level 1 Level 1 (0 points)
    Thanks Larry!! That did it! except for your perceived "100" issue, which I solved by making it less than 101. I had figured out all of the easier functions, but this one threw me for a loop, and I couldn't find this in the Apple Formulas Guide.

    Thanks again!
  • Level 8 Level 8 (41,790 points)
    Why not :

    =IF(A3>100, A3*.75, if(A3<100, A3-25,herethe_value_forA3=100))

    Yvan KOENIG (VALLAURIS, France) mardi 9 novembre 2010 21:07:38
  • LarryPGH Level 1 Level 1 (15 points)
    Yes, Yvan, of course.

    However, that was the point I was gently attempting to draw out; and, the OP solved the problem in a different way -- by changing the condition, rather than adding a new function value.
  • Badunit Level 6 Level 6 (11,400 points)
    Given the way you solved the "100" issue, a simpler form of the formula would be "if A>100 then B=A*0.75 else B=A-25"

    =IF(A>100,A*0.75,A-25)

    No need for a nested IF statement.
  • Barry Level 7 Level 7 (29,215 points)
    Fullastarbucks wrote:
    Thanks Larry!! That did it! except for your perceived "100" issue, which I solved by making it less than 101.


    I don't see the need for a second IF statement.

    the syntax on IF is:

    IF(if-condition,if-true,if-false)

    From your statement above, I gather you want A to be multiplied by 0.75 for values of up to and including 100, and to be reduced by 25 for values greater than 100. Written so that those two conditions are mutually exclusive (ie. no gap or overlap), you need only one condition statement.

    =IF(A<=100,A*0.75,A-25)

    Since in your particular case the result for A=100 is the same whether it's multiplied by 0.75 or has 25 subtracted from it, you would get the same results by putting 100 on the 'false' side of the condition:

    =IF(A<100,A*0.75,A-25)

    <div class="jive-quote">I had figured out all of the easier functions, but this one threw me for a loop, and I couldn't find this in the Apple Formulas Guide.

    iWork Formulas and Functions User Guide, page 158, in discussion of the IF function:

    *Usage Notes*
    If the Boolean value of if-expression is TRUE, the function returns the if-true
    expression; otherwise it returns the if-false expression.
    Both if-true and if-false *can contain additional IF functions (nested IF functions).*

    Regards,
    Barry