If Then Function in Excel

[NSGuard]

Is there a way to have two If Functions running together. The second one is attempting to place an greater than or less than equation into the first If loop based on if a specific cell contains a negative or positive number.

ie
This is what mycurrent attempt has produced, but doesn't work, the logic seems right to me. I just don't know how to make it work.

=IF(H5(IF(D5<0,>=,<=))D5,E5,0)

D5 = -7.5
E5 = 1
H5 = 8

The end result I'm looking for is:
=IF(H5>=-7.5,1,0)

Any help would be greatly appreciated. Thanks in advance.

 

[swingkyd]

well, I'm a little confused with your logic.
it sounds like you want to create a nested IF statement (which is no problem to do).
If you write out your logic with more examples, I probably could help you more.
Your statement doesn't work because the syntax for the IF statement is not correct.

IF (H5(IF(D5<0,>=,<=))D5,E5,0)

For this to work: "H5(IF(D5<0,>=,<=))D5" must evaluate either logical TRUE or FALSE. Since it does not, the IF function fails.
Perhaps you could clarify the logic a little more.
Try writing out what you want in logical sentences like:
If <value1> is Greater Than <value2> , I want to do <this>
If <value1> OR NOT (<value2>), choose this... etc.

Try that.

 

[AnotherHiggins]

=IF(H5(IF(D5<0,>=,<=))D5,E5,0)

I think this is what you're trying to do:
=if(d5<0,if(h5>=d5,e5,0),if(h5<=d5,e5,0))

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[NSGuard]

I guess I didn't explain it too well did I, haha.


A B C D E F G H I

3 Team A 19 -2 ???
4 at Team B 8/1/06 -7.5 1 21 2 ???

5 Team C -7.5 1 15 8 ???
6 at Team D 8/1/06 7 -8 ???


Here is an example of what I'm trying to do. Only using lines D, E, G and H. Calculation is for I.

D - Spread (can be negative or positive)
E - Wager (either going to be 0, 1, 4, 5, 6)
G - Actual Score for the team
H - Difference between scores of the two teams

This is a football spread pool. I am attempting to have I calculate if a team covers the spread (D) then they get the amount they wagered (E) in Column I, if they don't cover the spread the get 0.

My problem comes about when the spread is a positive number or a negative number. That is where I was attempting to have a statement chose which equation to use, if it was a negative spread use >= or if it was a positive spread use <=.

I'm not too sure this explains it any clearer, but it was worth a try.

Thanks for your attempts swingkyd and anotherhiggins.

 

[AnotherHiggins]

My suggestion,
=if(d5<0,if(h5>=d5,e5,0),if(h5<=d5,e5,0))
, will follow the logic you want. You may need to tweak the cell references.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[NSGuard]

Sorry John,

That didn't do exactly what I needed b/c the D5 will not always be a negative number.

This is pretty much what I'm looking to do, but all in one statement.

------------------
IF D5>0
Then
H5 > D5 Then I5 = E5 or
H5 < D5 Then I5 = 0 or
H5 = D5 Then I5 = 0

Or

IF D5<0
Then
H5 > D5 Then I5 = 0 or
H5 < D5 Then I5 = E5 or
H5 = D5 Then I5 = 0
------------------

Not even sure if it can be done, but hoping it can.

 

[Fenrirshowl]

Is this what you need in I5?

=if(d5<0,if(h5>d5,e5,0),if(d5>0,if(h5<d5,e5,0),0)

The final zero being the result if D5 is zero.

Fen

 

[AnotherHiggins]

NSGuard ,

Did you try my formula?

An if function works like this:
[tt]
=If(Condition1,ResultIfCond1IsTrue,ResultIfCond1IsFalse)
^ ^
Then Else[/tt]

My suggestion works like this:

=If(Condition1,
[tab]Then
[tab][tab]If(Condition2,ResultIfCond1&2AreTrue,ResultIfCond1IsTrue&Cond2IsFalse),
[tab]Else
[tab][tab]If(Condition3,ResultIfCond1IsFalse&Cond3ISTrue,ResultIfCond1&3AreFalse)
[tab]End If
)

You've changed your criteria since your original post, but the following does EXACTLY what you requested in you last post:

=if(D5>0,if(h5>d5,e5,0),if(h5>=d5,0,e5))

Fenrirshowl has a good point, you don't say what should happen if D5 = 0.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.