SOLVED

DEFINING LAST 4 PLACES IN PRICE POINTS

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

@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.