From four billing rates (depending on two separate types of inputs), formula to pick1

    Hello,


    Really need help trying to find a formula chain where I am able to use a table of billing rates and depending on two inputs ("A or B" and "1 or 2" for a total of 4 unique rates) the formula will spit out the appropriate rate.


    So for example...


    Rate 1 is $100 and would be applied if A is true and 1 is true
    Rate 2 is $200 and would be applied if B is true and 1 is true
    Rate 3 is $300 and would be applied if A is true and 2 is true
    Rate 4 is $400 and would be applied if B is true and 2 is true


    I need a formula that if cell A1 says "A" and B1 says "2"...then the rate "$300" would populate in cell C1.


    I tried XOR but that just spits out TRUE/FALSE and not what is contained in a cell.


    Thanks!

    Re: From four billing rates (depending on two separate types of inputs), formula to p


    Nested IF and AND statements will do it:


    =IF(AND(A1="A",B1=1),"$100",IF(AND(A1="B",B1=1),"$200",IF(AND(A1="A",B1=2),"$300","$400")))

    Ali :)


    Enthusiastic self-taught user of MS Excel who's always learning!
    If anyone has helped you towards solving a problem, then you can thank them by clicking on the like icon bottom right.

    :!:Forum Rules

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login