Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 07-28-2013, 01:13 AM
Jamal NUMAN Jamal NUMAN is offline Multiple If statements, Windows 7 64bit Multiple If statements, Office 2010 64bit
Expert
Multiple If statements,
 
Join Date: Nov 2010
Posts: 519
Jamal NUMAN is on a distinguished road
Question 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
Attached Images
File Type: jpg Clip_15.jpg (121.3 KB, 43 views)
Reply With Quote
  #2  
Old 07-28-2013, 02:58 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Multiple If statements, Windows 7 64bit Multiple If statements, Office 2010 64bit
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,766
Pecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant future
Default

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
Reply With Quote
  #3  
Old 07-28-2013, 04:01 AM
Jamal NUMAN Jamal NUMAN is offline Multiple If statements, Windows 7 64bit Multiple If statements, Office 2010 64bit
Expert
Multiple If statements,
 
Join Date: Nov 2010
Posts: 519
Jamal NUMAN is on a distinguished road
Question

Quote:
Originally Posted by Pecoflyer View Post
What is the B part in the last condition? 3B-9 is a string to XL. Is that what you want?
Many thanks Pecoflyer,

The excel file is attached. The 4 conditions are also included

Best

Jamal
Attached Files
File Type: xlsx MF.xlsx (9.4 KB, 12 views)
Reply With Quote
  #4  
Old 07-28-2013, 07:09 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Multiple If statements, Windows 7 64bit Multiple If statements, Office 2010 64bit
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,766
Pecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant future
Default

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
Reply With Quote
  #5  
Old 07-28-2013, 09:31 AM
Jamal NUMAN Jamal NUMAN is offline Multiple If statements, Windows 7 64bit Multiple If statements, Office 2010 64bit
Expert
Multiple If statements,
 
Join Date: Nov 2010
Posts: 519
Jamal NUMAN is on a distinguished road
Question

Quote:
Originally Posted by Pecoflyer View Post
I'm sorry, your logic totally escapes me for the two rows where the value in col A is <0 ?
Many thanks for the prompt answer Pecoflyer,

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
Reply With Quote
  #6  
Old 07-28-2013, 11:27 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Multiple If statements, Windows 7 64bit Multiple If statements, Office 2010 64bit
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,766
Pecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant future
Default

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
Reply With Quote
  #7  
Old 07-28-2013, 02:35 PM
Jamal NUMAN Jamal NUMAN is offline Multiple If statements, Windows 7 64bit Multiple If statements, Office 2010 64bit
Expert
Multiple If statements,
 
Join Date: Nov 2010
Posts: 519
Jamal NUMAN is on a distinguished road
Question

Quote:
Originally Posted by Pecoflyer View Post
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.
Sorry for the confusion

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
Reply With Quote
  #8  
Old 07-28-2013, 11:11 PM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Multiple If statements, Windows 7 64bit Multiple If statements, Office 2010 64bit
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,766
Pecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant future
Default

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
Reply With Quote
  #9  
Old 07-29-2013, 01:30 AM
Jamal NUMAN Jamal NUMAN is offline Multiple If statements, Windows 7 64bit Multiple If statements, Office 2010 64bit
Expert
Multiple If statements,
 
Join Date: Nov 2010
Posts: 519
Jamal NUMAN is on a distinguished road
Question

Quote:
Originally Posted by Pecoflyer View Post
Sorry you don't seem to understand my question
Hope someone else can help you
Thank you for the prompt help.

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
Attached Images
File Type: jpg Clip_17.jpg (64.6 KB, 40 views)
Attached Files
File Type: xlsx MF_02.xlsx (9.4 KB, 7 views)
Reply With Quote
  #10  
Old 07-29-2013, 02:08 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Multiple If statements, Windows 7 64bit Multiple If statements, Office 2010 64bit
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,766
Pecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant future
Default

Much better !
Enter
Code:
=IF(A3>0,A3+SIGN(B3)*B3,IF(B3>0,2*A3-B3,3*B3-A3))
in C3 and pull down as needed
__________________
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
Reply With Quote
  #11  
Old 07-30-2013, 02:24 PM
Jamal NUMAN Jamal NUMAN is offline Multiple If statements, Windows 7 64bit Multiple If statements, Office 2010 64bit
Expert
Multiple If statements,
 
Join Date: Nov 2010
Posts: 519
Jamal NUMAN is on a distinguished road
Question

Quote:
Originally Posted by Pecoflyer View Post
Much better !
Enter
Code:
=IF(A3>0,A3+SIGN(B3)*B3,IF(B3>0,2*A3-B3,3*B3-A3))
in C3 and pull down as needed

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
Attached Images
File Type: jpg Clip_478.jpg (61.3 KB, 39 views)
Attached Files
File Type: xlsx Q3.xlsx (10.0 KB, 8 views)
Reply With Quote
  #12  
Old 07-31-2013, 08:38 AM
BobBridges's Avatar
BobBridges BobBridges is offline Multiple If statements, Windows 7 64bit Multiple If statements, Office 2010 32bit
Expert
 
