To round to the nearest 5 in Excel, you can use the MROUND function. Suppose you have a dataset as shown below where you want to round the estimated number of hours to the nearest 5. This would mean that 161 should become 160 and 163 would become 165. MROUND function takes two arguments.
When rounding to the nearest half, round the fraction to whichever half the fraction is closest to on the number line. If a fraction is equally close to two different halves, round the fraction up. Here is a fraction. To figure out which value five-sixths is closest to, first think in terms of sixths.
Excel has a very elementary take on rounding. It's the same method you learned early in grade school: If you're rounding to a whole number, decimals from 0.1 to 0.4 round down and decimals from 0.5 to 0.9 round up.
Multiply your rating by 2, then round using Math.Round(rating, MidpointRounding.AwayFromZero)
, then divide that value by 2.
Math.Round(value * 2, MidpointRounding.AwayFromZero) / 2
Multiply by 2, round, then divide by 2
if you want nearest quarter, multiply by 4, divide by 4, etc
Here are a couple of methods I wrote that will always round up or down to any value.
public static Double RoundUpToNearest(Double passednumber, Double roundto)
{
// 105.5 up to nearest 1 = 106
// 105.5 up to nearest 10 = 110
// 105.5 up to nearest 7 = 112
// 105.5 up to nearest 100 = 200
// 105.5 up to nearest 0.2 = 105.6
// 105.5 up to nearest 0.3 = 105.6
//if no rounto then just pass original number back
if (roundto == 0)
{
return passednumber;
}
else
{
return Math.Ceiling(passednumber / roundto) * roundto;
}
}
public static Double RoundDownToNearest(Double passednumber, Double roundto)
{
// 105.5 down to nearest 1 = 105
// 105.5 down to nearest 10 = 100
// 105.5 down to nearest 7 = 105
// 105.5 down to nearest 100 = 100
// 105.5 down to nearest 0.2 = 105.4
// 105.5 down to nearest 0.3 = 105.3
//if no rounto then just pass original number back
if (roundto == 0)
{
return passednumber;
}
else
{
return Math.Floor(passednumber / roundto) * roundto;
}
}
There are several options. If performance is a concern, test them to see which works fastest in a large loop.
double Adjust(double input)
{
double whole = Math.Truncate(input);
double remainder = input - whole;
if (remainder < 0.3)
{
remainder = 0;
}
else if (remainder < 0.8)
{
remainder = 0.5;
}
else
{
remainder = 1;
}
return whole + remainder;
}
decimal d = // your number..
decimal t = d - Math.Floor(d);
if(t >= 0.3d && t <= 0.7d)
{
return Math.Floor(d) + 0.5d;
}
else if(t>0.7d)
return Math.Ceil(d);
return Math.Floor(d);
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With