IF formula

[jevi]

Hello:

I am trying to get the following result, as you can see the formula for the Performance 2, is right when the target is outstanding and not NPL, but in this case I would like the formula to give the following result: that in Performance 2: SME NPL: -200 and Consumer NPL: 76.16

The formula for the performance 2 is as follow:

=IF(C7="","",IF(C7=0,IF(D7>0,200),MIN(200,D7*100/C7)))

<TABLE style="WIDTH: 436pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=581 border=0><COLGROUP><COL style="WIDTH: 48pt" span=2 width=64><COL style="WIDTH: 62pt; mso-width-source: userset; mso-width-alt: 3035" width=83><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 91pt; mso-width-source: userset; mso-width-alt: 4425" width=121><COL style="WIDTH: 57pt; mso-width-source: userset; mso-width-alt: 2779" width=76><COL style="WIDTH: 82pt; mso-width-source: userset; mso-width-alt: 3986" width=109><TBODY><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD class=xl63 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext 1pt solid; WIDTH: 48pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=64 height=20> </TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 48pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=64></TD><TD class=xl64 style="BORDER-RIGHT: #969696 0.5pt solid; BORDER-TOP: #969696 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 62pt; BORDER-BOTTOM: #969696 0.5pt solid; BACKGROUND-COLOR: transparent" width=83 rowSpan=2>TARGETS
(C)
</TD><TD class=xl65 style="BORDER-RIGHT: #7f7f7f 0.5pt solid; BORDER-TOP: #969696 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 48pt; BORDER-BOTTOM: #7f7f7f 0.5pt solid; BACKGROUND-COLOR: transparent" width=64 rowSpan=2> NPL
(D)
</TD><TD class=xl66 style="BORDER-RIGHT: #7f7f7f 0.5pt solid; BORDER-TOP: #969696 0.5pt solid; BORDER-LEFT: #7f7f7f 0.5pt solid; WIDTH: 91pt; BORDER-BOTTOM: #7f7f7f 0.5pt solid; BACKGROUND-COLOR: transparent" width=121 rowSpan=2>PERFORMANCE<SUP>2</SUP></TD><TD class=xl66 style="BORDER-RIGHT: #7f7f7f 0.5pt solid; BORDER-TOP: #969696 0.5pt solid; BORDER-LEFT: #7f7f7f 0.5pt solid; WIDTH: 57pt; BORDER-BOTTOM: #7f7f7f 0.5pt solid; BACKGROUND-COLOR: transparent" width=76 rowSpan=2>GRAVITY
WEIGHT</TD><TD class=xl66 style="BORDER-RIGHT: #7f7f7f 0.5pt solid; BORDER-TOP: #969696 0.5pt solid; BORDER-LEFT: #7f7f7f 0.5pt solid; WIDTH: 82pt; BORDER-BOTTOM: #7f7f7f 0.5pt solid; BACKGROUND-COLOR: transparent" width=109 rowSpan=2>PERFORMANCE
POINTS</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl63 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20> </TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 14.25pt; mso-height-source: userset" height=19><TD class=xl70 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: #d4d0c8; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent; mso-ignore: colspan" colSpan=2 height=19>SME NPL</TD><TD class=xl71 style="BORDER-RIGHT: gray 0.5pt solid; BORDER-TOP: gray 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: gray 0.5pt solid; BACKGROUND-COLOR: transparent">14.1%</TD><TD class=xl72 style="BORDER-RIGHT: #7f7f7f 0.5pt solid; BORDER-TOP: #7f7f7f; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: #7f7f7f 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>50.5%</TD><TD class=xl73 style="BORDER-RIGHT: #7f7f7f 0.5pt solid; BORDER-TOP: #7f7f7f 0.5pt solid; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BACKGROUND: #339966; BORDER-LEFT: #7f7f7f 0.5pt solid; COLOR: white; BORDER-BOTTOM: #7f7f7f 0.5pt solid; FONT-FAMILY: Arial; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none">200.00</TD><TD class=xl74 style="BORDER-RIGHT: #7f7f7f 0.5pt solid; BORDER-TOP: #7f7f7f; BORDER-LEFT: #7f7f7f; BORDER-BOTTOM: #7f7f7f 0.5pt solid; BACKGROUND-COLOR: transparent">50</TD><TD class=xl74 style="BORDER-RIGHT: #7f7f7f 0.5pt solid; BORDER-TOP: #7f7f7f; BORDER-LEFT: #7f7f7f; BORDER-BOTTOM: #7f7f7f 0.5pt solid; BACKGROUND-COLOR: transparent">10000</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD class=xl70 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent; mso-ignore: colspan" colSpan=2 height=21> Consumer NPL</TD><TD class=xl75 style="BORDER-RIGHT: #969696 0.5pt solid; BORDER-TOP: gray; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: gray 0.5pt solid; BACKGROUND-COLOR: transparent">1.3%</TD><TD class=xl77 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right>1.0%</TD><TD class=xl73 style="BORDER-RIGHT: #7f7f7f 0.5pt solid; BORDER-TOP: #7f7f7f 0.5pt solid; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BACKGROUND: #339966; BORDER-LEFT: #7f7f7f 0.5pt solid; COLOR: white; BORDER-BOTTOM: #7f7f7f 0.5pt solid; FONT-FAMILY: Arial; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none">76.16</TD><TD class=xl76 style="BORDER-RIGHT: #7f7f7f 0.5pt solid; BORDER-TOP: #7f7f7f; BORDER-LEFT: #7f7f7f; BORDER-BOTTOM: #7f7f7f 0.5pt solid; BACKGROUND-COLOR: transparent">50</TD><TD class=xl74 style="BORDER-RIGHT: #7f7f7f 0.5pt solid; BORDER-TOP: #7f7f7f; BORDER-LEFT: #7f7f7f; BORDER-BOTTOM: #7f7f7f 0.5pt solid; BACKGROUND-COLOR: transparent">3808</TD></TR></TBODY></TABLE>


