Trying to Round Down or Up depending on values after decimal

[mixmstr]

Hi there, I have a spreadsheet I’m working on for employees worked hours. Depending on values after decimal, I want to either round down or up. Example: If value after decimal is (between .01-.24,round to 0),(between .25-.49,round to .25),(between .50-.74, rounds to .50),(between .75-.90, rounds to .75), and (between .90-.99, rounds up to 1 or next whole number).

So 5.24=5.00

5.40=5.25

5.60=5.50

5.90=5.75

5.91=6.00

Thank you for any help.

 

[Cubist]

Which version of XL are you using?

 

[lrobbo314]

This seems to work.

Ticket Split.xlsm
	F	G
1	5.01	5
2	5.02	5
3	5.03	5
4	5.04	5
5	5.05	5
6	5.06	5
7	5.07	5
8	5.08	5
9	5.09	5
10	5.1	5
11	5.11	5
12	5.12	5
13	5.13	5
14	5.14	5
15	5.15	5
16	5.16	5
17	5.17	5
18	5.18	5
19	5.19	5
20	5.2	5
21	5.21	5
22	5.22	5
23	5.23	5
24	5.24	5
25	5.25	5.25
26	5.26	5.25
27	5.27	5.25
28	5.28	5.25
29	5.29	5.25
30	5.3	5.25
31	5.31	5.25
32	5.32	5.25
33	5.33	5.25
34	5.34	5.25
35	5.35	5.25
36	5.36	5.25
37	5.37	5.25
38	5.38	5.25
39	5.39	5.25
40	5.4	5.25
41	5.41	5.25
42	5.42	5.25
43	5.43	5.25
44	5.44	5.25
45	5.45	5.25
46	5.46	5.25
47	5.47	5.25
48	5.48	5.25
49	5.49	5.25
50	5.5	5.5
51	5.51	5.5
52	5.52	5.5
53	5.53	5.5
54	5.54	5.5
55	5.55	5.5
56	5.56	5.5
57	5.57	5.5
58	5.58	5.5
59	5.59	5.5
60	5.6	5.5
61	5.61	5.5
62	5.62	5.5
63	5.63	5.5
64	5.64	5.5
65	5.65	5.5
66	5.66	5.5
67	5.67	5.5
68	5.68	5.5
69	5.69	5.5
70	5.7	5.5
71	5.71	5.5
72	5.72	5.5
73	5.73	5.5
74	5.74	5.5
75	5.75	5.75
76	5.76	5.75
77	5.77	5.75
78	5.78	5.75
79	5.79	5.75
80	5.8	5.75
81	5.81	5.75
82	5.82	5.75
83	5.83	5.75
84	5.84	5.75
85	5.85	5.75
86	5.86	5.75
87	5.87	5.75
88	5.88	5.75
89	5.89	5.75
90	5.9	5.75
91	5.91	6
92	5.92	6
93	5.93	6
94	5.94	6
95	5.95	6
96	5.96	6
97	5.97	6
98	5.98	6
99	5.99	6
100	6	6
Sheet1