Join Date: May 2013
Location: USA
Posts: 700
BobBridges has a spectacular aura aboutBobBridges has a spectacular aura about
Default

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)
In an Excel formula, "&" is the string-concatenation symbol, not the logical AND operator. So what does Excel see here? I've done a little testing and I think it's this:
Code:
(A2 > (0 & B2)) > 0
That is, first it concatenates 0 to B2. In your sample worksheet that yields the string "011". Then it checks to see whether A2 (which has the value 1) is greater than "011". Excel decides this is FALSE. Lastly it decides whether the result FALSE is greater than 0, which is TRUE. That's why you're always getting A+B in your worksheet.

To do an AND condition, change your formula to do this:

Code:
=IF(AND(A2>0,B2>0),A2+B2,0)
Maybe that's all you need; if you now know how to proceed, you're on your way. But if you're still having trouble, here are some comments:

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,""))))
That's doing it the longest possible way. If you know that none of your values can ever be equal to 0, you can make it shorter:
Code:
=IF($A2>0,IF($B2>0,$A2+$B2,$A2-$B2),IF($B2>0,$A2*2,$B2*3-9))
If you like being clever—and I do—you can do it a little differently. Instead of doing multiple IFs and ANDs, consider turning the four possible combinations into choices 1, 2, 3 and 4. For this the calculation is
Code:
=(1-SIGN(A2))+(1-SIGN(B2))/2+1
What good is that?, you ask? Well, now you can tell Excel to CHOOSE which of four formula to apply:
Code:
=CHOOSE((1-SIGN(A2))+(1-SIGN(B2))/2+1,A2+B2,A2-B2,A2*2,B2*3-9)
The index calculation is harder to read than the IF and AND formulae, but the final calculations are lined up and maybe it's easier to pick them out this way.

Feel free to ask more questions, but I hope this helps out some.
Reply With Quote
  #13  
Old 08-07-2013, 01:41 AM
Jamal NUMAN Jamal NUMAN is offline Multiple If statements, Windows 7 64bit Multiple If statements, Office 2010 64bit
Expert
Multiple If statements,
 
Join Date: Nov 2010
Posts: 519
Jamal NUMAN is on a distinguished road
Thumbs up

Quote:
Originally Posted by BobBridges View Post
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)
In an Excel formula, "&" is the string-concatenation symbol, not the logical AND operator. So what does Excel see here? I've done a little testing and I think it's this:
Code:
(A2 > (0 & B2)) > 0
That is, first it concatenates 0 to B2. In your sample worksheet that yields the string "011". Then it checks to see whether A2 (which has the value 1) is greater than "011". Excel decides this is FALSE. Lastly it decides whether the result FALSE is greater than 0, which is TRUE. That's why you're always getting A+B in your worksheet.

To do an AND condition, change your formula to do this:

Code:
=IF(AND(A2>0,B2>0),A2+B2,0)
Maybe that's all you need; if you now know how to proceed, you're on your way. But if you're still having trouble, here are some comments:

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,""))))
That's doing it the longest possible way. If you know that none of your values can ever be equal to 0, you can make it shorter:
Code:
=IF($A2>0,IF($B2>0,$A2+$B2,$A2-$B2),IF($B2>0,$A2*2,$B2*3-9))
If you like being clever—and I do—you can do it a little differently. Instead of doing multiple IFs and ANDs, consider turning the four possible combinations into choices 1, 2, 3 and 4. For this the calculation is
Code:
=(1-SIGN(A2))+(1-SIGN(B2))/2+1
What good is that?, you ask? Well, now you can tell Excel to CHOOSE which of four formula to apply:
Code:
=CHOOSE((1-SIGN(A2))+(1-SIGN(B2))/2+1,A2+B2,A2-B2,A2*2,B2*3-9)
The index calculation is harder to read than the IF and AND formulae, but the final calculations are lined up and maybe it's easier to pick them out this way.

Feel free to ask more questions, but I hope this helps out some.
Many thanks BobBridges for the massive help,

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
Attached Images
File Type: jpg Clip_542.jpg (74.3 KB, 41 views)
Reply With Quote
  #14  
Old 11-13-2022, 03:26 AM
Jun39 Jun39 is offline Multiple If statements, Windows 11 Multiple If statements, Office 2021
Novice
 
Join Date: Nov 2022
Posts: 2
Jun39 is on a distinguished road
Default

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.
Reply With Quote
  #15  
Old 11-14-2022, 05:40 AM
p45cal's Avatar
p45cal p45cal is offline Multiple If statements, Windows 10 Multiple If statements, Office 2019
Expert
 
Join Date: Apr 2014
Posts: 863
p45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant future
Default

For fun, in C3 copied down:
Code:
=10*(BIN2DEC(N(A3<0)&N(B3<0))+1)
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Multiple If statements, 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
Multiple If statements, 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

Other Forums: Access Forums

All times are GMT -7. The time now is 12:54 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft