Multiple IF Function

Adrae · Mar 26th, 2002, 10:11 AM

Can anyone tell me what is wrong with this function. I'm trying to setup an "Or" scenario, where the value in less than OR equal to.

=IF(B4
Thanks

Aladin Akyurek · Mar 26th, 2002, 10:15 AM

Quote:

On 2002-03-26 09:11, Adrae wrote:
Can anyone tell me what is wrong with this function. I'm trying to setup an "Or" scenario, where the value in less than OR equal to.

=IF(B4
Thanks

=(B4<=Sheet1!E4)*WIE!$F$6

MrStressed · Mar 26th, 2002, 10:15 AM

try this, though I might be Completely wrong.

=IF(B4<=Sheet1!E4,WIE!$F$6,0))

Adrae · Mar 26th, 2002, 10:22 AM

Well the error is gone, but the value is 0 no matter what. The values is Sheet1!E4 & B4 are dates formatted as MM/DD/YY. Could this be the problem, and if so, any ideas how to fix it?

If B4 is equal to or less than (comes before on calendar)E4 then I want to pull a specific value from another cell, else 0.

Thanks

Aladin Akyurek · Mar 26th, 2002, 10:26 AM

Quote:

On 2002-03-26 09:22, Adrae wrote:
Well the error is gone, but the value is 0 no matter what. The values is Sheet1!E4 & B4 are dates formatted as MM/DD/YY. Could this be the problem, and if so, any ideas how to fix it?

If B4 is equal to or less than (comes before on calendar)E4 then I want to pull a specific value from another cell, else 0.

Thanks [img]/board/images/smiles/icon_smile.gif[/img]

Try whether this works:

=(B4+0<=Sheet1!E4+0)*WIE!$F$6

Adrae · Mar 26th, 2002, 10:28 AM

That works beautifully!! Thanks

Cosmos75 · Mar 26th, 2002, 10:48 AM

=IF( B4 =< Sheet1!E4 , WIE!$F$6 , 0)

The "=<" means "equals to or less than".

[ This Message was edited by: Cosmos75 on 2002-03-26 10:06 ]

Adrae · Mar 26th, 2002, 11:17 AM

What about a "between" function. For example:

If E4 is between the dates 1/01/02 and 1/31/02 then value should be "x"

Any thoughts?

Thanks

Cosmos75 · Mar 26th, 2002, 11:34 AM

You can do this two ways

put your two reference dates in A1 (01/01/2002) and A2 (01/31/2002) and the cell you are looking at in E3

=IF(AND(E3 < A2 , E3 > A1),"OK","Not in range")

Or figure out the numerical value of the date by formating A1:A2 as numbers and use those number to replace the A2 and A3 in your formula.

In this case 01/01/2002 = 37257 and 01/31/2002 = 37278.

=IF(AND(E3<37287,E3>37257),"OK","Not in range")

Hope this helps.

[ This Message was edited by: Cosmos75 on 2002-03-26 10:34 ]

davers5 · Mar 26th, 2002, 11:39 AM

=IF(B4
You're missing a parentheses after the second if, it should be:

=IF(B4
This reads,
If B4 Otherwise If b4=sheet1 e4, then WIE f6
Otherwise 0

You should use a <= in this statement, but it is possible to nest if's you just have to have the write syntax. Here's an example of between:

=IF(A4>=10,IF(A4<=30,"True","False"),"False")
This will print "True" if the number is between 10 and 30 and "False" otherwise.

It works like this:
You can think of it as being If(condition,then, else)
If the condition a4>=10 is true then it goes to the then section and evaluates the next if. If a4<+30 is true it goes to then and returns "True". The then section of the initial if then becomes true and the cell prints true. It it's false, the then condition of the initial if becomes false and it prints "False". If the condition of the intial if is false it goes to the else section and prints "False".

I hope that makes sense and is not too confusing. You can nest up to 7 ifs in this manner.

Dave

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 26th, 2002, 10:11 AM	#1
Adrae Board Regular Join Date: Feb 2002 Location: Chicago, IL USA Posts: 306	Can anyone tell me what is wrong with this function. I'm trying to setup an "Or" scenario, where the value in less than OR equal to. =IF(B4 Thanks

Mar 26th, 2002, 10:15 AM	#3
MrStressed Board Regular Join Date: Mar 2002 Location: Kent, the Garden of England, that no one has bothered to weed. Posts: 62	try this, though I might be Completely wrong. =IF(B4<=Sheet1!E4,WIE!$F$6,0))

Mar 26th, 2002, 10:22 AM	#4
Adrae Board Regular Join Date: Feb 2002 Location: Chicago, IL USA Posts: 306	Well the error is gone, but the value is 0 no matter what. The values is Sheet1!E4 & B4 are dates formatted as MM/DD/YY. Could this be the problem, and if so, any ideas how to fix it? If B4 is equal to or less than (comes before on calendar)E4 then I want to pull a specific value from another cell, else 0. Thanks

Mar 26th, 2002, 10:28 AM	#6
Adrae Board Regular Join Date: Feb 2002 Location: Chicago, IL USA Posts: 306	That works beautifully!! Thanks

Mar 26th, 2002, 10:48 AM	#7
Cosmos75 Board Regular Join Date: Feb 2002 Location: Tulsa, OK Posts: 354	=IF( B4 =< Sheet1!E4 , WIE!$F$6 , 0) The "=<" means "equals to or less than". [ This Message was edited by: Cosmos75 on 2002-03-26 10:06 ]

Mar 26th, 2002, 11:17 AM	#8
Adrae Board Regular Join Date: Feb 2002 Location: Chicago, IL USA Posts: 306	What about a "between" function. For example: If E4 is between the dates 1/01/02 and 1/31/02 then value should be "x" Any thoughts? Thanks

Mar 26th, 2002, 11:34 AM	#9
Cosmos75 Board Regular Join Date: Feb 2002 Location: Tulsa, OK Posts: 354	You can do this two ways put your two reference dates in A1 (01/01/2002) and A2 (01/31/2002) and the cell you are looking at in E3 =IF(AND(E3 < A2 , E3 > A1),"OK","Not in range") Or figure out the numerical value of the date by formating A1:A2 as numbers and use those number to replace the A2 and A3 in your formula. In this case 01/01/2002 = 37257 and 01/31/2002 = 37278. =IF(AND(E3<37287,E3>37257),"OK","Not in range") Hope this helps. [ This Message was edited by: Cosmos75 on 2002-03-26 10:34 ]

Mar 26th, 2002, 11:39 AM	#10
davers5 Board Regular Join Date: Feb 2002 Posts: 255	=IF(B4 You're missing a parentheses after the second if, it should be: =IF(B4 This reads, If B4 Otherwise If b4=sheet1 e4, then WIE f6 Otherwise 0 You should use a <= in this statement, but it is possible to nest if's you just have to have the write syntax. Here's an example of between: =IF(A4>=10,IF(A4<=30,"True","False"),"False") This will print "True" if the number is between 10 and 30 and "False" otherwise. It works like this: You can think of it as being If(condition,then, else) If the condition a4>=10 is true then it goes to the then section and evaluates the next if. If a4<+30 is true it goes to then and returns "True". The then section of the initial if then becomes true and the cell prints true. It it's false, the then condition of the initial if becomes false and it prints "False". If the condition of the intial if is false it goes to the else section and prints "False". I hope that makes sense and is not too confusing. You can nest up to 7 ifs in this manner. Dave