Round up number to nearest 0.5 or 0.9

[winstone88]

Hi Guys,

I need a help for the below problems that i am facing.

I need one formula or equation to fulfill the below equation.

1. if the last two decimal is 0.0 will remain us zero
2. if last two decimal is 0.01 - 0.49 , it will be round up to 0.50
3. if last two decimal is 0.51 - 0.89 , it will be round up to 0.90
4. if last two decimal is 0.91 - 0.99 , it will be round up to the nearest dollar


Thanks in advance for the effort in helping me solve this big problem of mine.

Best Regards,
Winston

[FlameRetired]

Hi winstone88. Welcome to the forum.

You don't say what you want to do if the last two decimals are already 0.50 or 0.90 so I assumed they remain the same.

Using a lookup table in D1:E3 does this resemble what you are looking for?

In B2 and filled down

Please Login or Register  to view this content.

	A	B	C	D	E
1	Source	Rounded		0.01	0.5
2	0.53	0.9		0.51	0.9
3	0.2	0.5		0.91	1
4	0.66	0.9
5	0.95	1
6	0.53	0.9
7	0.9	0.9
8	1.09	1.5
9	0.69	0.9
10	0.71	0.9
11	0.28	0.5
12	0.88	0.9
13	0.82	0.9
14	0.7	0.9
15	0.43	0.5
16	0.96	1
17	0.89	0.9
18	0.99	1
19	1.06	1.5
20	0.96	1

[Phuocam]

Or:

=LOOKUP(MOD(A2,1),{0,0.01,0.51,0.91},{0,0.5,0.9,1})+INT(A2)

[FlameRetired]

@Phuocam Yes, better ... don't forget about the zeros.

[sanram]

You can try this formula also :

Please Login or Register  to view this content.