Forum Discussion
Solved
DEFINING LAST 4 PLACES IN PRICE POINTS
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 t...
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().
=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.