Cell Formulas
Range	Formula
F1:F100	F1	=5+SEQUENCE(100)/100
G1:G100	G1	=LET(i,INT(F1#),f,F1#-i,n,INT(f/0.25)*0.25,(i+n)+((f>0.9)*0.25))
Dynamic array formulas.

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Another option

=FLOOR.MATH(A2,0.25)+IF(MOD(A2,1)>0.9,0.25,0)

 

[mixmstr]

Im using

Which version of XL are you using?

Office 365

 

[mixmstr]

This seems to work.

Ticket Split.xlsm
	F	G
1	5.01	5
2	5.02	5
3	5.03	5
4	5.04	5
5	5.05	5
6	5.06	5
7	5.07	5
8	5.08	5
9	5.09	5
10	5.1	5
11	5.11	5
12	5.12	5
13	5.13	5
14	5.14	5
15	5.15	5
16	5.16	5
17	5.17	5
18	5.18	5
19	5.19	5
20	5.2	5
21	5.21	5
22	5.22	5
23	5.23	5
24	5.24	5
25	5.25	5.25
26	5.26	5.25
27	5.27	5.25
28	5.28	5.25
29	5.29	5.25
30	5.3	5.25
31	5.31	5.25
32	5.32	5.25
33	5.33	5.25
34	5.34	5.25
35	5.35	5.25
36	5.36	5.25
37	5.37	5.25
38	5.38	5.25
39	5.39	5.25
40	5.4	5.25
41	5.41	5.25
42	5.42	5.25
43	5.43	5.25
44	5.44	5.25
45	5.45	5.25
46	5.46	5.25
47	5.47	5.25
48	5.48	5.25
49	5.49	5.25
50	5.5	5.5
51	5.51	5.5
52	5.52	5.5
53	5.53	5.5
54	5.54	5.5
55	5.55	5.5
56	5.56	5.5
57	5.57	5.5
58	5.58	5.5
59	5.59	5.5
60	5.6	5.5
61	5.61	5.5
62	5.62	5.5
63	5.63	5.5
64	5.64	5.5
65	5.65	5.5
66	5.66	5.5
67	5.67	5.5
68	5.68	5.5
69	5.69	5.5
70	5.7	5.5
71	5.71	5.5
72	5.72	5.5
73	5.73	5.5
74	5.74	5.5
75	5.75	5.75
76	5.76	5.75
77	5.77	5.75
78	5.78	5.75
79	5.79	5.75
80	5.8	5.75
81	5.81	5.75
82	5.82	5.75
83	5.83	5.75
84	5.84	5.75
85	5.85	5.75
86	5.86	5.75
87	5.87	5.75
88	5.88	5.75
89	5.89	5.75
90	5.9	5.75
91	5.91	6
92	5.92	6
93	5.93	6
94	5.94	6
95	5.95	6
96	5.96	6
97	5.97	6
98	5.98	6
99	5.99	6
100	6	6
Sheet1

Cell Formulas
Range	Formula
F1:F100	F1	=5+SEQUENCE(100)/100
G1:G100	G1	=LET(i,INT(F1#),f,F1#-i,n,INT(f/0.25)*0.25,(i+n)+((f>0.9)*0.25))
Dynamic array formulas.

Thank you for the reply. I will give it a shot when I get home

 

[mixmstr]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Another option

=FLOOR.MATH(A2,0.25)+IF(MOD(A2,1)>0.9,0.25,0)

Thank you for the reply. I just updated account details to show office 365. I will give your option a try.

 

[Fluff]

I just updated account details to show office 365.

Thanks for that.

 

[mixmstr]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Another option

=FLOOR.MATH(A2,0.25)+IF(MOD(A2,1)>0.9,0.25,0)

This worked!! Thank you for this. Much appreciated.

 

[mixmstr]

This seems to work.

Ticket Split.xlsm
	F	G
1	5.01	5
2	5.02	5
3	5.03	5
4	5.04	5
5	5.05	5
6	5.06	5
7	5.07	5
8	5.08	5
9	5.09	5
10	5.1	5
11	5.11	5
12	5.12	5
13	5.13	5
14	5.14	5
15	5.15	5
16	5.16	5
17	5.17	5
18	5.18	5
19	5.19	5
20	5.2	5
21	5.21	5
22	5.22	5
23	5.23	5
24	5.24	5
25	5.25	5.25
26	5.26	5.25
27	5.27	5.25
28	5.28	5.25
29	5.29	5.25
30	5.3	5.25
31	5.31	5.25
32	5.32	5.25
33	5.33	5.25
34	5.34	5.25
35	5.35	5.25
36	5.36	5.25
37	5.37	5.25
38	5.38	5.25
39	5.39	5.25
40	5.4	5.25
41	5.41	5.25
42	5.42	5.25
43	5.43	5.25
44	5.44	5.25
45	5.45	5.25
46	5.46	5.25
47	5.47	5.25
48	5.48	5.25
49	5.49	5.25
50	5.5	5.5
51	5.51	5.5
52	5.52	5.5
53	5.53	5.5
54	5.54	5.5
55	5.55	5.5
56	5.56	5.5
57	5.57	5.5
58	5.58	5.5
59	5.59	5.5
60	5.6	5.5
61	5.61	5.5
62	5.62	5.5
63	5.63	5.5
64	5.64	5.5
65	5.65	5.5
66	5.66	5.5
67	5.67	5.5
68	5.68	5.5
69	5.69	5.5
70	5.7	5.5
71	5.71	5.5
72	5.72	5.5
73	5.73	5.5
74	5.74	5.5
75	5.75	5.75
76	5.76	5.75
77	5.77	5.75
78	5.78	5.75
79	5.79	5.75
80	5.8	5.75
81	5.81	5.75
82	5.82	5.75
83	5.83	5.75
84	5.84	5.75
85	5.85	5.75
86	5.86	5.75
87	5.87	5.75
88	5.88	5.75
89	5.89	5.75
90	5.9	5.75
91	5.91	6
92	5.92	6
93	5.93	6
94	5.94	6
95	5.95	6
96	5.96	6
97	5.97	6
98	5.98	6
99	5.99	6
100	6	6
Sheet1

Cell Formulas
Range	Formula
F1:F100	F1	=5+SEQUENCE(100)/100
G1:G100	G1	=LET(i,INT(F1#),f,F1#-i,n,INT(f/0.25)*0.25,(i+n)+((f>0.9)*0.25))
Dynamic array formulas.

I couldn't get this to work as I wasn't sure what i & n values should be.