Formula for a Repeating Sequence

[LeonardH]

Hello,

My situation is, I have meter readings that come in daily and are ever growing. Equipment is serviced based on the meter readings and the type of service depends on a sequence. Presently this is all done manually.

My question is, if I have a repeating sequence of 16 services spaced at intervals of 250 (shown below), does someone know of a way to find which would be the next service required based on which service would be next?

Sequence #	1​	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16​
Interval	250	500	750	1000	1250	1500	1750	2000	2250	2500	2750	3000	3250	3500	3750	4000
Service Type	A	B	A	C	A	B	A	D	A	B	A	C	A	B	A	E​

An example would be, an equipment currently has 97,830 hours. The next service would be at 98,000 hours and would be a "D" service to follow the sequence. So, I would need a formula that returns "D Service".

Thanks in advance! My mind has been struggling with this one.



FYI, I'm in Excel 2016

 

[Fluff]

FYI, I'm in Excel 2016

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

How about

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Sequence #	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16
2	Interval	250	500	750	1000	1250	1500	1750	2000	2250	2500	2750	3000	3250	3500	3750	4000
3	Service Type	A	B	A	C	A	B	A	D	A	B	A	C	A	B	A	E
4
5		3751	E
6		97830	D
Report

Cell Formulas
Range	Formula
C5:C6	C5	=INDEX($B$3:$Q$3,MOD(ROUNDUP(B5/250,0)-1,16)+1)

 

[RobP]

Hi Leonard

sorry, I didnt catch how you know based on 97830 hours that the next service would be 98000 .. ? I didn't find any relation to your data shown above ?

thanks
Rob

 

[lrobbo314]

Always interesting to see how other people do it. I would use @Fluff's solution, especially if you're not on 365, but here was how I tackled it.

Template.xltx
	J	K
17	Number:	97830
18	Result:	D
Data

Cell Formulas
Range	Formula
K18	K18	=LET(n,K17,x,(TRUNC(n/250,0)+1)*250,SWITCH(MID(x,LEN(x)-2,1),"2","A","5","B","7","C","0","D"))

 

[Fluff]

especially if you're not on 365

Maybe you missed

FYI, I'm in Excel 2016

So the OP cannot use your formula.

 

[Fluff]

Another option if you don't have the grid is

=CHOOSE(MOD(ROUNDUP(B5/250,0)-1,16)+1,"A","B","A","C","A","B","A","D","A","B","A","C","A","B","A","E")

 

[lrobbo314]

I didn't pay close enough attention to the sequence, so my formula wouldn't work anyway, whoops.

 

[LeonardH]

Hi Leonard

sorry, I didnt catch how you know based on 97830 hours that the next service would be 98000 .. ? I didn't find any relation to your data shown above ?

thanks
Rob

Hi Rob, the service is in the repeating sequence. If the sequence repeats from 0 hours, at 98000 hours, the service would be a D service. Hope that helps!

Thanks everyone for your solutions! My sheet works great now with Fluff's solution.

 

[Fluff]

Glad we could help & thanks for the feedback.