Need a conversion/range solution

JDJohnson

New Member
Joined
Jun 23, 2012
Messages
4
I am trying to take a percentage and for every .5 percent I want one point and I want it to round down, The catch is I want it to only start after 2.5%. Now I have two types of possibilities that are entered in texts. So for example you have a percentage of sales that you get points for. You dont get points unless you get over 2.5% and for every .5 percent you get 1 point. So 2.5-2.99 = 1 point, 3.0-3.49 = 2 points, ect. But if you have Server in a box you keep your value in points. If you have Bar you lose 5 points. Can anyone help with this equation?

I am thinking =IF(OR(d27="2.5 to 2.99"),"1",IF(OR(d27="3.0 to 3.49"),"2",IF(OR(d27="3.5 to3.99"))) to get started but how do i incorporate the Server or Bar Part?
 

Some videos you may like

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

adam087

Well-known Member
Joined
Jun 7, 2010
Messages
1,356
Does this do what you need?

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: right;;">0.50%</td><td style=";">Bar</td><td style="text-align: right;;">-5</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">0.82%</td><td style=";"></td><td style="text-align: right;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">1.44%</td><td style=";">Server</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">2.08%</td><td style=";">Server</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;">2.76%</td><td style=";"></td><td style="text-align: right;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;">3.00%</td><td style=";">Server</td><td style="text-align: right;;">2</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;">3.48%</td><td style=";">Server</td><td style="text-align: right;;">2</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;;">4.20%</td><td style=";"></td><td style="text-align: right;;">4</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;;">4.80%</td><td style=";">Server</td><td style="text-align: right;;">5</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: right;;">5.34%</td><td style=";"></td><td style="text-align: right;;">6</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: right;;">5.76%</td><td style=";"></td><td style="text-align: right;;">7</td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: right;;">6.01%</td><td style=";"></td><td style="text-align: right;;">8</td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="text-align: right;;">6.67%</td><td style=";">Server</td><td style="text-align: right;;">9</td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="text-align: right;;">7.14%</td><td style=";"></td><td style="text-align: right;;">10</td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style="text-align: right;;">7.68%</td><td style=";">Bar</td><td style="text-align: right;;">6</td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style="text-align: right;;">8.01%</td><td style=";"></td><td style="text-align: right;;">12</td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style="text-align: right;;">8.61%</td><td style=";">Server</td><td style="text-align: right;;">13</td></tr><tr ><td style="color: #161120;text-align: center;">18</td><td style="text-align: right;;">8.92%</td><td style=";">Bar</td><td style="text-align: right;;">8</td></tr><tr ><td style="color: #161120;text-align: center;">19</td><td style="text-align: right;;">9.36%</td><td style=";"></td><td style="text-align: right;;">14</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">C1</th><td style="text-align:left">=CEILING(<font color="Blue">MAX(<font color="Red">A1-0.0249999, 0</font>)/0.005, 1</font>)-(<font color="Blue">5*(<font color="Red">B1="Bar"</font>)</font>)</td></tr></tbody></table></td></tr></table><br />
Regards
Adam
 

JDJohnson

New Member
Joined
Jun 23, 2012
Messages
4
That is what I was looking for. I have a question and I am sorry I know the basic functions and am fairly new to all the possible functions and how to put them together but the points will start at 0 and never be negative. Also I have 3 sets of points to add together from percentages. For Example:
PositionAdd-On % (Weekly)Add-On PtsGuest Check Average (Weekly)Guest Check Average PtsDrink Contest % (Whole Contest)Drink Contest PtsTotal Pts
Serv/Bar2.98%1$14.7228.27%1215
Serv/Bar1.51%0$18.3594.99%514
Serv/Bar2.74%1$16.4755.28%612
Serv2.02%0$15.5946.27%812
Serv6.33%8$15.7642.11%012
Serv/Bar1.92%0$16.4355.74%712
Serv/Bar2.42%0$13.8707.03%1010
Serv1.94%0$12.5106.42%88
Serv1.65%0$12.4105.02%66
Serv3.05%2$15.1732.89%16
Serv2.36%0$15.3033.61%36

<colgroup><col width="64" span="8" style="width:48pt"> </colgroup><tbody>
</tbody>
 

JDJohnson

New Member
Joined
Jun 23, 2012
Messages
4
Worksheet Formulas
CellFormula
C1=CEILING(MAX(A1-0.0249999, 0)/0.005, 1)-(5*(B1="Bar"))

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>


Regards
Adam
Ok that worked perfect. Can I make it a little more complicated now? I will have the same type of point system for both the %'s it will look like this:
0%-2.49% = 0 point, 2.50%-2.99% = 1 point, 3.00%-3.49% = 2, etc
Also the dollar to points range would look like this:
$0.00-$13.99 = 0 points, $14.00-$14.49 = 1 point, $14.50-$14.99 = 2 point, etc
Now after all the points are converted in their own boxes I need to add them up and if they are a Bar they subtract 5 points, if they are a Serv they stay the same. The points never go negative though.
 

JDJohnson

New Member
Joined
Jun 23, 2012
Messages
4
Ok I have it all done but the total.
I used the formula =CEILING(MAX(D9-0.0249999, 0)/0.005, 1) for the Percentages,
For the dollars =CEILING(MAX(F11-13.999, 0)/0.5, 1)
In the totals I am getting some negative numbers and I need the total points to start at 0 and go up even if they are a Bar,
the formula I am using is =(E11+G11+I11)-(5*(B11="Bar"))
How would I make the total points 0 if it is a negative number in the last formula?
 

adam087

Well-known Member
Joined
Jun 7, 2010
Messages
1,356
Hi JD

Although there are other ways around it, I usually use MAX to stop a formula dropping a value below zero.
=MAX([your formula], 0).

MAX always returns the largest value, so if your formula is larger than 0, that's what you see. If your formula tries to return a number below zero then the MAX statement will return 0 because this higher.

Hope this helps.

Regards
Adam
 

Watch MrExcel Video

Forum statistics

Threads
1,127,919
Messages
5,627,624
Members
416,257
Latest member
salomon

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top