thank you,

 

[Gerald Higgins]

Hi, sorry, it's not clear to me exactly what you want.

Can you explain more clearly please ?

 

[jevi]

I am sorry for not a good explanation. Let's try !


I would like that the formula =IF(C7="","",IF(C7=0,IF(D7>0,200),MIN(200,D7*100/C7))) , can give the result that when D7>C7= the result should be NEGATIV, when D7<C7= p POSITIV. in be should result the <> <=c7, the result can be POSITIV.
So for in the following example the result that I want would be:

<TABLE style="WIDTH: 436pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=581 border=0><COLGROUP><COL style="WIDTH: 48pt" span=2 width=64><COL style="WIDTH: 62pt; mso-width-source: userset; mso-width-alt: 3035" width=83><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 91pt; mso-width-source: userset; mso-width-alt: 4425" width=121><COL style="WIDTH: 57pt; mso-width-source: userset; mso-width-alt: 2779" width=76><COL style="WIDTH: 82pt; mso-width-source: userset; mso-width-alt: 3986" width=109><TBODY><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD class=xl65 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext 1pt solid; WIDTH: 48pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=64 height=20></TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 48pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=64></TD><TD class=xl66 style="BORDER-RIGHT: #969696 0.5pt solid; BORDER-TOP: #969696 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 62pt; BORDER-BOTTOM: #969696 0.5pt solid; BACKGROUND-COLOR: transparent" width=83 rowSpan=2>TARGETS</TD><TD class=xl67 style="BORDER-RIGHT: #7f7f7f 0.5pt solid; BORDER-TOP: #969696 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 48pt; BORDER-BOTTOM: #7f7f7f 0.5pt solid; BACKGROUND-COLOR: transparent" width=64 rowSpan=2>Feb NPL</TD><TD class=xl68 style="BORDER-RIGHT: #7f7f7f 0.5pt solid; BORDER-TOP: #969696 0.5pt solid; BORDER-LEFT: #7f7f7f 0.5pt solid; WIDTH: 91pt; BORDER-BOTTOM: #7f7f7f 0.5pt solid; BACKGROUND-COLOR: transparent" width=121 rowSpan=2>PERFORMANCE<SUP>2</SUP></TD><TD class=xl68 style="BORDER-RIGHT: #7f7f7f 0.5pt solid; BORDER-TOP: #969696 0.5pt solid; BORDER-LEFT: #7f7f7f 0.5pt solid; WIDTH: 57pt; BORDER-BOTTOM: #7f7f7f 0.5pt solid; BACKGROUND-COLOR: transparent" width=76 rowSpan=2>GRAVITY
WEIGHT
</TD><TD class=xl68 style="BORDER-RIGHT: #7f7f7f 0.5pt solid; BORDER-TOP: #969696 0.5pt solid; BORDER-LEFT: #7f7f7f 0.5pt solid; WIDTH: 82pt; BORDER-BOTTOM: #7f7f7f 0.5pt solid; BACKGROUND-COLOR: transparent" width=109 rowSpan=2>PERFORMANCE
POINTS
</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl65 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20></TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 14.25pt; mso-height-source: userset" height=19><TD class=xl72 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: #d4d0c8; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent; mso-ignore: colspan" colSpan=2 height=19>SME NPL</TD><TD class=xl73 style="BORDER-RIGHT: gray 0.5pt solid; BORDER-TOP: gray 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: gray 0.5pt solid; BACKGROUND-COLOR: transparent">14.1%</TD><TD class=xl74 style="BORDER-RIGHT: #7f7f7f 0.5pt solid; BORDER-TOP: #7f7f7f; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: #7f7f7f 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>50.0%</TD><TD class=xl75 style="BORDER-RIGHT: #7f7f7f 0.5pt solid; BORDER-TOP: #7f7f7f 0.5pt solid; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BACKGROUND: red; BORDER-LEFT: #7f7f7f 0.5pt solid; COLOR: white; BORDER-BOTTOM: #7f7f7f 0.5pt solid; FONT-FAMILY: Arial; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none">-200.00</TD><TD class=xl76 style="BORDER-RIGHT: #7f7f7f 0.5pt solid; BORDER-TOP: #7f7f7f; BORDER-LEFT: #7f7f7f; BORDER-BOTTOM: #7f7f7f 0.5pt solid; BACKGROUND-COLOR: transparent">50</TD><TD class=xl76 style="BORDER-RIGHT: #7f7f7f 0.5pt solid; BORDER-TOP: #7f7f7f; BORDER-LEFT: #7f7f7f; BORDER-BOTTOM: #7f7f7f 0.5pt solid; BACKGROUND-COLOR: transparent">-1000</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD class=xl72 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent; mso-ignore: colspan" colSpan=2 height=21>Consumer NPL</TD><TD class=xl77 style="BORDER-RIGHT: #969696 0.5pt solid; BORDER-TOP: gray; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: gray 0.5pt solid; BACKGROUND-COLOR: transparent">1.3%</TD><TD class=xl79 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right>1.0%</TD><TD class=xl75 style="BORDER-RIGHT: #7f7f7f 0.5pt solid; BORDER-TOP: #7f7f7f 0.5pt solid; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BACKGROUND: #339966; BORDER-LEFT: #7f7f7f 0.5pt solid; COLOR: white; BORDER-BOTTOM: #7f7f7f 0.5pt solid; FONT-FAMILY: Arial; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none">76.16</TD><TD class=xl78 style="BORDER-RIGHT: #7f7f7f 0.5pt solid; BORDER-TOP: #7f7f7f; BORDER-LEFT: #7f7f7f; BORDER-BOTTOM: #7f7f7f 0.5pt solid; BACKGROUND-COLOR: transparent">50</TD><TD class=xl76 style="BORDER-RIGHT: #7f7f7f 0.5pt solid; BORDER-TOP: #7f7f7f; BORDER-LEFT: #7f7f7f; BORDER-BOTTOM: #7f7f7f 0.5pt solid; BACKGROUND-COLOR: transparent">3808</TD></TR></TBODY></TABLE>

thnx