If Then else in Excel

[Gulfcourse]

I used to use if then else in Crystal Reporting but haven't figured it out in Excel.
I have a column with rows of cells with different percentages in them, let's just say Column Q rows Q2 through Q30.
In Column T, T2 through T30 I would like to put a number based on whats in Q.
Tte numbers in Q range from 2% to 100% and what I want is if
the cell in column Q2 is between 90 and 100 the put 100 in T2
the cell in column Q2 is between 89 and 80 the put 90 in T2
the cell in column Q2 is between 79 and 70 the put 80 in T2
the cell in column Q2 is between 69 and 60 the put 70 in T2
the cell in column Q2 is between 59 and 50 the put 60 in T2
the cell in column Q2 is between 49 and 40 the put 50 in T2
the cell in column Q2 is between 39 and 30 the put 40 in T2
the cell in column Q2 is between 29 and 20 the put 30 in T2
the cell in column Q2 is between 19 and 10 the put 20 in T2
the cell in column Q2 is between 9 and 1 the put 10 in T2

and I would like to do that for all the rows, evaluating the cell in Q and putting the appropriate result in Q, and ideas?
I would like it to look like what I did manually in the image.

Thank you

 

[6StringJazzer]

So you are basically saying you want to round up to the next highest multiple of 10%.

See formula below. (Hint: this is not an If Then Else problem.)

I made up some quick fake data. For the future, consider pasting your actual data into a post rather than a screenshot.

$scratch.xlsm
	A	B
1	5%	10%
2	10.00%	10%
3	15.00%	20%
4	25.00%	30%
5	30.00%	30%
6	35.00%	40%
7	40.00%	40%
8	45.00%	50%
9	50.00%	50%
10	55.00%	60%
11	60.00%	60%
12	65.00%	70%
13	70.00%	70%
14	75.00%	80%
15	80.00%	80%
16	85.00%	90%
17	90.00%	90%
18	90.00%	90%
19	100.00%	100%
Sheet6

Cell Formulas
Range	Formula
B1:B19	B1	=MROUND(A1+0.001,0.1)

 

[Gulfcourse]

GMGOLF_NEW.xlsx
	I	J	K	L	M	N	O		Q		S	T		V
1	# OF ROUNDS	AVERAGE PER	$$ PUT INTO POT	WIN \ LOSS TOTAL	COST PER PER ROUND	% return	LAST		% of Rounds		# of Rounds	PAY %		NEW
2	36	$10.78	216	$172	$4.78	79.63%	Roderick		100.00%		36	100%		100.00%
4	24	$10.17	144	$100	$4.17	69.44%	Theberge		66.67%			70%		70.00%
6	14	$9.36	84	$47	$3.36	55.95%	Bonham		38.89%			40%		40.00%
7	16	$8.63	96	$42	$2.63	43.75%	Frederick		44.44%			50%		40.00%
8	25	$6.60	150	$15	$0.60	10.00%	Gressell		69.44%			70%		70.00%
9	26	$6.54	156	$14	$0.54	8.97%	Petro		72.22%			80%		70.00%
10	28	$6.54	168	$15	$0.54	8.93%	Edmisten		77.78%			80%		80.00%
11	34	$6.21	204	$7	$0.21	3.43%	Sepic		94.44%			100%		90.00%
12	34	$6.18	204	$6	$0.18	2.94%	Keep		94.44%			100%		90.00%
13	27	$5.37	162	-$17	-$0.63	-10.49%	Wallon		75.00%			80%		80.00%
14	33	$5.21	198	-$26	-$0.79	-13.13%	Zeman		91.67%			100%		90.00%
15	28	$5.07	168	-$26	-$0.93	-15.48%	Dangerfield		77.78%			80%		80.00%
16	31	$5.00	186	-$31	-$1.00	-16.67%	Hobbs		86.11%			90%		90.00%
17	26	$4.77	156	-$32	-$1.23	-20.51%	Kilroy		72.22%			80%		70.00%
18	2		12	-$4	-$2.00	-33.33%	Hibjan		5.56%			10%		10.00%
20	32	$3.47	192	-$81	-$2.53	-42.19%	Nassar		88.89%			90%		90.00%
21	3	$3.33	18	-$8	-$2.67	-44.44%	Gosnell		8.33%			10%		10.00%
22	24	$3.00	144	-$72	-$3.00	-50.00%	Nigro		66.67%			70%		70.00%
24	25	0.56	33	-19	-$0.76	-57.58%	Lincoln		69.44%			70%		70.00%
25	2	$2.00	12	-$8	-$4.00	-66.67%	Funari		5.56%			10%		10.00%
26	1	$0.00	6	-$6	-$6.00	-100.00%	Moderick		2.78%			10%		0.00%
STANDINGS

