Multiple IF Function

Adrae

Active Member
Joined
Feb 19, 2002
Messages
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<Sheet1!E4,WIE!$F$6,(if B4=Sheet1!E4,WIE!$F$6,0))

Thanks :)
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
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<Sheet1!E4,WIE!$F$6,(if B4=Sheet1!E4,WIE!$F$6,0))

Thanks :)

=(B4<=Sheet1!E4)*WIE!$F$6
 
Upvote 0
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 :)
 
Upvote 0
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 /board/images/smiles/icon_smile.gif

Try whether this works:

=(B4+0<=Sheet1!E4+0)*WIE!$F$6
 
Upvote 0
=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
 
Upvote 0
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 :)
 
Upvote 0
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
 
Upvote 0
=IF(B4<Sheet1!E4,WIE!$F$6,(if B4=Sheet1!E4,WIE!$F$6,0))

You're missing a parentheses after the second if, it should be:

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

This reads,
If B4<Sheet1 E4, then WIE f6
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
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,237
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top