How to round a number DOWN to the nearest "log multiple"?

[Kelvin Stott]

I created this formula which magnificently rounds a number UP to the nearest "log multiple" of 1, 1.5, 2, 2.5 or 5:

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

Now I would like to modify this so that it rounds a number DOWN to the nearest "log multiple" of 1, 1.5, 2, 2.5 and 5, so I tried the following:

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

But this doesn't work. You'll see in the attached chart that there are gaps when it should be symmetric, and I don't understand why. Problem is I don't even understand how or why the first formula works, as I created that by trial and error.

File: https://drive.google.com/file/d/0B4rFQKgfCtswakp3ZEtwcFNwZXM/view?usp=sharing

Can anyone please help me fix this, so that I can round down as well as up.

Thanks!

 

[Kelvin Stott]

Here's an image of the chart, showing the gaps in the grey line (FLOOR function) where it should mirror the orange line (CEILING function):

[can't attach image, sorry]

 

[shg]

Can you explain what those bounds are are supposed to be?

 

[Kelvin Stott]

Yes, they are for scaling a chart axis automatically, but not the way Excel usually does it (going all the way down to zero, which I don't want).

 

[StephenCrump]

Is this what you need:

C2: =FLOOR(A2,10^INT(LOG(2*A2))/2)

	A	B	C
1	Number	RoundUp	RoundDown
2	1	1	1
3	1.1	1.5	1
4	1.4	1.5	1
5	1.5	1.5	1.5
6	1.6	2	1.5
7	1.9	2	1.5
8	2	2	2
9	2.1	2.5	2
10	2.6	5	2.5
11	6	10	5
12	11	15	10
13	16	20	15
14	21	25	20
15	26	50	25
16	51	100	50
17	101	150	100
18	151	200	150
19	201	250	200
20	251	500	250
21	501	1000	500
22	1001	1500	1000

<tbody>
</tbody>

​

 

[Kelvin Stott]

Looks great, but I tried that and it doesn't give the right floor (should be 25) from 30 to 49.

 

[Kelvin Stott]

OK, this is really strange. Again, I found this by trial and error, and I have NO idea why it works:

=FLOOR(B4,MIN(10^INT(LOG(4*B4))/2,10^INT(LOG(20*B4))/4))

And also this:

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

WHY? Nothing makes sense to me anymore!

 

[StephenCrump]

Looks great, but I tried that and it doesn't give the right floor (should be 25) from 30 to 49.

Sorry, my mistake. Clearly, I didn't test very comprehensively.

OK, this is really strange. Again, I found this by trial and error, and I have NO idea why it works:

=FLOOR(B4,MIN(10^INT(LOG(4*B4))/2,10^INT(LOG(20*B4))/4))

And also this:

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

WHY? Nothing makes sense to me anymore!

Given you're mostly working with increments of 2.5, 25, 250 etc in a log 10 system, there will be any number of combinations of log/4, and 4*log that will give you the right roundings.

I'd prefer myself to set out the logic in VBA like this, as it's then much easier to generalise, especially if your roundings are less uniform.

Function MyRound(d As Double, Optional bDown As Boolean = True) As Double

    Dim dInc As Double, dtemp As Double
    
    dtemp = Application.Log(d)
    Select Case 10 ^ (dtemp - Int(dtemp))
    Case Is <= 2.5
        dInc = 0.5
    Case Is <= 5
        dInc = 2.5
    Case Else
        dInc = 5
    End Select
    
    If bDown Then
        MyRound = Application.Floor(d, dInc * 10 ^ Int(dtemp))
    Else
        MyRound = Application.Ceiling(d, dInc * 10 ^ Int(dtemp))
    End If
    
End Function

LogScale: B2
A4: =10^LogScale
A5: =A4+10^(LogScale-1)
B4: =MyRound(A4)
C4: =MyRound(A4,FALSE)

In the cell highlighted in red, your ceiling formula gives 50.

	A	B	C
1	LogScale	1
2
3	Number	RoundDown	RoundUp
4	10	10	10
5	11	10	15
6	12	10	15
7	13	10	15
8	14	10	15
9	15	15	15
10	16	15	20
11	17	15	20
12	18	15	20
13	19	15	20
14	20	20	20
15	21	20	25
16	22	20	25
17	23	20	25
18	24	20	25
19	25	25	25
20	26	25	50
21	27	25	50
22	28	25	50
23	29	25	50
24	30	25	50
25	31	25	50
26	32	25	50
27	33	25	50
28	34	25	50
29	35	25	50
30	36	25	50
31	37	25	50
32	38	25	50
33	39	25	50
34	40	25	50
35	41	25	50
36	42	25	50
37	43	25	50
38	44	25	50
39	45	25	50
40	46	25	50
41	47	25	50
42	48	25	50
43	49	25	50
44	50	50	50
45	51	50	100
46	52	50	100
47	53	50	100
48	54	50	100
49	55	50	100
50	56	50	100
51	57	50	100
52	58	50	100
53	59	50	100
54	60	50	100
55	61	50	100
56	62	50	100
57	63	50	100
58	64	50	100
59	65	50	100
60	66	50	100
61	67	50	100
62	68	50	100
63	69	50	100
64	70	50	100
65	71	50	100
66	72	50	100
67	73	50	100
68	74	50	100
69	75	50	100
70	76	50	100
71	77	50	100
72	78	50	100
73	79	50	100
74	80	50	100
75	81	50	100
76	82	50	100
77	83	50	100
78	84	50	100
79	85	50	100
80	86	50	100
81	87	50	100
82	88	50	100
83	89	50	100
84	90	50	100
85	91	50	100
86	92	50	100
87	93	50	100
88	94	50	100
89	95	50	100
90	96	50	100
91	97	50	100
92	98	50	100
93	99	50	100
94	100	100	100

<tbody>
</tbody>

 

[Kelvin Stott]

Thanks Stephen, that makes a lot of sense. Probably I will steal/borrow your VBA code, since at least I understand it and so it's easy to modify.

Thanks again!

 

[StephenCrump]

You're welcome. I'm glad you found the code useful.