Formula without the basic IF

[andyb16]

Hi Guys,

I need to build a formula in such a way that each increment of 500,000 starting from 3.5 mil would make me eligible for a certain discount as shown in the below table. Bascically its a 0.5% increase on every 500,000. Also there is a stop at 7 mil...any value above that can only attract a discount of 9%. is there a way i can build this formula without an if. The IF formula i used, while it works is long and tedious.

Thanks!
<TABLE style="WIDTH: 224pt; BORDER-COLLAPSE: collapse; mso-padding-bottom-alt: 0in; mso-padding-left-alt: 0in; mso-padding-right-alt: 0in; mso-padding-top-alt: 0in; mso-yfti-tbllook: 1184" cellSpacing=0 cellPadding=0 width=298 border=0><COLGROUP><COL style="WIDTH: 107pt; mso-width-source: userset; mso-width-alt: 5193" width=142><COL style="WIDTH: 117pt; mso-width-source: userset; mso-width-alt: 5705" width=156><TBODY><TR style="HEIGHT: 13.5pt; mso-yfti-firstrow: yes; mso-yfti-irow: 0" height=18><TD class=xl154 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 107pt; PADDING-TOP: 0in; BORDER-BOTTOM: #ece9d8; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent" width=142 height=18>
Thresholds
</TD><TD class=xl154 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 117pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=156></SPAN>Percentages</SPAN></TD></TR><TR style="HEIGHT: 13.5pt; mso-yfti-irow: 1" height=18><TD class=xl155 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 107pt; PADDING-TOP: 0in; BORDER-BOTTOM: #ece9d8; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent" width=142 height=18> 3,500,000 </TD><TD class=xl156 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 117pt; PADDING-TOP: 0in; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=156>6.00%</SPAN></TD></TR><TR style="HEIGHT: 13.5pt; mso-yfti-irow: 2" height=18><TD class=xl154 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 107pt; PADDING-TOP: 0in; BORDER-BOTTOM: #ece9d8; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent" width=142 height=18> 4,000,000 </TD><TD class=xl157 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 117pt; PADDING-TOP: 0in; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=156>6.50%</SPAN></TD></TR><TR style="HEIGHT: 13.5pt; mso-yfti-irow: 3" height=18><TD class=xl154 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 107pt; PADDING-TOP: 0in; BORDER-BOTTOM: #ece9d8; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent" width=142 height=18> 4,500,000 </TD><TD class=xl157 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 117pt; PADDING-TOP: 0in; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=156>7.00%</SPAN></TD></TR><TR style="HEIGHT: 13.5pt; mso-yfti-irow: 4" height=18><TD class=xl154 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 107pt; PADDING-TOP: 0in; BORDER-BOTTOM: #ece9d8; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent" width=142 height=18> 5,000,000 </TD><TD class=xl157 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 117pt; PADDING-TOP: 0in; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=156>7.50%</SPAN></TD></TR><TR style="HEIGHT: 13.5pt; mso-yfti-irow: 5" height=18><TD class=xl154 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 107pt; PADDING-TOP: 0in; BORDER-BOTTOM: #ece9d8; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent" width=142 height=18> 5,500,000 </TD><TD class=xl157 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 117pt; PADDING-TOP: 0in; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=156>8.00%</SPAN></TD></TR><TR style="HEIGHT: 13.5pt; mso-yfti-irow: 6" height=18><TD class=xl154 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 107pt; PADDING-TOP: 0in; BORDER-BOTTOM: #ece9d8; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent" width=142 height=18> 6,000,000 </TD><TD class=xl157 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 117pt; PADDING-TOP: 0in; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=156>8.50%</SPAN></TD></TR><TR style="HEIGHT: 13.5pt; mso-yfti-irow: 7" height=18><TD class=xl154 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 107pt; PADDING-TOP: 0in; BORDER-BOTTOM: #ece9d8; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent" width=142 height=18> 6,500,000 </TD><TD class=xl157 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 117pt; PADDING-TOP: 0in; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=156>9.00%</SPAN></TD></TR><TR style="HEIGHT: 13.5pt; mso-yfti-irow: 8; mso-yfti-lastrow: yes" height=18><TD class=xl154 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 107pt; PADDING-TOP: 0in; BORDER-BOTTOM: #ece9d8; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent" width=142 height=18> 7,000,000 </TD><TD class=xl157 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 117pt; PADDING-TOP: 0in; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=156>9.00%</SPAN></TD></TR></TBODY></TABLE>

 

[WinteE]

Hi,

=IF(A2<3500000;6;IF(A2>6500000;9;(ROUNDUP(((A2-3500000)/500000);0)/2)+6))

Where Range A2 is the field where the amount is in.

Success

 

[mikerickson]

This will handle values (in A1) >= 3,500,000. You didn't say what you want for lower fiigures.

=MIN(FLOOR(0.025+A1/10^8, 0.005), 0.09)

 

[andyb16]

Thanks Both!