#1
|
|||
|
|||
Multiple If statements,
Multiple If statements,
I wanted to code an If statement that meet the cases below but sounds not to work with me. if A>0 and B>0 then X=A+B If A>0 and B<0 then X=A-B If A<0 and B>0 then X=2*A If A<0 and B<0 then X=3B-9 It works for the first condition but couldn’t know how to proceed (attached) Thank you Best Jamal
__________________
Jamal NUMAN, Jamal432@gmail.com, P.O.BoX: 731, Ramallah, West Bank. |
#2
|
||||
|
||||
What is the B part in the last condition? 3B-9 is a string to XL. Is that what you want?
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post |
#3
|
|||
|
|||
Quote:
The excel file is attached. The 4 conditions are also included Best Jamal
__________________
Jamal NUMAN, Jamal432@gmail.com, P.O.BoX: 731, Ramallah, West Bank. |
#4
|
||||
|
||||
I'm sorry, your logic totally escapes me for the two rows where the value in col A is <0 ?
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post |
#5
|
|||
|
|||
Quote:
These conditions are compatible with the four possible negative/positive values for A and B The conditions are equivalent to saying: If A is positive and B is positive then… If A is positive and B is negative then… If A is negative and B is positive then… If A is negative and B is negative then… Best Jamal
__________________
Jamal NUMAN, Jamal432@gmail.com, P.O.BoX: 731, Ramallah, West Bank. |
#6
|
||||
|
||||
Thank you for the explanation
As I have more than 10000 posts in one forum or another, I know what A1>0 means thank you Would you please be so kind to explicitate what the outcome is when conditioons are met In XL there is no A and B, but A1 or B20 Your examples of outcome when A1 <0 are a complete mystery to me. I cannot understand your logic.
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post |
#7
|
|||
|
|||
Quote:
What I meant is: If A2 is positive and B2 is positive then C2= If A2 is positive and B2 is negative then C2= If A2 is negative and B2 is positive then C2= If A2 is negative and B2 is negative then C2= Thanks Best Jamal
__________________
Jamal NUMAN, Jamal432@gmail.com, P.O.BoX: 731, Ramallah, West Bank. |
#8
|
||||
|
||||
Sorry you don't seem to understand my question
Hope someone else can help you
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post |
#9
|
|||
|
|||
Quote:
Apologies to not be able to well explain what I want. If you allow me, I’ll be trying to explain what I need once more. Please, consider the values of A and B and the calculated value of X (attached) A B X 1 11 12 2 -12 14 -5 12 -22 -1 -1 -2 These calculations are made based on the conditions below if A>0 and B>0 then X=A+B If A>0 and B<0 then X=A-B If A<0 and B>0 then X=2*A-B If A<0 and B<0 then X=3B-A My issue here is that I couldn’t code these conditions in excel Best Jamal
__________________
Jamal NUMAN, Jamal432@gmail.com, P.O.BoX: 731, Ramallah, West Bank. |
#10
|
||||
|
||||
Much better !
Enter Code:
=IF(A3>0,A3+SIGN(B3)*B3,IF(B3>0,2*A3-B3,3*B3-A3))
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post |
#11
|
|||
|
|||
Quote:
Many thanks Pecoflyer for the help, I wanted to code the conditions below: if A3>0 and B3>0 then C3=10 If A3>0 and B3<0 then C3=20 If A3<0 and B3>0 then C3=30 If A3<0 and B3<0 then C3=40 I tried the formula below but sound not to produce the correct answer =IF((A3>0&B3>0), 10, (IF((A3>0&B3<0), 20, IF(A3<0 & B3>0, 30, 40)))) Also, the formula that you have already provided sounds not to be generic, it fails to give the correct answer when A3>0 and B3<0 =IF(A3>0,10,IF(B3>0,30,40)) What might be the issue here? Best Jamal
__________________
Jamal NUMAN, Jamal432@gmail.com, P.O.BoX: 731, Ramallah, West Bank. |
#12
|
||||
|
||||
Jamal, let me try. One thing I see wrong in your attachment is the way you're attempting to do "and" conditions:
Code:
=IF((A2>0&B2>0),A2+B2, 0) Code:
(A2 > (0 & B2)) > 0 To do an AND condition, change your formula to do this: Code:
=IF(AND(A2>0,B2>0),A2+B2,0) 1) You tried three time to explain to Pecoflyer what you want to accomplish, and although the explanations look clear enough to me, I do notice that you said it differently each time. The first time, on 07-28, you said: A>0, B>0 => A+B A>0, B<0 => A-B A<0, B>0 => 2A A<0, B<0 => 3B-9 The next day you changed what happened when A is negative: A<0, B>0 => =2A-B A<0, B<0 => 3B-A The third time, 07-30, you just gave constants: A>0, B>0 => 10 A>0, B<0 => 20 A<0, B>0 => 30 A<0, B<0 => 40 I think I know why you did that: You already know how to do the arithmetic calculation, but you wanted to ask only about the IF formula. But if there's another reason, what is it? 2) If you're not sure how to combine many IF statements together, here's one sample. It uses your first set of calculations, and if any of the values in A or B is equal to 0 (rather than greater or less) then it displays a blank value: Code:
=IF(AND($A2>0,$B2>0),$A2+$B2,IF(AND($A2>0,$B2<0),$A2-$B2,IF(AND($A2<0,$B2>0),$A2*2,IF(AND($A2<0,$B2<0),3*$B2-9,"")))) Code:
=IF($A2>0,IF($B2>0,$A2+$B2,$A2-$B2),IF($B2>0,$A2*2,$B2*3-9)) Code:
=(1-SIGN(A2))+(1-SIGN(B2))/2+1 Code:
=CHOOSE((1-SIGN(A2))+(1-SIGN(B2))/2+1,A2+B2,A2-B2,A2*2,B2*3-9) Feel free to ask more questions, but I hope this helps out some. |
#13
|
|||
|
|||
Quote:
Correct. You got my point. All what I wanted to know is how to write an If statement for the cases below if A3>0 and B3>0 then C3=10 If A3>0 and B3<0 then C3=20 If A3<0 and B3>0 then C3=30 If A3<0 and B3<0 then C3=40 The equation you have provided works fine (attached) =IF(AND(A3>0,B3>0),10,IF(AND(A3>0,B3<0),20,IF(AND( A3<0,B3>0),30,IF(AND(A3<0,B3<0),40,"")))) Best Jamal
__________________
Jamal NUMAN, Jamal432@gmail.com, P.O.BoX: 731, Ramallah, West Bank. |
#14
|
|||
|
|||
As a worksheet function, the IF function can be entered as part of a formula in a cell of a worksheet. It is possible to nest multiple IF functions within one Excel formula in Excel 4 Outcomes. You can nest up to 7 IF functions to create a complex IF THEN ELSE statement.
|
#15
|
||||
|
||||
For fun, in C3 copied down:
Code:
=10*(BIN2DEC(N(A3<0)&N(B3<0))+1) |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Please tell me it doesn't take 7 statements to turn table borders off | Jennifer Murphy | Word VBA | 5 | 07-25-2012 03:12 PM |
showing results of If statements in a mail merge | m&co | Mail Merge | 3 | 01-06-2012 07:27 PM |
Multiple Input, Multiple Digital Signatures | Jeff | Word | 0 | 01-04-2012 09:03 AM |
Multiple task lists and multiple calendars | kballing | Outlook | 0 | 01-18-2011 10:23 AM |
IF statements that shifts to right. | kent | Excel | 0 | 01-19-2006 02:23 PM |