DEFINING LAST 4 PLACES IN PRICE POINTS

[Paul_Bragason]

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 · Accepted solution]

@Paul_Bragason 

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))

[#3]

@Paul_Bragason 

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().

=IF(A1<14.95,29.95,LOOKUP(A1-ROUNDDOWN(A1,-2),{0,14.95,39.95,64.95,89.95},{-0.05,29.95,49.95,79.95,99.95})+ROUNDDOWN(A1,-2))

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.