snipt

Ctrl+h for KB shortcuts

Text only

Excel formula to format pricing based on business rules

1
2
3
4
5
6
7
-- Alter prices to fit business pricing rules
-- < .99 - rounds up to .x9 - .39, .99
-- < 9.99 - rounds up to .99 - 7.99, 8.99
-- < 99.99 - rounds up to .49 or .99 - 12.49, 49.99
-- > 100 - rounds down to .00 - 101.00, 459.00

=INT(A1)&IF(A1>=100,".00", IF(A1>=10,IF(RIGHT(A1,2)*1 > 49, ".99", ".49"), IF(A1>=1, ".99", TEXT(ROUNDUP(RIGHT(A1,2)/100,1)-0.01,".00"))))
https://snipt.net/embed/497f35eff11a970046435612271d7e58/
/raw/497f35eff11a970046435612271d7e58/
497f35eff11a970046435612271d7e58
text
Text only
7
2019-06-26T02:13:02
True
False
False
/api/public/snipt/15532/
excel-formula-to-format-pricing-based-on-business-rules
<table class="highlighttable"><tr><td class="linenos"><div class="linenodiv"><pre><a href="#L-1">1</a> <a href="#L-2">2</a> <a href="#L-3">3</a> <a href="#L-4">4</a> <a href="#L-5">5</a> <a href="#L-6">6</a> <a href="#L-7">7</a></pre></div></td><td class="code"><div class="highlight"><pre><span></span><span id="L-1"><a name="L-1"></a>-- Alter prices to fit business pricing rules </span><span id="L-2"><a name="L-2"></a>-- &lt; .99 - rounds up to .x9 - .39, .99 </span><span id="L-3"><a name="L-3"></a>-- &lt; 9.99 - rounds up to .99 - 7.99, 8.99 </span><span id="L-4"><a name="L-4"></a>-- &lt; 99.99 - rounds up to .49 or .99 - 12.49, 49.99 </span><span id="L-5"><a name="L-5"></a>-- &gt; 100 - rounds down to .00 - 101.00, 459.00 </span><span id="L-6"><a name="L-6"></a> </span><span id="L-7"><a name="L-7"></a>=INT(A1)&amp;IF(A1&gt;=100,&quot;.00&quot;, IF(A1&gt;=10,IF(RIGHT(A1,2)*1 &gt; 49, &quot;.99&quot;, &quot;.49&quot;), IF(A1&gt;=1, &quot;.99&quot;, TEXT(ROUNDUP(RIGHT(A1,2)/100,1)-0.01,&quot;.00&quot;)))) </span></pre></div> </td></tr></table>
excel, formatting, pricing