Possible to round up values to set amounts?

[Fred45]

I was wondering if it's possible to round up a column of numbers to the nearest .05, or .09?

They will all be prices. So for example some of them could be

4.33
3.26
2.94
2.96

I would want these rounded up to:

4.35
3.29
2.95
2.99

Thanks!

[LJMetzger]

Hi Fred45,

Try the following in a cell:

Please Login or Register  to view this content.

The formulae multiply by 100,
then roundup to 1 digit (e.g. 324 becomes 330)
then divides by 100 (multiply by 0.01)
then adds the last digit.

Lewis

[newdoverman]

This seems to work with limited testing. You didn't supply a minimum value to which this would apply so I omitted one:

Please Login or Register  to view this content.

[shg]

	a	b	c
1	0.19	0.19	b1: =floor(a1+0.001, 0.1) + lookup(mod(a1+0.001, 0.1), {0,5}%, {5,9}%)
2	7.14	7.15
3	2.75	2.79
4	7.95	7.99
5	2.67	2.69
6	2.90	2.95
7	9.46	9.49
8	7.04	7.05
9	2.26	2.29
10	9.65	9.69
11	4.32	4.35
12	7.76	7.79
13	4.27	4.29
14	7.09	7.09
15	9.91	9.95
16	1.39	1.39
17	2.85	2.89
18	4.97	4.99
19	2.41	2.45

[AlKey]

Or:

=IF(OR(--RIGHT(A1)={5,9}),A1,--SUBSTITUTE(A1,RIGHT(A1),IF(--RIGHT(A1)<5,5,9)))

	A	B
1	4.33	4.55
2	3.26	3.29
3	2.94	2.95
4	2.96	2.99
5	4.35	4.35
6	3.29	3.29
7	2.95	2.95
8	2.99	2.99
9	2.99	2.99
10	2.98	2.99
11	2.97	2.99
12	3.55	3.55

[Fred45]

Thanks guys. Glad I asked as I would never have figured this one out!

shg's seems to be perfect for what I was after with only one slight flaw. If the number is already at .x5, it will round up to .x9

So on that table, 2.85 rounds to 2.89 and 9.65 rounds to 9.69, but I'd rather they stayed put as they are already at .x5. Is there any sort of amendment that might solve that?

[shg]

=floor(a1+1%%, 0.1) + lookup(mod(a1+1%%, 0.1), {0,6}%, {5,9}%)

[AlKey]

I think my formula already does it.

[shg]

I think your formula has a problem with whole numbers, Al.

[newdoverman]

This should do what you want:

	A	B	C
1	$4.33	$4.35	=IF(RIGHT(A1,1)*1<=5,CEILING(A1,0.05),IF(RIGHT(A1,1)*1>5,CEILING(A1,0.0997)))
2	$3.26	$3.29
3	$2.94	$2.95
4	$2.96	$2.99
5	$2.90	$2.99
6	$2.91	$2.95
7	$2.92	$2.95
8	$2.93	$2.95
9	$2.94	$2.95
10	$2.95	$2.95
11	$2.96	$2.99
12	$2.97	$2.99
13	$2.98	$2.99
14	$2.99	$2.99
15	$3.00	$3.00

[shg]

$2.90 -> $2.99 ? If so, I misunderstood.

[Fred45]

Alkey's formula did do it, but yes it seemed to round up whole numbers to more whole numbers if they were already whole... if that makes sense.

shg's last one seems to do the trick!

[AlKey]

Hmm I din't see any whole numbers but here is the adjusted formula

=IF(MOD(A1,1)=0,A1,IF(OR(--RIGHT(A1)={5,9}),A1,--SUBSTITUTE(A1,RIGHT(A1),IF(--RIGHT(A1)<5,5,9))))

[shg]

	a	b	c
1	100.10	500.50	b1: =if(mod(a1,1)=0,a1,if(or(--right(a1)={5,9}),a1,--substitute(a1,right(a1),if(--right(a1)<5,5,9))))

[vlady]

how about

Please Login or Register  to view this content.