Cell Formulas
Range	Formula
L2,L4,L6:L18,L20:L22,L24:L26	L2	=SUM(H2-K2)
M2,M4,M6:M18,M20:M22,M24:M26	M2	=IF(I2,L2/I2,"")
N2,N4,N6:N18,N20:N22,N24:N26	N2	=L2/K2
Q2,Q4,Q6:Q18,Q20:Q22,Q24:Q26	Q2	=I2/$S$2
J2,J4,J6:J17,J24:J26,J20:J22	J2	=IFERROR(H2/I2,0)
I2	I2	=Playing!D24
I4	I4	=Playing!D27
I6,I10,I16,I22	I6	=Playing!D5
I7	I7	=Playing!D10
I8	I8	=Playing!D13
I9,I11	I9	=Playing!D23
I12	I12	=Playing!D16
I13:I14	I13	=Playing!D28
I15	I15	=Playing!D7
I17,I20	I17	=Playing!D17
I18	I18	=Playing!D14
K2,K4,K6:K18,K25:K26,K20:K22	K2	=SUM(I2*6)
I21	I21	=Playing!D12
I24	I24	=Playing!D18
I25	I25	=Playing!D11
I26	I26	=Playing!D19
V2,V4,V6:V18,V20:V22,V24:V26	V2	=MROUND(Q2+0.001,0.1)

 

[Gulfcourse]

So it worked, sort of if 90 to 100 it should be 100,
80 to 89, should be 90
70 to 79 should be 80
and so on, your original formula has the 90's as 90's instead of 100, everything else seems fine except for V25 which is 0 instead of ten.

Thank you very much!

 

[Scott Huish]

=MIN(100,ROUNDUP((A1+1)/10,0)*10)

 

[Gulfcourse]

Thanks Scott either that didn't work or I'm doing something wrong, which could be the case.
I pasted the formula you gave me into into W2, changed the formula to =MIN(100,ROUNDUP((Q2+1)/10,0)*10) and all I get in column W all the way down is the number 10.
I see your note about xl2bb, I thought I did that, is that simply a standard note or did I screw that up?
Thank you for the help

 

[Scott Huish]

Try this:
=IF(Q2="","",MIN(100,ROUNDUP((Q2+1)/10,0)*10))

 

[6StringJazzer]

This does the trick. EDIT: I suggest using CEILING.MATH but the result is the same.

$scratch.xlsm
	A	B
1	5%	10%
2	10.00%	20%
3	15.00%	20%
4	25.00%	30%
5	30.00%	40%
6	35.00%	40%
7	40.00%	50%
8	45.00%	50%
9	50.00%	60%
10	55.00%	60%
11	60.00%	70%
12	65.00%	70%
13	70.00%	80%
14	75.00%	80%
15	80.00%	90%
16	85.00%	90%
17	90.00%	100%
18	100.00%	100%
Sheet6

Cell Formulas
Range	Formula
B1:B18	B1	=MIN(1,CEILING(A1+0.001,0.1))

 

[6StringJazzer]

And here is your data with my formula in NEW

$scratch.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	# OF ROUNDS	AVERAGE PER	$$ PUT INTO POT	WIN \ LOSS TOTAL	COST PER PER ROUND	% return	LAST		% of Rounds		# of Rounds	PAY %		NEW
2	36	$10.78	216	$172	$4.78	79.63%	Roderick		100.00%		36	100%		100.00%
3
4	24	$10.17	144	$100	$4.17	69.44%	Theberge		66.67%			70%		70.00%
5
6	14	$9.36	84	$47	$3.36	55.95%	Bonham		38.89%			40%		40.00%
7	16	$8.63	96	$42	$2.63	43.75%	Frederick		44.44%			50%		50.00%
8	25	$6.60	150	$15	$0.60	10.00%	Gressell		69.44%			70%		70.00%
9	26	$6.54	156	$14	$0.54	8.97%	Petro		72.22%			80%		80.00%
10	28	$6.54	168	$15	$0.54	8.93%	Edmisten		77.78%			80%		80.00%
11	34	$6.21	204	$7	$0.21	3.43%	Sepic		94.44%			100%		100.00%
12	34	$6.18	204	$6	$0.18	2.94%	Keep		94.44%			100%		100.00%
13	27	$5.37	162	($17)	($0.63)	-10.49%	Wallon		75.00%			80%		80.00%
14	33	$5.21	198	($26)	($0.79)	-13.13%	Zeman		91.67%			100%		100.00%
15	28	$5.07	168	($26)	($0.93)	-15.48%	Dangerfield		77.78%			80%		80.00%
16	31	$5.00	186	($31)	($1.00)	-16.67%	Hobbs		86.11%			90%		90.00%
17	26	$4.77	156	($32)	($1.23)	-20.51%	Kilroy		72.22%			80%		80.00%
18	2		12	($4)	($2.00)	-33.33%	Hibjan		5.56%			10%		10.00%
19
20	32	$3.47	192	($81)	($2.53)	-42.19%	Nassar		88.89%			90%		90.00%
21	3	$3.33	18	($8)	($2.67)	-44.44%	Gosnell		8.33%			10%		10.00%
22	24	$3.00	144	($72)	($3.00)	-50.00%	Nigro		66.67%			70%		70.00%
23
24	25	0.56	33	-19	($0.76)	-57.58%	Lincoln		69.44%			70%		70.00%
25	2	$2.00	12	($8)	($4.00)	-66.67%	Funari		5.56%			10%		10.00%
26	1	$0.00	6	($6)	($6.00)	-100.00%	Moderick		2.78%			10%		10.00%
Sheet5

Cell Formulas
Range	Formula
N2,N24:N26,N20:N22,N6:N18,N4	N2	=MIN(1,CEILING.MATH(I2+0.001,0.1))

 

[Gulfcourse]

Thank you, Thank you, Thank you, I'm blown away at the help an novice Excel user gets at this site and I'm continually learning along the way.
The only problem is the more I learn the more I realize I don't know!
Thanks again to all for the help!