Need a conversion/range solution

[JDJohnson]

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?

 

[adam087]

Does this do what you need?

Excel 2010
	A	B	C
1	0.50%	Bar	-5
2	0.82%		0
3	1.44%	Server	0
4	2.08%	Server	0
5	2.76%		1
6	3.00%	Server	2
7	3.48%	Server	2
8	4.20%		4
9	4.80%	Server	5
10	5.34%		6
11	5.76%		7
12	6.01%		8
13	6.67%	Server	9
14	7.14%		10
15	7.68%	Bar	6
16	8.01%		12
17	8.61%	Server	13
18	8.92%	Bar	8
19	9.36%		14
Sheet1

Cell Formulas
Range	Formula
C1	=CEILING(MAX(A1-0.0249999, 0)/0.005, 1)-(5*(B1="Bar"))

Regards
Adam

 

[JDJohnson]

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]

Worksheet Formulas Cell Formula 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]

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]

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