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
Number	RoundUp	RoundDown

<tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]1.1[/TD]
[TD="align: right"]1.5[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]1.4[/TD]
[TD="align: right"]1.5[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]1.5[/TD]
[TD="align: right"]1.5[/TD]
[TD="align: right"]1.5[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]1.6[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1.5[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]1.9[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1.5[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]2.1[/TD]
[TD="align: right"]2.5[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]2.6[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]2.5[/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]5[/TD]

[TD="align: center"]12[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]10[/TD]

[TD="align: center"]13[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]15[/TD]

[TD="align: center"]14[/TD]
[TD="align: right"]21[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]20[/TD]

[TD="align: center"]15[/TD]
[TD="align: right"]26[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]25[/TD]

[TD="align: center"]16[/TD]
[TD="align: right"]51[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]50[/TD]

[TD="align: center"]17[/TD]
[TD="align: right"]101[/TD]
[TD="align: right"]150[/TD]
[TD="align: right"]100[/TD]

[TD="align: center"]18[/TD]
[TD="align: right"]151[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]150[/TD]

[TD="align: center"]19[/TD]
[TD="align: right"]201[/TD]
[TD="align: right"]250[/TD]
[TD="align: right"]200[/TD]

[TD="align: center"]20[/TD]
[TD="align: right"]251[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"]250[/TD]

[TD="align: center"]21[/TD]
[TD="align: right"]501[/TD]
[TD="align: right"]1000[/TD]
[TD="align: right"]500[/TD]

[TD="align: center"]22[/TD]
[TD="align: right"]1001[/TD]
[TD="align: right"]1500[/TD]
[TD="align: right"]1000[/TD]

</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
LogScale

<tbody>
[TD="align: center"]1[/TD]

[TD="bgcolor: #FFFF00, align: center"]1[/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]Number[/TD]
[TD="align: right"]RoundDown[/TD]
[TD="align: right"]RoundUp[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]15[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]15[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]15[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]15[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]15[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]20[/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]20[/TD]

[TD="align: center"]12[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]20[/TD]

[TD="align: center"]13[/TD]
[TD="align: right"]19[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]20[/TD]

[TD="align: center"]14[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]20[/TD]

[TD="align: center"]15[/TD]
[TD="align: right"]21[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]25[/TD]

[TD="align: center"]16[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]25[/TD]

[TD="align: center"]17[/TD]
[TD="align: right"]23[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]25[/TD]

[TD="align: center"]18[/TD]
[TD="align: right"]24[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]25[/TD]

[TD="align: center"]19[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]25[/TD]
[TD="bgcolor: #FF0000, align: right"]25[/TD]

[TD="align: center"]20[/TD]
[TD="align: right"]26[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]50[/TD]

[TD="align: center"]21[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]50[/TD]

[TD="align: center"]22[/TD]
[TD="align: right"]28[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]50[/TD]

[TD="align: center"]23[/TD]
[TD="align: right"]29[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]50[/TD]

[TD="align: center"]24[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]50[/TD]

[TD="align: center"]25[/TD]
[TD="align: right"]31[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]50[/TD]

[TD="align: center"]26[/TD]
[TD="align: right"]32[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]50[/TD]

[TD="align: center"]27[/TD]
[TD="align: right"]33[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]50[/TD]

[TD="align: center"]28[/TD]
[TD="align: right"]34[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]50[/TD]

[TD="align: center"]29[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]50[/TD]

[TD="align: center"]30[/TD]
[TD="align: right"]36[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]50[/TD]

[TD="align: center"]31[/TD]
[TD="align: right"]37[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]50[/TD]

[TD="align: center"]32[/TD]
[TD="align: right"]38[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]50[/TD]

[TD="align: center"]33[/TD]
[TD="align: right"]39[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]50[/TD]

[TD="align: center"]34[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]50[/TD]

[TD="align: center"]35[/TD]
[TD="align: right"]41[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]50[/TD]

[TD="align: center"]36[/TD]
[TD="align: right"]42[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]50[/TD]

[TD="align: center"]37[/TD]
[TD="align: right"]43[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]50[/TD]

[TD="align: center"]38[/TD]
[TD="align: right"]44[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]50[/TD]

[TD="align: center"]39[/TD]
[TD="align: right"]45[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]50[/TD]

[TD="align: center"]40[/TD]
[TD="align: right"]46[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]50[/TD]

[TD="align: center"]41[/TD]
[TD="align: right"]47[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]50[/TD]

[TD="align: center"]42[/TD]
[TD="align: right"]48[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]50[/TD]

[TD="align: center"]43[/TD]
[TD="align: right"]49[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]50[/TD]

[TD="align: center"]44[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]50[/TD]

[TD="align: center"]45[/TD]
[TD="align: right"]51[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]100[/TD]

[TD="align: center"]46[/TD]
[TD="align: right"]52[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]100[/TD]

[TD="align: center"]47[/TD]
[TD="align: right"]53[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]100[/TD]

[TD="align: center"]48[/TD]
[TD="align: right"]54[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]100[/TD]

[TD="align: center"]49[/TD]
[TD="align: right"]55[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]100[/TD]

[TD="align: center"]50[/TD]
[TD="align: right"]56[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]100[/TD]

[TD="align: center"]51[/TD]
[TD="align: right"]57[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]100[/TD]

[TD="align: center"]52[/TD]
[TD="align: right"]58[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]100[/TD]

[TD="align: center"]53[/TD]
[TD="align: right"]59[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]100[/TD]

[TD="align: center"]54[/TD]
[TD="align: right"]60[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]100[/TD]

[TD="align: center"]55[/TD]
[TD="align: right"]61[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]100[/TD]

[TD="align: center"]56[/TD]
[TD="align: right"]62[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]100[/TD]

[TD="align: center"]57[/TD]
[TD="align: right"]63[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]100[/TD]

[TD="align: center"]58[/TD]
[TD="align: right"]64[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]100[/TD]

[TD="align: center"]59[/TD]
[TD="align: right"]65[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]100[/TD]

[TD="align: center"]60[/TD]
[TD="align: right"]66[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]100[/TD]

[TD="align: center"]61[/TD]
[TD="align: right"]67[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]100[/TD]

[TD="align: center"]62[/TD]
[TD="align: right"]68[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]100[/TD]

[TD="align: center"]63[/TD]
[TD="align: right"]69[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]100[/TD]

[TD="align: center"]64[/TD]
[TD="align: right"]70[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]100[/TD]

[TD="align: center"]65[/TD]
[TD="align: right"]71[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]100[/TD]

[TD="align: center"]66[/TD]
[TD="align: right"]72[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]100[/TD]

[TD="align: center"]67[/TD]
[TD="align: right"]73[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]100[/TD]

[TD="align: center"]68[/TD]
[TD="align: right"]74[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]100[/TD]

[TD="align: center"]69[/TD]
[TD="align: right"]75[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]100[/TD]

[TD="align: center"]70[/TD]
[TD="align: right"]76[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]100[/TD]

[TD="align: center"]71[/TD]
[TD="align: right"]77[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]100[/TD]

[TD="align: center"]72[/TD]
[TD="align: right"]78[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]100[/TD]

[TD="align: center"]73[/TD]
[TD="align: right"]79[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]100[/TD]

[TD="align: center"]74[/TD]
[TD="align: right"]80[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]100[/TD]

[TD="align: center"]75[/TD]
[TD="align: right"]81[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]100[/TD]

[TD="align: center"]76[/TD]
[TD="align: right"]82[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]100[/TD]

[TD="align: center"]77[/TD]
[TD="align: right"]83[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]100[/TD]

[TD="align: center"]78[/TD]
[TD="align: right"]84[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]100[/TD]

[TD="align: center"]79[/TD]
[TD="align: right"]85[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]100[/TD]

[TD="align: center"]80[/TD]
[TD="align: right"]86[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]100[/TD]

[TD="align: center"]81[/TD]
[TD="align: right"]87[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]100[/TD]

[TD="align: center"]82[/TD]
[TD="align: right"]88[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]100[/TD]

[TD="align: center"]83[/TD]
[TD="align: right"]89[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]100[/TD]

[TD="align: center"]84[/TD]
[TD="align: right"]90[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]100[/TD]

[TD="align: center"]85[/TD]
[TD="align: right"]91[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]100[/TD]

[TD="align: center"]86[/TD]
[TD="align: right"]92[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]100[/TD]

[TD="align: center"]87[/TD]
[TD="align: right"]93[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]100[/TD]

[TD="align: center"]88[/TD]
[TD="align: right"]94[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]100[/TD]

[TD="align: center"]89[/TD]
[TD="align: right"]95[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]100[/TD]

[TD="align: center"]90[/TD]
[TD="align: right"]96[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]100[/TD]

[TD="align: center"]91[/TD]
[TD="align: right"]97[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]100[/TD]

[TD="align: center"]92[/TD]
[TD="align: right"]98[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]100[/TD]

[TD="align: center"]93[/TD]
[TD="align: right"]99[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]100[/TD]

[TD="align: center"]94[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]100[/TD]

</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.