•1 min read•from Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community
Schrodinger's blank cell. What causes this value to echo differently than others when referenced.
Our take
Have you ever encountered a blank cell in Excel that behaves unexpectedly, like Schrödinger's cat—both empty and not empty at the same time? This phenomenon occurs when formulas interact with blank cells, sometimes treating them as zeros, while other times, they echo a different value entirely. Understanding why this happens can transform how you leverage your data. Join us as we delve into the nuances of blank cell behavior, uncovering insights that can enhance your spreadsheet skills and streamline your data analysis.
d3:e7 are data entered
g3 down to g7 is
=TEXTSPLIT(TEXTJOIN(",",,D3&","&SUBSTITUTE(E3,",",","&D3&",")),",")
m3 is
=G3:K8 and as expected, blank cells convert to zeros
look at n6 it is not a zero... but-- look at the formulas below as applied to h6
it's not 'blank' (i14/k14) but it is equal to a blank cell (i17/k17)
what is it? if I click it- it lights up as a spill cell from the left.. but why didn't it make n6 as a zero? (which is what caught my eye)
(edit, and you are all NUTS with your convoluted solutions to
https://www.reddit.com/r/excel/comments/1r2aqid/how_to_split_strings_and_join_values/ )
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- 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]
- 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]
- Trying to remove invisible characters (go to special -> blanks not finding all blank cells)Hi everyone, I’m at my wits end with this one. I’ve copy and pasted data a large chunk of data from one excel sheet to another that has a blank line or two between meaningful data. I want to remove all those blank cells, but when I use go to special -> blanks it says there isn’t any. I’ve tried using CLEAN, tried using TRIM, tried using both, tried using CODE to detect what character is in the cell, it comes back as #VALUE. The way the data was pulled was with the formula IFERROR(TEXTBEFORE(A1, “ - “,1)””). It isn’t treating “” as blank for whatever reason. Maybe an extra important detail: the original file is a .xls; new book is .xlsx. Using Office 365. Any help is appreciated! submitted by /u/newblood310 [link] [comments]
Tagged with
#Excel alternatives for data analysis#rows.com#Excel compatibility#row zero#big data management in spreadsheets#generative AI for data analysis#enterprise-level spreadsheet solutions#conversational data analysis#AI-driven spreadsheet solutions#real-time data collaboration#financial modeling with spreadsheets#intelligent data visualization#no-code spreadsheet solutions#Schrodinger's cell#blank cell#spill cell#TEXTSPLIT#TEXTJOIN#Excel#data entered