Rounding Table Formula

[DS1974]

hi guys
i have a pricing template that i want to implement to round up price points

what's the formula please?

Between			Round Up To
$ 0.01	$ 0.27		$ 0.28
$ 0.29	$ 0.47		$ 0.48
$ 0.49	$ 0.67		$ 0.68
$ 0.69	$ 0.77		$ 0.78
$ 0.79	$ 0.86		$ 0.87
$ 0.88	$ 0.97		$ 0.98
eg
$ 27.01			$ 27.28
$ 27.70			$ 27.78

<colgroup><col span="3"><col></colgroup><tbody>
</tbody>

thanks guys - Darcy

 

[jorismoerings]

Hi,

try this:

Book1
	A	B	C	D	E	F
1	Between	$ -		Round Up To
2	$ 0,01	$ 0,27		$ 0,28
3	$ 0,29	$ 0,47		$ 0,48
4	$ 0,49	$ 0,67		$ 0,68
5	$ 0,69	$ 0,77		$ 0,78
6	$ 0,79	$ 0,86		$ 0,87
7	$ 0,88	$ 0,97		$ 0,98
8
9
10	eg
11	$ 27,01			$ 27,28		$ 27,28
12	$ 27,70			$ 27,78		$ 27,78
Sheet1

Cell Formulas
Range	Formula
F11	=TRUNC(A11)+LOOKUP(A11-TRUNC(A11),$B$1:$B$7,$D$2:$D$7)

 

[jtakw]

Hi,

Use my formula in F11 if you can modify your Table as shown in my sample, or use my formula in D11 which has the Table built into the formula:

Excel 2010
	A	B	C	D	F
1	Between			Round Up To
2	$0.01	$0.27		$0.28
3	$0.29	$0.47		$0.48
4	$0.49	$0.67		$0.68
5	$0.69	$0.77		$0.78
6	$0.79	$0.86		$0.87
7	$0.88	$0.97		$0.98
8	$0.99	$0.99		$0.99
9
10	eg
11	$27.00			$27.00	$27.00
12	$27.27			$27.28	$27.28
13	$27.28			$27.28	$27.28
14	$27.70			$27.78	$27.78
15	$27.97			$27.98	$27.98
16	$27.98			$27.98	$27.98
17	$27.99			$27.99	$27.99
Sheet8

Cell Formulas
Range	Formula
D11	=TRUNC(A11)+LOOKUP(ROUND(MOD(A11,1),2),{0,0.01,0.28,0.29,0.48,0.49,0.68,0.69,0.78,0.79,0.87,0.88,0.98,0.99},{0,0.28,0.28,0.48,0.48,0.68,0.68,0.78,0.78,0.87,0.87,0.98,0.98,0.99})
F11	=TRUNC(A11)+SUMIFS(D$2:D$8,A$2:A$8,"<="&ROUND(MOD(A11,1),2),D$2:D$8,">="&ROUND(MOD(A11,1),2))

Either formula copied down as needed.

FYI, the formula provided in Post #2 will fail with values like $27.00, 27.99, and at All Rounding points.