Forum Discussion
Extracting round values from an interval
Hello everybody, I'm really strugling with something here, I'm not even sure if the title is correct 40.5 - 67.5 Let's say I have these 2 values (40.5 is A1 and 67.5 is C1), I'm trying to f...
Try this:
=LET( a,SEQUENCE(ROUNDDOWN(C1,0)-ROUNDUP(A1,0)+1,,ROUNDUP(A1,0),1), b,MOD(a,5)=0, d,FILTER(a,b), d)
If you want a comma-delimited list, replace the last d in Detlef_Lewin's formula with TEXTJOIN(",",,d) (TEXTJOIN function documentation) If you want cells extending to the right, place the formula into cell D1 (or further right) and replace the last d with TRANSPOSE(d) (TRANSPOSE function documentation)
If you want something that "pushes" the content of C1 to the right as it inserts the (five, in this case) calculated values between it and A1, you'll need a VBA macro or Office Script. But if a copy of A1 and C1 is acceptable, you could use a formula in D1 (or further right) that replaces the last d with HSTACK(A1,TRANSPOSE(d),C1) (HSTACK function documentation) This last option requires Excel 365 or Excel for the web.
