•1 min read•from Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community
What formula for sequencing number but when adjacent row is blank it wont get sequenced
Our take
If you're looking to create a sequence of numbers in Google Sheets that skips blank rows, you've encountered a common challenge. The formula you've tried may not yield the desired outcome if blank rows are filled with hidden formulas. A more effective approach is to use `=IF(B14="", "", MAX(A$13:A13) + 1)`, which will correctly increment the sequence only for cells that contain data. This solution ensures that your sequence reflects the actual entries, providing a clearer view of your data without counting unintended blanks.
I have tried this formula form chat gpt: =IF(B14="","",COUNTIF($B$14:B14,"<>")) =IF(B14="","",SUBTOTAL(103,$B$14:B14)) =IF(B14="","",SUBTOTAL(103,B$14:B14))
But the blank row still get counted like: B1 has data, number 1 B2 has no data, blank B3 has data, number 3
What i want is: B1 has data, so its number 1 B2 has no data, so its blank B3 has data, so its number 2
Iam using google spreadsheets, sorry if it confusing iam still new here
*edit: thanks for the help everyone i think the previous formula not doing what i wanted is because the cell that i thought blank is filled with vlookup formula, iam using =IF(B14 = "", "", MAX(A$13:A13) + 1) and it work!
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- Sum formula for calculating month occurrences from dates works for everything except for January.I am attempting to automate the calculations of how often each month is recorded in another tab. The formula is working for all the other months, but as I am using this template to calculate values as I enter them, I arbitrarily set the limit to 2000, allowing me not to have to think about changing the formula as I enter new information. Unfortunately, it seems that the blank cells are being coded as "1" https://preview.redd.it/7zte9xn701vg1.png?width=972&format=png&auto=webp&s=ba131bec322f84ce55fa9fdbc35cedfcbeb12d9f I have tried going through the formula and found that all the blank cells are coding themselves as "true" only for the month of January (1). I attempted to add =IF(SHEET2!A3:A2000 = " ", 0, ... to the beginning of the formula, but it just gave me a spill error. I might be getting confused between excel vs. Google Sheets formulas. Thank you! submitted by /u/mentallylost14 [link] [comments]
- Cell merging / formatting formulasThis might be an odd one. I'm not that skilled with excel as my use of in within my job is pretty limited. However, I tend to use this template my predecessor made to summarize data from our program. Works well, just a simple ='SHEET 1'!A1 for all cells. The first two images give an example. After the data is ported, I have to get rid of the zeros between the data and write system names. When it comes to pasting it on letters, the names are bolded, upped a font size, and two of the cells are merged (3rd image) This gets a bit tedious as the lists can get pretty long so I've been trying to figure out how to streamline it on my own. My idea has so far has been to have a separate cell detect when I'm finished adding my data and then format the aforementioned cells (4th image). For the life of me, just can't figure out how to write a formula to do it. What I would need is for the formula to detect a 1 (could be anything) in cell G10. It would then check for any blank cells in columns A and B. Once found, it would merge & center, bold the text, increase the font size, and align right. Is this only possible with a macro? I've been unable to find any formulas that could accomplish this. https://preview.redd.it/853mpr4jspog1.png?width=788&format=png&auto=webp&s=943a34a57f1d7c7f88256dd89b81b9c6fc301e34 https://preview.redd.it/e3w6ms4jspog1.png?width=453&format=png&auto=webp&s=9b189fa77d6638b627f8676dad60bc148753617c https://preview.redd.it/msh6ys4jspog1.png?width=411&format=png&auto=webp&s=d5f5025b315f24fd5f3a64c6e07e019abd3a77ab https://preview.redd.it/7j66tt4jspog1.png?width=936&format=png&auto=webp&s=1faa4df12ee74892f5fa9dc27ac95621280cf3c5 submitted by /u/Extension_Train9093 [link] [comments]
- Generating a formula in a cell re-iteratively?Is there anyway using the newish functions (sorry I am an old version excel guy) to trim this down into one line using LET/SEQUENCE/ etc =VSTACK( IF(BA2-(BA2-1)>BA2,HSTACK("","","","","","","",""),VSTACK(HSTACK("","","","",INDEX(AZ:AZ,BA2-(BA2-1)),INDEX(AY:AY,BA2-(BA2-1)),"",""),FILTER(AP:AW,(AW:AW=INDEX(AY:AY,BA2-(BA2-1)))*(AW:AW<>"")))), IF(BA2-(BA2-2)>BA2,HSTACK("","","","","","","",""),VSTACK(HSTACK("","","","",INDEX(AZ:AZ,BA2-(BA2-2)),INDEX(AY:AY,BA2-(BA2-2)),"",""),FILTER(AP:AW,(AW:AW=INDEX(AY:AY,BA2-(BA2-2)))*(AW:AW<>"")))) ) it actually repeats 20 times I have just trimmed it to 2 iterations here I appreciate any insight submitted by /u/FFootyFFacts [link] [comments]
- Blank cells are being summed as a value greater than five using a SUM(IF functionHi! I have a large dataset of 7-day schedules that I am summing to calculate breaks. The function is setup like this: =SUM(IF(CELL1>5, 0.5, 0)), (IF(CELL2>5, 0.5, 0)) and so on until all seven days are tabulated. Because a full time person would only work 5 days a week, at least 2 days are blank per row, but we're open all 7 days and I want to sum the entire spreadsheet, so I have to count all 7 days. A full time person should have 2.5 hours per week. For some reason the function is mostly working, but about a third of the results are larger than they should be because it's counting some blank cells as greater than 5. For example, a part time person working only 3 days should have 1.5 break hours, but the function returns 3.5 because it is counting all the blank cells as containing a value > 5. What's really strange is it is only doing this some of the time. Every row has at least two blank cells, but only about a third of the sums are wrong. I can't figure out why. The columns are all formatted as a number. The value does update correctly if I manually enter a "0" in the blank cells, but this is a very large dataset and that would take forever. Thoughts? submitted by /u/andylefunk [link] [comments]