I have a list of cable measurements which range from 40 - 500. I'm looking for it to round up to the nearest option (40, 60, 80, 120, 300, 500 as these are the cable lengths available) But with a variable of rounding up or down by 10.

So if the measurement is 121, I want it to round down to 120. if its 131, i want it to round up to 300.

Am i asking too much?

CodePudding user response：

Give a try on the following formula-

=LET(x,XLOOKUP(A2,{40,60,80,120,300,500},{40,60,80,120,300,500},0,-1),IF((A2-x)<10,x,XLOOKUP(A2,{40,60,80,120,300,500},{40,60,80,120,300,500},0,1)))