Rounding to the nearest number from a list

New Contributor

Hi all

I am looking at a way of quickly rounding a large list of prices to the nearest psychological price point from a separate list of prices?

Any help would be greatly appreciated?

3 Replies


I assume you are referring to the practice of pricing $12.00 goods at $11.95?  Round to the nearest whole number price point and subtract the 1cent, 5cents, $1, $5 depending on the amount and your policy.  Note, this does not alter the original value, only the value shown by the copy.

Hi, thanks for this, unfortunately, the rule changes at various prices, for those below 200USD they would be priced at the nearest .99, whereas anything above 200USD would be priced to the nearest whole dollar but ending in 9 for example 205 would become 209, 314 would become 309


This works a solution through using 365 but a complete set of rules would be needed before the solution is 'packaged' to remove the helper table.