How to round up a number to nearest "log multiple" of 1, 2 or 5?

[Kelvin Stott]

Hi,

Is the a function or formula I could use to ROUND UP a number to just 1 significant figure, which must be either 1, 2 or 5?

For example:

1.3 -> 2
2.4 -> 5
7.1 -> 10
11 -> 20
21 -> 50
52 -> 100
120 -> 200
219 -> 500
560 -> 1,000

I've tried many combinations of MROUND and ROUNDUP, CEILING, etc., but keep getting tied up in knots.

Thanks for any help.

 

[Aladin Akyurek]

Here are some links on significant figures:

http://www.mrexcel.com/forum/excel-questions/1143-significant-figures.html
http://www.mrexcel.com/forum/excel-questions/5836-sig-figs.html
http://www.mrexcel.com/forum/excel-questions/64212-significant-figures-problem.html
http://www.mrexcel.com/forum/excel-...icant-figures-decimal-points.html#post3688405

 

[Kelvin Stott]

Hi Aladin, I have seen those links, but unfortunately they don't solve my problem as they aren't restricted to log multipes of 1, 2 and 5. Please check the examples in my original post to see how I need the numbers to round up. Thanks.

 

[Kelvin Stott]

So far, this is what I managed to develop:

=CEILING(B4,10^INT(LOG(4*B4))/2)

This rounds the number in B4 up to the nearest log multiple of 1, 1.5, 2, 2.5 and 5, which is not exactly what I was looking for, but actually fits better with what I am trying to do.

 

[Rick Rothstein]

I'd be willing to bet there is a simpler formula than this, but until someone posts it, this will have to do...

[table="width: 500"]
[tr]
	[td]=IF(0+LEFT(A1-0.0000000001)<2,2,IF(0+LEFT(A1-0.0000000001)<5,5,10))*10^(LEN(INT(A1-0.0000000001))-1)[/td]
[/tr]
[/table]

 

[Kelvin Stott]

Thanks, Rick, certainly very creative to process a number as text in nested IF-THEN statements, though not exactly elegant!

 

[Rick Rothstein]

Thanks, Rick, certainly very creative to process a number as text in nested IF-THEN statements, though not exactly elegant!

You did not say you wanted an elegant solution, so I did not give you one. Besides, I could not think of another way to do it.

 

[Kelvin Stott]

This gets me even closer, rounding up to the nearest log multiple of 1, 1.5, 2 and 5, though don't ask me why as I found it by trial and error:

=CEILING(B4,10^INT(LOG(5*B4))/2)

 

[West Man]

Another possibility: =CHOOSE(LEFT(A2,1)+1,2,2,5,5,5,10,10,10,10)*(10^(LEN((INT(A2))-1)-1))