Occasional Contributor

Hi There,


I have a massive list of prices that I need to set price points to.

I have used the CEILING formula in the past to go in increments of 50 but our price points are more dynamic now so this cannot work.

I need a formula to define either a 29.95, 49.95, 79.95 or 99.95 - which ever price point is closest.

The prices I have range from 86.51 - 6692.79 across 2874 rows.

So if a price is 1002.79 it should be 999.95.


Does anyone know a way of doing this?

2 Replies
best response confirmed by Paul_Bragason (Occasional Contributor)



With a price in P2:

=LET(d,P2+0.05, m, MOD(d, 100), p, IFS(m<15, 0, m<40, 30, m<65, 50, m<90, 80, TRUE, 100), w, FLOOR(d, 100), MAX(w+p-0.05, 0))


Hi Paul, I have created a formula for you that performs your desired calculation. I used the LOOKUP() function in combination with IF() and ROUNDDOWN().



In the enclosed Excel sheet, I have also provided this formula as a LAMBDA() function with the name "RNF" = Round-Ninety-Five. So you can achieve your desired result with =RNF(A1) if your value is in cell A1. Of course, this also works with the formula above.