Rounding to a specific value based on range

[TC_Mike]

I’ve been using the following formula to calculate margin and do the rounding to the closest .x9
=ROUND(($A1/0.7),1)-0.01

Now there are certain price points that I want to hit (or avoid) so I’m trying to create a formula to only hit these price points. The formula would do the following,

Retails ending in …
x.00 to x.10 would round down to .99
x.11 to x.30 would round to x.29
x.31 to x.50 would round to x.49
x.51 to x.80 would round to x.79
x.81 to x.99 would round to x.99


If I have to calculate the margin separately, that would be OK, but I was trying to get it all done in one cell.

 

[DILIPandey]

Hi TC_Mike,

Try using below formula;-

=IF(MOD(A1,1)=0,INT(A1)-0.1,LOOKUP(IF(ISNUMBER(SEARCH(".",RIGHT(A1,2))),(RIGHT(A1,1)&0)*1,(RIGHT(A1,2)*1)),{11,31,51,81},{29,49,79,99}))


Regards,
DILIPandey

 

[Peter_SSs]

See if this does what you want.

Excel Workbook
	A	B
1	Number	Rounded
2	3.05	2.99
3	12.18	12.29
4	0.5	0.49
5	7.51	7.79
6	7.93	7.99
Rounding

 

[TC_Mike]

Thanks to DILIPandey & Peter_SSs for chiming in. Peter_SSs's formula worked best for me. Here are the side by side results of the two.

 

[DILIPandey]

Thanks for the heads- up Mike. This happens when one does'nt do a proper testing

Revising the formula as :-

=IF(OR(MOD(A1,1)=0,RIGHT(A1,3)*1<=0.1),((INT(A1)-1)&"."&99)*1,(INT(A1)&"."&LOOKUP(IF(ISNUMBER(SEARCH(".",RIGHT(A1,2))),(RIGHT(A1,1)&0)*1,(RIGHT(A1,2)*1)),{1,11,31,51,81},{99,29,49,79,99}))*1)

Which is giving correct results as per above data sample.


Regards,
DILIPandey