Rounding Function


Posted by Steve Shoebottom on November 23, 2001 10:28 AM

I have a problem where I want to round up my selling prices to end in either £5.00 or £9.00. For instance working from a cost of £55.00, adding a mark-up of 10%, will give me a selling price of £60.50, however any selling price that results between ##9.01 and ##4.99 must be rounded up to ##5.00 (as the above does, i.e. £60.50 must be £65.00!). By the same token any selling price calculated from any particular margin that falls between ##5.01 and ##8.99 needs to be rounded up to ##9.00. This formula obviously needs to be copied to suit an infinite number of selling prices i.e £6.99, £128.45, etc.. Remember the goal is to show a selling price "ending" in either £5.00 or £9.00

Posted by IML on November 23, 2001 11:48 AM

If your unrounded mark up cost is in A1, try
=VLOOKUP(A1-INT(A1/10)*10,{0,5;6,9;9.0001,15},2)+INT(A1/10)*10

I'd check back because there almost certainly must be a better way.



Posted by IML on November 23, 2001 11:51 AM

better make that

=VLOOKUP(A1-INT(A1/10)*10,{0,5;5.0001,9;9.0001,15},2)+INT(A1/10)*10