Excel expert , I need to find ocurrences . Formula use LET TEXTJOINT SUBSTITUTE

user_19359173 0
Viewed 64

ANOTHER EXAMPLE data is in A2:K2:

=LET(txt, TEXTJOIN("", FALSE, 0, --A2:K2, 0), modTxt, SUBSTITUTE(txt, "0.5", 1), halfDaysAdjust, 0.5*(LEN(txt)-LEN(SUBSTITUTE(txt, "00.50", "0000"))), LEN(modTxt)-LEN(SUBSTITUTE(modTxt, "10", "0"))-halfDaysAdjust)

This is formula that is giving me the 3 first logics I need to add one more logic without messing up the other logics

Example of logics

1 0 1 0 1 = 3 ocurrences 
1 1 1 0 1 = 2 ocurrences 
.5 0 .5 .5 0 = 1.5 ocurrences 
1 .5 0 .5 .5 = 3 ocurrences

Description:

First logic :1 are not consecutive is count as separate occurrence

2nd logic: 1s that are consecutive is 1 occurrence

3rd logic:.5 are count as a half point always

4th logic and this is the one I need to add:If a .5 is next to a 1 that .5 becomes in 1 occurrence.

This the example ocurrences example............................

excel-formula excel
1 Answers
=LET(txt,TEXTJOIN("",0,0,--A2:H2,0),
     modtxt,SUBSTITUTE(            
            SUBSTITUTE(txt,"10.5","1010"),                     
                           "0.51","101"),
LEN(modtxt)-LEN(SUBSTITUTE(modtxt,"10","1"))
+((LEN(modtxt)-LEN(SUBSTITUTE(modtxt,"0.5","00")))/2))

I first modified the 1 followed by 0.5 to 1010 so it would count both as a whole. Then I counted the occurances of 10 in the modified text. I added the count of occurances of 0.5 in the modified text and divided that count by 2 so it would add the count as halves.

enter image description here

user_126342300
Related