Advanced Rounding

  • Thread starter Thread starter nander
  • Start date Start date
N

nander

the rounddown function will change 18.96 to 18.90. I'd like a mor
advanced rounding feature. For the numbers on the left I like
rounding forumula that would give the the values on the right

18.96 18.95
21.87 21.85
16.49 16.50
10.59 10.55
15.44 15.45
10.73 10.75
7.29 7.25
5.67 5.65

I'd like some way to affect the cents portion of the value of a cell.
Or if I could have something that would return .25, .50, .75 and .9
depending on a range
 
What algorithm are you using to get your values?

=ROUND(A1*20,0)/20

will round to the neasest nickel

=ROUND(A1*4,0)/4

will round to the nearest 0.25

but for the life of me, I can't figure out why

16.49 ===> 16:50

but

10:59 ===> 10.55


willIn article <[email protected]>,
 
Sorry about the confusion. I don't have a particular algorithm for the
other two. I was just giving examples of what I was looking for.
 
what I've discovered


16.49 > 16.45 =FLOOR(A1,0.05) 16.50 CEILING(A1,0.05)
10.59 > 10.55 =FLOOR(A2,0.05) 10.60 CEILING(A2,0.05
 
What you are looking for can be accomplished using the VLOOKUP
function.............build a VLOOKUP table (RangeNamed "roundtable") from
..00 to .99 and giving the result you wish to return for each decimal in the
next column to the right.............then, assuming your list is in column
A, put this in B1 and copy down...........

=(ROUNDDOWN(A1,0)&VLOOKUP(RIGHT(A1,3),roundtable,2,FALSE))*1

hth
Vaya con Dios,
Chuck, CABGx3
 
Hi
Problem is that your rounding requirements don't have a common rule for
all your values. It's not clear how you want to round (IMHO this is no
rounding) in which case (sometimes you want to round to the nickel but
not in all cases).
The only way I see (unless you can provide a kind of rule for your
requirements) would be to use a lookup table for all possible decimal
values).
Maybe you can explain the business requirement behind this rounding
issue -> could be easier to come to a solution for you
 
Back
Top