Same question is in the file i share
cell A3 is the startpoint for 1
i want to know the gap, how many rows before it comes again
In cell E3, 1 comes again
So the answer is 3
because there is no 1 in B3:D3
next gap of 1 starts in E3
and comes again in J3
answer of the gap is 4
I only want the longest gap, only one return with the longest
You dont need to do all work for me if you dont want to, but i dont know where to start and if you can give me some ideas it would be nice
Last edited by KentaTippar; 10-03-2021 at 10:48 AM. Reason: remove the link for sharing
Be aware that where you state 'rows', a glance at the workbook reveals that you mean 'columns'. This query seems to be about identifying the longest gap in a sequence of cells between a given number. I don't use Google Sheets, so won't be assisting.
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.
You do realise this is a Google sheets question and this is essentially an excel forum.
If this was an excel workbook then in A3 I would enter
=IFERROR(MATCH(IF(A1="X",0,A1),B1:$M1,FALSE),0)-1
and copy it across, then in A4
=MAX(A3:M3)
Richard Buttrey
RIP - d. 06/10/2022
If any of the responses have helped then please consider rating them by clicking the small star iconbelow the post.
"This query seems to be about identifying the longest gap in a sequence of cells between a given number."
Thats correct. My english isnt the best to explain.
Column A3:M3 is where to look in row 3
That's why the OP posted in the 'Other Platforms' section and prefixed the title with 'Google Spreadsheets'.You do realise this is a Google sheets question and this is essentially an excel forum.
We get a lot of Google Sheets queries these days.
is this forum not for google spreadsheets?, i thought google docs was that, sorryOriginally Posted by Richard Buttrey
I try that code in excel and maybe it works in google sheets or i can convert it
It's absolutely fine to ask Google Sheets queries here, as long as you post in the right section and make it clear that you are using the Google offering. You did both of these things correctly.
There are plenty of members who do use Google Sheets who may be able to help you.
i deleted A1:M1, because that has nothing to do with the question
Note that in Richard's first formula you will probably have to use semicolons ( ; ) instead of commas ( , ) due to your regional settings.
Hope this helps.
Pete
yes, i know i tried that formula but it didnt worked anyway. And i wait for any reply to google sheets instead
put this formula into cell B4, then drag across:
Screen Shot 2021-10-03 at 9.28.10 pm.png
Please Login or Register to view this content.
alternative formula to give a value of 0 if there are no previous occurances (so you get a 0 instead of a blank cell):
i just noticed previous replies regarding your regional settings for Google Sheets, so use this formula instead:Please Login or Register to view this content.
Please Login or Register to view this content.
Last edited by janmorris; 10-03-2021 at 10:40 AM.
wow, thats smart to have it to all, Thank you!
no problem, and thank you for the reps, much appreciated!
I missunderstood how they count ( i use a footballprogram with different requirement to use when i make my bets)
And they count not only the gap between, they also count that first value, so in B3:F3 they count as 4, not 3
I have also another question with this, i tried myself to count in different ways but maybe im too old to understand formulas
I need to know how many gaps of each sign there is, it doesnt mather where they are but for each signs i need how many of 1 gap of sign 1, 2 gaps of sign 1 etc...and same with the signs of X and 2
docs.google.com/spreadsheets/d/1Ovdp0rpuhh0_xxi9XHcLyG9gS_FJ2Or6B45uOzLH47g/edit?usp=sharing
basically the formula calculates "since last occurance" -1And they count not only the gap between, they also count that first value, so in B3:F3 they count as 4, not 3
so just remove that piece from the formula, so the new formula is now:
Please Login or Register to view this content.Im not sure i understand properly, Can you please add a row/grid and manually put in what the values you want?I need to know how many gaps of each sign there is, it doesnt mather where they are but for each signs i need how many of 1 gap of sign 1, 2 gaps of sign 1 etc...and same with the signs of X and 2
Last edited by janmorris; 10-04-2021 at 12:01 AM.
aha, i tried remove "-1" but did it wrong , thank you again.
=ARRAY_CONSTRAIN(ARRAYFORMULA(IF(COUNTIF($B$2:B2;C2)=0;0;COLUMN(C2) - MAX(--($B$2:B2=C2)*COLUMN($B$2:B2)); 1; 1)))
I did a new sheet how i mean with question 2
can you explain how you get the total "18"?
edit: .. dont worrry, i figured it out...
solution added to the sheet.
here is what i came up with, but there may be a more elegant solution:
Screen Shot 2021-10-05 at 11.22.52 am.pngPlease Login or Register to view this content.
Last edited by janmorris; 10-05-2021 at 12:24 AM.
ohh, didnt know it was so much to do with the formula. Before i just count them with =and+...i dont care about how the code looks like, if it works then its perfect.
Im very grateful for your help. It saves me so much time. Thank you again!
i noticed the formula has not been transposed to other rows correctly, for each date you must edit the RED part of the formula:
i have already made the adjustments on your sheetPlease Login or Register to view this content.
Last edited by janmorris; 10-05-2021 at 12:46 AM.
Yes, i noticed that, i did it right in my original sheets, good because i need to learn too :-), some mistakes is good. Now i have about 200 rows waiting for me to fill in the rest. But i only need to do it once. Then i can use the same every year.
in that case, if you do not need the "differences" row in between the dates, i would recommend separating into two tables, in this was you can remove the fixed reference "$" and drag down, then across
i will create a page as a demo
ohh, im almost done..i prepare only 1 in notepad and then use find and replace, then copy and paste
http://www.docs.google.com/spreadshe...it?usp=sharing
that is the copy of the original
Last edited by AliGW; 10-05-2021 at 03:03 AM.
by separating the rows each day, the new formula to get the totals becomes much easier because we are comparing two tables instead of comparing many rows to the one previous:
please see the "Complete" tab for end resultsPlease Login or Register to view this content.
I have attached excel file for those who want to see the final results as discussed above.
Last edited by janmorris; 10-05-2021 at 03:44 AM.
This thread is currently marked as SOLVED. Please remove the SOLVED tag whilst you require further assistance. Thanks.
Thank you, i save that because i have more games to do the same with. Im already finish with the other one....the hard way
Attachment 750460
This is not correct, there is only 2 times of gaps of 5 in sign 1
the error was caused due to the data being formatted automatically and/or as a number.. it must be forced formatted as text. i have set this and it is now calculating correctly again
Thanks! If I was rich i would hire you. I will not close this thread yet, i try first myself at another formula, its about blocksystem, 2 or 3 signs in each block i look for. I hope i fix it myself
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks