# Need a conversion/range solution

#### JDJohnson

##### New Member
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?

### Excel Facts

If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

##### Well-known Member
Does this do what you need?

Regards

#### JDJohnson

##### New Member
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:
 Position Add-On % (Weekly) Add-On Pts Guest Check Average (Weekly) Guest Check Average Pts Drink Contest % (Whole Contest) Drink Contest Pts Total Pts Serv/Bar 2.98% 1 \$14.72 2 8.27% 12 15 Serv/Bar 1.51% 0 \$18.35 9 4.99% 5 14 Serv/Bar 2.74% 1 \$16.47 5 5.28% 6 12 Serv 2.02% 0 \$15.59 4 6.27% 8 12 Serv 6.33% 8 \$15.76 4 2.11% 0 12 Serv/Bar 1.92% 0 \$16.43 5 5.74% 7 12 Serv/Bar 2.42% 0 \$13.87 0 7.03% 10 10 Serv 1.94% 0 \$12.51 0 6.42% 8 8 Serv 1.65% 0 \$12.41 0 5.02% 6 6 Serv 3.05% 2 \$15.17 3 2.89% 1 6 Serv 2.36% 0 \$15.30 3 3.61% 3 6

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

#### JDJohnson

##### New Member
Worksheet Formulas
CellFormula
C1=CEILING(MAX(A1-0.0249999, 0)/0.005, 1)-(5*(B1="Bar"))

</tbody>

<tbody>
</tbody>

Regards
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
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?

##### Well-known Member
Hi JD

Although there are other ways around it, I usually use MAX to stop a formula dropping a value below zero.

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

Replies
1
Views
177
Replies
3
Views
127
Replies
5
Views
404
Replies
1
Views
65
Replies
4
Views
199

1,128,122
Messages
5,628,806
Members
416,342
Latest member
BlueDevil12

### 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.

### Which adblocker are you using?

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

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