SOLVED

# Extracting round values from an interval

Copper Contributor

# 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 find a way to get all values divisibile by 5 between this numbers whith those two as top and bottom, in this case I need something to get 45,50,55,60,65, also it would be great if it could be done so that every returned value has it's own cell. I watched so many tutrioals but I'm completely lost, I don't even know where to start. It's not so neccessary for my studies now (I could just write those values myself), but I'm the type of person who loves to constantly learn and improve.

(Sorry if I labeled it wrong, but besides Excel, I have no idea what other labels should I choose)

7 Replies
best response confirmed by Nick2000 (Copper Contributor)
Solution

# Re: Extracting round values from an interval

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

# Re: Extracting round values from an interval

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.

# Re: Extracting round values from an interval

I may need to see  a larger sample set of numbers but try this one:

``````=LET(
k, (C1 - A1) / 5,
arr, SEQUENCE(k, , A1 + 5, 5),
MROUND(arr, 5)
)``````

# Re: Extracting round values from an interval

@Detlef Lewin  Thank you soooo much, you helped me a lot, I'm still trying to learn and understand this whole formula, but I'll do it step by step, thank you again and sorry for the delayed response but I wrote it (about 5-6 hours after your post) and forgot to press "post", thank you again :))

# Re: Extracting round values from an interval

@SnowMan55  Thank you very much for all the options, you lost me a bit with the second part, but every new information is welcomed (I'll research it as soon as I can, I promise :) ) , thank you so much for sharing that information!

# Re: Extracting round values from an interval

@Patrick2788  Thank you very much, it works with just a tiny "problem", if the value is already divisible by 5 (let's say it would've been 45 instead of 40.5 in my example) it just jumps to the next value (50 in my example), thank you so much for taking your time, I don't know what you refer to when you're saying by "larger sample set of numbers", because I need this formula only for 2 values (if you could explain a bit it would be great). That being said, have nice day and thanks again :)

# Re: Extracting round values from an interval

A slightly more verbose style that may be easier to read.

``````= LET(
start,     CEILING(bottom, 5),
end,       FLOOR(top, 5),
intervals, 1 + (end - start) / 5,
SEQUENCE(intervals, , start, 5)
)``````

If called for, you may need to check that 'intervals > 0'.

``````    ...
IF(intervals > 0, SEQUENCE(intervals, , start, 5), "None")
)``````
1 best response

Accepted Solutions
best response confirmed by Nick2000 (Copper Contributor)
Solution

# Re: Extracting round values from an interval

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