Need help w/ Nested If/Then Statement

[grey9192]

Need help creating a formula that will calculate an appropriate payout % given a specific achievement %. It needs to fit the parameters below. I can’t seem to get my if/then formula to be completely correct, especially for %s above 100.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>

<TABLE class=MsoNormalTable style="MARGIN: auto auto auto -0.65pt; WIDTH: 129pt; BORDER-COLLAPSE: collapse; mso-yfti-tbllook: 1184; mso-padding-alt: 0in 5.4pt 0in 5.4pt" cellSpacing=0 cellPadding=0 width=172 border=0><TBODY><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 0; mso-yfti-firstrow: yes"><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: windowtext 1pt solid; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: windowtext 1pt solid; WIDTH: 80pt; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent; mso-border-alt: solid windowtext .5pt" vAlign=bottom noWrap width=107>

Achevement %<o></o>

​

</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: windowtext 1pt solid; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 49pt; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent; mso-border-top-alt: solid windowtext .5pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt" vAlign=bottom noWrap width=65>

Payout %<o></o>

​

</TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 1"><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: windowtext 1pt solid; WIDTH: 80pt; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt" vAlign=bottom noWrap width=107>

less than 80%<o></o>

​

</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 49pt; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt" vAlign=bottom noWrap width=65>

0<o></o>

​

</TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 2"><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: windowtext 1pt solid; WIDTH: 80pt; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt" vAlign=bottom noWrap width=107>

80%<o></o>

​

</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 49pt; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt" vAlign=bottom noWrap width=65>

50%<o></o>

​

</TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 3"><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: windowtext 1pt solid; WIDTH: 80pt; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt" vAlign=bottom noWrap width=107>

100%<o></o>

​

</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 49pt; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt" vAlign=bottom noWrap width=65>

100%<o></o>

​

</TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 4"><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: windowtext 1pt solid; WIDTH: 80pt; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt" vAlign=bottom noWrap width=107>

250%<o></o>

​

</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 49pt; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt" vAlign=bottom noWrap width=65>

230%<o></o>

​

</TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 5; mso-yfti-lastrow: yes"><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: windowtext 1pt solid; WIDTH: 80pt; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt" vAlign=bottom noWrap width=107>

more than 250%<o></o>

​

</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 49pt; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt" vAlign=bottom noWrap width=65>

230%<o></o>​

</TD></TR></TBODY></TABLE><o></o>​

Thanks!<o></o>

 

[Misca]

Use VLOOKUP instead of several IF statements:

Change the "Less than 80 %" to 0 and have the minimum values in the left column.

The formula should be something like

=VLOOKUP($C$10,$A$1:$B$4,2,TRUE)

Where you change the cell references to match your cell addresses.

 

[grey9192]

But would that vlookup be able to calculate a payout % for an achievement % that falls between the given specs? For example, would that vlookup be able to calculate a payout for an achievement of 139% or 92%?

 

[grey9192]

Perhaps I wasn't completely clear when I origianlly posted my question. The forumula needs to be able to calculate for any achievement percentage, the chart I provided simply provides some parameters.

Your help is appreciated!

 

[Misca]

Why don't you try it?

The TRUE at the end means it'll look for not only exact matches but closest (smaller) matches. That's why you should use the minimum values on the left column and the values have to be in ascending order.

 

[grey9192]

Maximum portion of Calculation

If I use your vlookup method, that would mean I need to create a pretty large table for the formula to look to, correct? For example, I would need to list every single percentage point between 80 and 250 and their corresponding payout percentages. What I'm looking for is a formula that will calculate the payout percentage by simply looking at another percentage.

Here is what I did when my specs were: a minimum of 80% achievement is needed to receive any payout, in this case 50%. 100% achievement receieves 100% payout. And achievement of 130% or greater receieves a maximum payout of 175%. The formula would calculate appropriately for ANY achievement %.

<TABLE style="WIDTH: 354pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=472 border=0><COLGROUP><COL style="WIDTH: 49pt; mso-width-source: userset; mso-width-alt: 2377" width=65><COL style="WIDTH: 305pt; mso-width-source: userset; mso-width-alt: 14884" width=407><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 49pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=65 height=20>Achieved</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 305pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=407>Payout Formula</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl66 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>100%</TD><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">=IF(A3<0.8,0,IF(((A3-0.8)*2.5)+0.5>1.75,1.75,((A3-0.8)*2.5)+0.5))
</TD></TR></TBODY></TABLE>


What I need the new formula to do is change the maximum portion. I need it to now calculate any achievement of 250 or greater gets a maximum payout of 230% and all the other %s between 100 and 250 to calculate correctly now the the range has changed on the high end.

Thank You Misca for helping me work through this. I realize my explanations may not be clear. =)