•2 min read•from Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community
Counting longest spurt of consecutive cells with value
Our take
Hey y’all! It sounds like you're tackling a significant data challenge with your 6-hour intervals over the year. Counting the longest consecutive period where values exceed -12 can indeed be streamlined. While your current method is functional, consider exploring more efficient solutions using formulas, Power Query, or VBA to reduce workbook clutter. This will not only enhance usability but also make it easier to analyze multiple columns and subsets over time.
Hey y’all! In my data I have a row for every 6 hours for 12 months and a column with varying values for each row. Like,
A B
1/1/2026 0:00 -9.76
1/1/2026 6:00 -12.54
etc.
I need to count what was the longest length of time the values were above -12 for the entire year. Any ideas on how to do this?
I’m open to any solutions be it formulas, power query, or macros/VBA. I’m on 365 on a desktop. I’m currently cleaning up a bunch of data sheets other people made and this is a task we have to do frequently. In this workbook I’ll have to do it on 20+ columns and then do it again on each column for subsets of the time we have data for.
Their current solution is a C column with =IF(B2>=-12,C2+.25,0) to count the days >-12 in a row in 6 hour increments. Then in column D they have =IF(C3>C2+C4,C3,0) to have a column with just the max value of each consecutive spurt of time. At the end they run =MAX(D2:D1459) to get the longest spurt. Then they add 2 more columns for each subset of time they want to look at. It takes up half the workbook.
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- Countifs function comparing the number of occurrences of a value within a date range without having to change the date for every cell?https://preview.redd.it/5wv7p6qq6dvg1.png?width=1523&format=png&auto=webp&s=8678f87180d0cf5c410d2f14fdaefc77ca4dc255 I am a self-taught excel newbie and am trying to help automate the calculation of the stats at my job (meaning I will likely be back with multiple more questions). For this problem, I am trying to get the data to automatically calculate as information is entered in another sheet to determine how often certain values (e.g. "Academic") appear within each month. For some reason, when I put the cell (H2) in place of the date, excel does not register it as a cell and will not complete the calculation. The only solution I have found is to have to individually type out each date range rather than referring to the cell that contains the date range. Further, is there a way to not have to include the year? in this job, we have a separate file for every school year meaning there will be no overlap of month causing issues that way. I am trying to find an easier way to do a year to year comparison without having to include helper columns with the beginning and end dates of each month per year. https://preview.redd.it/55l3ixa49dvg1.png?width=1140&format=png&auto=webp&s=3b222fe0266778a2645f72fed52b59efae4ed850 Thank you! submitted by /u/mentallylost14 [link] [comments]
- Anomalous Sorting of COUNTIF ColumnsEDIT: Resolved as I was trimming the worksheet to upload a version with data redacted with replacement text. As I was removing extraneous worksheets, the formula broke with a #REF value. When I fixed them, the problem resolved. Looks like I was actually connected to another set of the same data, but since it as outside of the table, it was creating the anomaly inside of it. Essentially the issue outlined in this blog article, except A) I am not using the unnecessary sheet reference that fixes the problem if it's removed and B) the formula displays correctly: https://excelcharts.com/excel-sort-countif-function-mess-fix-it-how-to/ (Unfortunately, the data I'm working on is proprietary, so I can't share specifics. :( Images are mockups that simulate the results I'm getting.) I have a large table on Sheet1. Column A is a Date column that runs back several years. I would like to count instances in text column B going one year back as of the most recent update and then sort that from largest to smallest, so I can make a simple line graph with the top ten items. Updated iterations of this graph will be used in a routine report, I'm ultimately trying to draft a plug and play tool so my Excel-deficient coworkers can just Copy/Paste into Powerpoint. https://preview.redd.it/ad56jbjp0oog1.png?width=364&format=png&auto=webp&s=8fc6b43dfe338b2c93d3ce7945d5fe9b7e45e0e5 On Sheet2 I create a secondary table that will have 2 columns. The text column A and the Count column B. I have entered each unique Data value from Sheet1 into the Data column in Sheet2. In the Count column I enter the following formula: =COUNTIFS('Sheet1'!A:A,">="&MAX('Sheet1'!A:A)-365,'Sheet1'!A:A,">="&MAX('Sheet1'!A:A),'Sheet1'!B:B,A#) (Where # equal the row) The table on Sheet1 has over 20 columns and I ultimately want to create line graphs for the data in several columns. Since there will be several of these graphs, and the data in Sheet1 is appended with new, up to date data at least once a week, it makes more sense to list the range as 'Sheet1'!A:A rather than 'Sheet1'!A1:A# The formula works just fine. https://preview.redd.it/0gpltacq2oog1.png?width=260&format=png&auto=webp&s=0ba8b74005b82fa9f37813dde8f838af29d49b96 But when I try to sort by Count, it does what's pretty much spelled out in the above linked article, moving the Data cell to the correct row, with the formula adjusting to reflect it's new replacement, but returning the value associated with the Data in the row it was in before the sort. So we see "Item27" in row 28 has the highest count, with 56 occurrences. The formula in row 28 column B, as expected, is: =COUNTIFS('Sheet1'!A:A,">="&MAX('Sheet1'!A:A)-365,'Sheet1'!A:A,">="&MAX('Sheet1'!A:A),'Sheet1'!B:B,A28) After the sort, "Item27" is in row 2 (as expected) but is returning a count of 4, the amount of occurrences "Item17" (now sitting in "Item27"'s old position in row 28) returned before the sort. Of course, "Item17" is returning the wrong count as well. "Item1" was obviously displaced by "Item27" and is in it's expected row 19 but returning a count of 56, which is "Item47"'s actual count. Essentially, each "Item#" is returning a count of whatever "Item#" replaced them in their old position in the table. https://preview.redd.it/aknjet2x5oog1.png?width=249&format=png&auto=webp&s=d069620eed3867df4eabae42532654c089f1cef7 HOWEVER, unlike the example in the article, the formula sitting in B2 next to "Item47" is still correct: =COUNTIFS('Sheet1'!A:A,">="&MAX('Sheet1'!A:A)-365,'Sheet1'!A:A,">="&MAX('Sheet1'!A:A),'Sheet1'!B:B,A2) So.... what's the issue and how do I resolve it? Version: Microsoft Excel for Microsoft 365 MSO (v2508) Environment: Desktop My Knowledge: Intermediate submitted by /u/LikeCCClockwork [link] [comments]
- Slow spreadsheet - need troubleshootingHi, I have a spreadsheet that has two tabs, one is essentially the original data which is YTD driven for a particular GL account, the company has smaller amounts of transactions, so by December we are talking about maybe 3-5k rows of transactions for the account total. The main tab being utilized, has about 30 columns of look up and sumifs formulas referencing the source data and in total approx maybe 500 rows by year end? To me it doesn’t seem excessive. I’ve dealt with way heavier spreadsheets that have more omph and run faster. But for some reason this one is slow as all hell to work in. I’ve even tried barcoded some data and not seen any improvement. I’m not too techy into what else could be slowing it down. And ideas on what to troubleshoot from here? submitted by /u/SlideTemporary1526 [link] [comments]
- Conditional Formatting to highlight time for 24 hours (different for each cell)I would like the cells to be highlighted in red if it exceeds 24 hours based on the time and date in the individual cell. It is a large data set and it is preferable to not amend the formula in each cell individually. For example, column L has the cells where I want the cells to be highlighted if they exceed 24 hours however each cell as a different time; L2 is Mar 20, 2026, 10:25:21 PM and L3 is Mar 20, 2026, 7:00:01 AM. The 24 hour mark for the each cell would vary. submitted by /u/Interesting_Low9324 [link] [comments]
Tagged with
#Excel alternatives for data analysis#generative AI for data analysis#natural language processing for spreadsheets#real-time data collaboration#data cleaning solutions#real-time collaboration#financial modeling with spreadsheets#big data management in spreadsheets#conversational data analysis#row zero#intelligent data visualization#data visualization tools#enterprise data management#big data performance#data analysis tools#rows.com#enterprise-level spreadsheet solutions#Excel compatibility#google sheets#AI-driven spreadsheet solutions