IF AND question
in [Functions]
|
From: Gamq on 5 May 2010 20:44 Hi there, I need a function that can provide one of three answers: 1 2 1 1... 3 3 4 4... ? ? ? ?... If A1 = 1 and A2=3, answer 106; but if A1=1 and A2 = 4, answer 104; but if A1=2, regardless of A2, answer 95. The next function for column B is the same, except the answer is dependent on the value delivered from the column A function. E.g. A3 + 6 or A3 +4 or A3-5 etc. Any help would be most appreciated. -- ***** Many thanks Gamq
From: Ms-Exl-Learner on 5 May 2010 21:34 Use the below formula for your first query. =IF(A1="","",IF(A1=2,95,IF(AND(A1=1,A2=3),106,IF(AND(A1=1,A2=4),104)))) I am not clear about your second query. Remember to Click Yes, if this post helps! -------------------- (Ms-Exl-Learner) -------------------- "Gamq" <Gamq(a)discussions.microsoft.com> wrote in message news:566A95F3-DA71-44D1-A9B7-0BE32AB06820(a)microsoft.com... > Hi there, > > I need a function that can provide one of three answers: > > 1 2 1 1... > 3 3 4 4... > ? ? ? ?... > > If A1 = 1 and A2=3, answer 106; but if A1=1 and A2 = 4, answer 104; but if > A1=2, regardless of A2, answer 95. > > The next function for column B is the same, except the answer is dependent > on the value delivered from the column A function. E.g. A3 + 6 or A3 +4 or > A3-5 etc. > > Any help would be most appreciated. > -- > ***** > Many thanks > Gamq
From: Steve Dunn on 6 May 2010 06:33 Is this it?: A3: =IF(A1=2,95,IF(A1=1,IF(A2=3,106,IF(A2=4,104,"Unknown")),"Unknown")) B3: =IF(B1=2,A3-5,IF(B1=1,IF(B2=3,A3+6,IF(B2=4,A3+4,"Unknown")),"Unknown")) If you'd prefer an error when when A1 or A2 contain unknown values, you can use NA() instead of "Unknown". If you are happy to have 0 as an answer for unknown values, these can be simplified: A3: =(A1=2)*95+(A1=1)*((A2=3)*106+(A2=4)*104) B3: =(B1=2)*(A3-5)+(B1=1)*((B2=3)*(A3+6)+(B2=4)*(A3+4)) HTH Steve D. "Gamq" <Gamq(a)discussions.microsoft.com> wrote in message news:566A95F3-DA71-44D1-A9B7-0BE32AB06820(a)microsoft.com... > Hi there, > > I need a function that can provide one of three answers: > > 1 2 1 1... > 3 3 4 4... > ? ? ? ?... > > If A1 = 1 and A2=3, answer 106; but if A1=1 and A2 = 4, answer 104; but if > A1=2, regardless of A2, answer 95. > > The next function for column B is the same, except the answer is dependent > on the value delivered from the column A function. E.g. A3 + 6 or A3 +4 or > A3-5 etc. > > Any help would be most appreciated. > -- > ***** > Many thanks > Gamq
|
Pages: 1 Prev: Why does this confuse me?? -- ? on Offset function Next: Worksheet Functions |