Consider 2nd IF if a criteria is met

[hsandeep]

column B & column C are variables
column E & column F are constants (2.5 & -2.5 respectively)

Output column is column H.
Please bear with my language...please

I am using H2<f2,-100,0)) and="" able="" to="" get="" correct="" answer.
=IF(B2>E2,100,IF(C2 is less than F2,-100,0))

<f2,-100,0))
<f2,-100,0))
But H4=Incorrect. Answer should be -100 since C4=-2.6 which is less than F4 i.e. as long as C is non zero the formula should consider 2nd IF.

What should be the 'correct' formula.
Sheet1

	B	C	D	E	F	G	H

<tbody>
[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD="align: right"]3.2[/TD]
[TD="align: right"]0[/TD]

[TD="align: right"]2.5[/TD]
[TD="align: right"]-2.5[/TD]

[TD="align: right"]100[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]
[TD="align: right"]3.2[/TD]
[TD="align: right"]-2.4[/TD]

[TD="align: right"]2.5[/TD]
[TD="align: right"]-2.5[/TD]

[TD="align: right"]100[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]
[TD="bgcolor: #ffcc99, align: right"]3.2[/TD]
[TD="bgcolor: #ffcc99, align: right"]-2.6[/TD]
[TD="bgcolor: #ffcc99"][/TD]
[TD="bgcolor: #ffcc99, align: right"]2.5[/TD]
[TD="bgcolor: #ffcc99, align: right"]-2.5[/TD]
[TD="bgcolor: #ffcc99"][/TD]
[TD="bgcolor: #ffcc99, align: right"]100[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]-4.77[/TD]

[TD="align: right"]2.5[/TD]
[TD="align: right"]-2.5[/TD]

[TD="align: right"]-100[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula H2 =IF(B2>E2,100,IF(C2<f2,-100,0)< font="">)</f2,-100,0)<> H3 =IF(B3>E3,100,IF(C3<f3,-100,0)< font="">)</f3,-100,0)<> H4 =IF(B4>E4,100,IF(C4<f4,-100,0)< font="">)</f4,-100,0)<> H5 =IF(B5>E5,100,IF(C5<f5,-100< font=""></f5,-100<> <tbody> </tbody>

<tbody>
</tbody>

Many thanks for .....with me.</f2,-100,0))
</f2,-100,0))
</f2,-100,0))>

 

[Claus8528]

The answer in H4 shouldn't be -100 since B4>E4. The first IF-function returns True, which returns 100.

Or am I missing something?

 

[hsandeep]

The answer in H4 shouldn't be -100 since B4>E4. The first IF-function returns True, which returns 100.

Or am I missing something?

Actually my formula is wrong. IF C is non zero than it should check IF C is greater than F<f &="" return="" -100="" but="" if="" c="" is="" zero="" than="" proceed="" to="" check="" b="" non=""> & return -100 & IF C is zero than it should check IF B is greater than E & return 100 else 0</f>

 

[hsandeep]

Correct formula required: IF C is non zero than it should check F & IF C is greater than F<f &="" return="" -100="" but="" if="" c="" is="" zero="" than="" proceed="" to="" check="" b="" non=""> then return -100 BUT IF C is zero AND IF B is greater than E then return 100 else 0</f>

 

[Claus8528]

Correct formula required: IF C is non zero than it should check F & IF C is greater than F<f &="" return="" -100="" but="" if="" c="" is="" zero="" than="" proceed="" to="" check="" b="" non=""> then return -100 BUT IF C is zero AND IF B is greater than E then return 100 else 0</f>

Try this:
=IF(AND(C4<>0,C4>F4),-100,IF(AND(C4=0,B4>E4),100,0))

 

[hsandeep]

Try this:
=IF(AND(C4<>0,C4>F4),-100,IF(AND(C4=0,B4>E4),100,0))

Got it. Thanks Claus8528

 

[Claus8528]

you're welcome