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?

[TABLE="width: 298"]
<colgroup><col span="3"><col></colgroup><tbody>[TR]
[TD]Between[/TD]
[TD][/TD]
[TD][/TD]
[TD]Round Up To[/TD]
[/TR]
[TR]
[TD] $ 0.01[/TD]
[TD] $ 0.27[/TD]
[TD][/TD]
[TD] $ 0.28[/TD]
[/TR]
[TR]
[TD] $ 0.29[/TD]
[TD] $ 0.47[/TD]
[TD][/TD]
[TD] $ 0.48[/TD]
[/TR]
[TR]
[TD] $ 0.49[/TD]
[TD] $ 0.67[/TD]
[TD][/TD]
[TD] $ 0.68[/TD]
[/TR]
[TR]
[TD] $ 0.69[/TD]
[TD] $ 0.77[/TD]
[TD][/TD]
[TD] $ 0.78[/TD]
[/TR]
[TR]
[TD] $ 0.79[/TD]
[TD] $ 0.86[/TD]
[TD][/TD]
[TD] $ 0.87[/TD]
[/TR]
[TR]
[TD] $ 0.88[/TD]
[TD] $ 0.97[/TD]
[TD][/TD]
[TD] $ 0.98[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]eg [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD] $ 27.01[/TD]
[TD][/TD]
[TD][/TD]
[TD] $ 27.28[/TD]
[/TR]
[TR]
[TD] $ 27.70[/TD]
[TD][/TD]
[TD][/TD]
[TD] $ 27.78[/TD]
[/TR]
</tbody>[/TABLE]


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.