MacMost Q&A Forum • View All Forum QuestionsAsk a Question

How Do I Combine Two If Statements, Which Refer To the Same Cell, Into One Formula In iWork Numbers 09?

I am setting up a table in which I have two categories: BB and INST. Each category has a different price (example, $20 for BB and $50 for INS).
The categories are in my D column and I would like to display the correct price in the E column.
Right now my (non-functioning) formula looks like this:
=OR(IF(D2=”BB”,20),OR,(IF(D2=”INS”=50)))
Thank you for your time and help.
—–
Corné

Comments: 11 Responses to “How Do I Combine Two If Statements, Which Refer To the Same Cell, Into One Formula In iWork Numbers 09?”

    12 years ago

    The way you would do it is to nest the IF statements.
    The first parameter is the condition to test. The second is the value if true, the third is the value if false. So put another IF function in the third parameter:
    =IF(D2="BB",20,IF(D2="INS",50,0))
    The last 0 would be the value if it is neither BB or INS.
    This works OK if you have two possible values of D2. But if it is more, you probably don't want to have deeply nested IF statements. So you would want to use LOOKUP instead and have a second table that is a list of items and values. I plan on doing a video on this very subject soon, perhaps next week.

    Richard van Uffelen
    11 years ago

    Hello, could anyone please tell me how to combine a series of "if expressions"?
    (I am using Apple Numbers on an iPad)
    A quick outline of my issue:
    I have a formula which has calculated a total turnover. Depending on the value of this turnover, I will want to automatically calculate a discount percentage:

    For this, I have 4 brackets in which this turnover can "sit".

    1. 10.000 - 20.000 (this bracket has 20% discount on purchase)
    2. 20.000 - 30.000 (this bracket has 30% discount on purchase)
    3. 30.000 - 40,000 (this bracket has 40% discount on purchase)
    4. 50,000 - or more (this bracket has 50% discount on purchase)

    Depending on in which bracket the turnover belongs, a discount percentage changes.

    Eg.
    If the turnover is 15,000, it is in the first bracket , the discount is 20%
    If the turnover is 23,000, it is in the second bracket , the discount is 30%
    If the turnover is 31,000, it is in the third bracket , the discount is 40%
    If the turnover is 67,000, it is in the fourth bracket , the discount is 50%

    So I need to calculate the percentage in a cell.
    I have created the if expression for one statement
    if(total estimated sales)>50,000,50,20)

    but as soon as I combine it with a second one it returns that the formula has errors. Is this possible? Am I using the right method? Any ideas?

    Thanks very much in advance.
    Richard

    Richard van Uffelen
    11 years ago

    Hi Gary, thanks so much for your quick reply. I thought of the lookup function earlier and tried it, however, from your video I understood that it looks up one value (e.g. bananas) rather than a range (eg: from 10,000-20,000. I have not found a way to use look-up that way. Is that possible? I hope you can help...as I am so nearly there with my "App", but this is the very last "hick-up"

      11 years ago

      Well, then nested IF statements might be your best bet. You just have to create it very carefully as it is easy to make a mistake with all the parenthesis. Look at my first response above for the simplest example. But yours will need to be more complex. Or, you could break it into multiple cells if you want: The first cell uses a single IF statement to test for 10-20 and puts .2 if it is, and 0 if not. Then the second cell tests for 20-30 and puts .3 or 0. Etc. Then the last cell adds up all of those cells for the total (should be all zeros in every case except one).

    Richard van Uffelen
    11 years ago

    Thanks a lot, Gary. I think your idea to break it down will hopefully do the trick. I will have a play!

    Richard van Uffelen
    11 years ago

    Hi Gary, Well I have got it all working perfectly. So thanks a lot. One issue I have is now all formulas are in, the "App" is a little sensitive for other people to use it as you can move things around and edit formulas. Is there any way to 'lock' cells so people can not 'break' things. Maybe it is not possible but it would be a great help...

      11 years ago

      You can't lock cells, but you can lock tables. So in some cases you can design your spreadsheet so all of the things that people shouldn't touch are in locked tables.

    Richard van Uffelen
    11 years ago

    Hi Gary, I am using Numbers on the iPad, but I have not found this function in there. I have selected the table then 'Arrange' but there is no "lock" option. Is this a function that is not supported in Numbers for the iPad? I could buy the application for the Mac and add it there and then open it on the iPad but I expect if the function is not supported it won't work. I am correct?

      11 years ago

      Ah, I didn't realize you were using Numbers for iPad (this topic is about Numbers for Mac). I guess the locking function just isn't there. Still, you could simply put those cells into tables that are off the screen or on different tabs in the same spreadsheet.

    Richard van Uffelen
    11 years ago

    HI Gary, Thanks for that. That locking function would have been perfect, but OK, I will find a work around somehow. Thanks again.

Comments Closed.