Google Spreadsheets- characters in a row
11x2221111xx1
false 4 (should be 1) =SUBSTITUTE(O19;REPT("1";P20);"";"4")=LEN(O19)-LEN(O20)
false 3 (should be 0) =SUBSTITUTE(O20;REPT("1";P21);"";"3")=LEN(O20)-LEN(O21)
false 2 (should be 1) =SUBSTITUTE(O21;REPT("1";P22);"";"2")=LEN(O21)-LEN(O22)
false 1 (should be 1) =SUBSTITUTE(O22;REPT("1";P23);"";"1")=LEN(O22)-LEN(O23)
How to get the right numbers instead of False?
I want to know how many times number 1 has been in a row
4 times a row
3 times a row
2 times a row
1 times a row
are you able tp attach a txt file that has link to a shared version (without sensitive info) of your spreadsheet?
i wish i could but im not allowed to post a link
(You are not allowed to post any kinds of links, images or videos until you post a few times.)
ok, i have more questions so i start a new thread
Last edited by KentaTippar; 09-18-2021 at 09:36 AM. Reason: more text
Actually, you can, but you will have to enter the URL with maybe TWO (no more) gaps in it.
This question needs to be answered in this thread.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
aha, good it comes from you bec u are moderator, then lets do it :-)
Last edited by KentaTippar; 09-19-2021 at 06:03 AM. Reason: removing link i share
in cell O6, im not sure what you are trying to do with this formula:
1. the formula asks if one part equals another, which can only return true or false.
Please Login or Register to view this content.
2. part of that comparison is using itself to deduct from another cell, which will always return false
I dont know either, im very new to this and that formula was written by another forum but it was in excel, and i was kicked out because it was to google spreadsheets, not excel.
if there is 2 or more in a row of number 1 i want it to return how many in a row. But ok, if that formula only returns false, thats wrong
it appears that the above mentioned formula in O6, is actually meant to be two (2) separate formulas:
in theory....
O6 should be:
and R6 should be the remaining:Please Login or Register to view this content.
however, this give a difference in length, rather than the count, so it is fails to give the correct results.Please Login or Register to view this content.
instead, use the formula in O6 (as above), and drag down, then....
in R6 use the following formula:
and drag downPlease Login or Register to view this content.
Last edited by janmorris; 09-18-2021 at 12:47 PM.
very tricky to get it correct but its still not solved
Last edited by KentaTippar; 09-18-2021 at 03:24 PM. Reason: more
Please check your google spreadsheet for the formulas already applied, and if it takes care of your question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.
What isn't working? Be specific. "Still not solved" tells us nothing useful.Originally Posted by KentaTippar
11X222111XXX1
there is one in a row of 1(1)
one in a row of 2 (11)
one in a row of 3(111)
0 of 4 in a row
4 0
3 1
2 1
1 1
that is the correct return
Provide a link to the Google Sheet - you can do this by adding two spaces to the URL.
i already did that yesterday
Is that the version showing the issues?
yes, its the same question as yesterday
I know it's the same question - that's not what I asked.
the new formula you guys helped me yesterday is not correct
in Q6:Q9 it returns wrong
in S6:S9 is what it should return
or maybe i dont understand your answers,
ok, its working, i didnt see it was done in 2 sheets
thank you
Last edited by KentaTippar; 09-19-2021 at 05:57 AM. Reason: CORRECT
Thanks for letting us know.
it has already been explained to look at column R.
column Q is all variations, column R is exclusive variations.
the duplicate/copy of the sheet was only to use as a working sheet while others were working on the main sheet.
because you have removed the file link there is no way for me to do a screenshot of the proof of results.
have another look and you will see the data is the same for columns Q and R on both sheets.
Last edited by janmorris; 09-19-2021 at 07:28 AM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks