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 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)
Thank you for your time!
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)
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)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 :))
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.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) )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 🙂
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") )
