Forum Discussion

Mission956's avatar
Mission956
Copper Contributor
Nov 04, 2019

Logic tables

I have used excell for years with simple formulas. Now I need to use what I tem logic flows. Here is a description of what I need to accomplish.

I need: If Cell B1 is greater than 100 and less than 200, cell B3 will equal 4. 

I also need: If  Cell B1 is greater than 100 and less than 200 Cell B3 will equal Cell B5.

Can anyone teach a 76 year old dog this new trick?

Thanks

Ned

Formulas and Functions

4 Replies

  • Abiola1's avatar
    Abiola1
    MVP
    Nov 04, 2019
    Hello,
    You've got to use both IF & AND Functions to get the desires outcome
    Question 1
    =IF(AND(B1>100, B1<200), B3, 0)
    Question 2
    IF(AND(B1 > 100, B1 < 200), B5, 0)


    I hope this this helps

    Regards
    • Mission956's avatar
      Mission956
      Copper Contributor
      Nov 04, 2019

      Abiola1  Thanks for the response. I have the Xcel manual on order because what I need to know is too much to learn here. The first response works fine. The second appears to be the same without the = and a different source cell even though the question was different. Forget that second one. Concerning the first one; could the formula contain several greater / less than statements so that >0 but <99 =4, >=100 but <=199=6, etc? 

      Its clear I don't even know more than the basic syntax.

      Thanks again

      • Abiola1's avatar
        Abiola1
        MVP
        Nov 04, 2019
        Hello,

        Glad you found my response useful. Yes of course, you can evaluate up to 255 logical condition with the AND function nested inside IF function
        For example
        IF(AND(A1>100, B1<50, C1>=400), D1*5%, D1*2%)

        The above formula is simply saying IF the value in cell A1 is greater than 100, value in cell B1 is less than 50 and value in cell C1 is greater than of equal to 400 Then, multiply the value in cell D1 by 5 percent. Otherwise, multiply the value in cell D1 by 2 percent

Resources