CODEWITHSUNDEEP
excelrounding
user2917922
user2917922

Reputation: 11

Excel rounding up or down to specific number

I can't seem to figure out a formula in excel for rounding up or down numbers to end in either 49 or 99.

What I'm looking to do

$824 should round down to $799; $825 should round up to $849; $874 should round up to $899; $873 should round down to $849

The number should round up or down to whichever number it is closest to.

Upvotes: 1

Views: 24190

Answers (3)

ant
ant

Reputation: 56

I agree that rounding of 874 to 899 seems strange. However, if this is not a mistake and can be reasoned, for example, by desire to choose 99 in the case of a tie-break, then here is the solotion:

=IF(ABS(MROUND(A1;100)-1-A1)<=ABS(MROUND(A1;50)-1-A1);MROUND(A1;100)-1;MROUND(A1;50)-1)

Output:

824 799
825 849
873 849
874 899

Upvotes: 0

Automate This
Automate This

Reputation: 31364

Since it's always 49 or 99 you can use MROUND like this:

=MROUND(A4,50)-1

If you have a reasonably small range and you want other unique values that are not multiples of each other you can use a build-in lookup function like this (replace 'A1' with your value or cell address):

=LOOKUP(A1, {0,575,625,675,725,775,825,875,900}, {"Too Low",599,649,699,749,799,849,899,"Over"})

The lookup function allows you to do odd rounding such as your referring to by rounding 874 up instead of down. Alter the equation like this for this offset.

=LOOKUP(A1, {0,574,625,674,725,774,825,874,900}, {"Too Low",599,649,699,749,799,849,899,"Over"})

Upvotes: 2

wilsjd
wilsjd

Reputation: 2258

=ROUND(A1/50,0)*50-1

Source: http://www.mrexcel.com/forum/excel-questions/24527-round-nearest-50-a.html

So essentially, just round to the nearest 50 then subtract 1.

enter image description here

Upvotes: 1

Related Questions