Kelvin Stott
Active Member
- Joined
- Oct 26, 2010
- Messages
- 338
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!
=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!
Last edited: