THANK YOU!!! This helped me to quickly correct a simple summing error. I copied data from a website and pasted into excel and although, numbers looked like numbers and were formatted as a number, excel did not recognize it as such. Thankfully, I hadn't done very many cells like that and therefore manually retyped and it corrected as I typed. What would you have done to correct that issue, if there was a large amount of data entered in this way? TIA
There are lots of ways to convert text to numbers. - Text to Columns feature - Convert to number on the Error Checking (Green Triangle flag) context menu - Copy a blank cell, then Paste Special > Add. If you Google it, you should find quite a few articles with options.
@@ExcelOffTheGrid Sorry, just seeing your response. I had Googled and done Text to Columns feature, but didn't work and thus was looking for a way to correct the issue with larger data targets.
Superb explanation - many thanks - but I have just created your scenario 3 in Excel 365, and using both TRUE and FALSE in the final argument of the VLOOKUP it does return the correct answer - so have Microsoft fixed this? I don't have the precision option ticked.
It didn’t work on Thursday when I recorded the video. It’s been broken for about 10 years before then. So I would be surprised if they suddenly put in something to fix that scenario. I think regional settings can also alter the impact. If they have put in a fix… amazing!!
I can still reproduce the same impact with different numbers. We reported an issue with 17 vs 15 digits of precision earlier in the year. So it might just be a coincidence of timing.
It is definitely meant to be 15 digits. That's why Credit Card numbers which have 16 digits don't work correctly in Excel. The last digit always gets changed to a zero.
THANK YOU!!! This helped me to quickly correct a simple summing error. I copied data from a website and pasted into excel and although, numbers looked like numbers and were formatted as a number, excel did not recognize it as such. Thankfully, I hadn't done very many cells like that and therefore manually retyped and it corrected as I typed. What would you have done to correct that issue, if there was a large amount of data entered in this way? TIA
There are lots of ways to convert text to numbers.
- Text to Columns feature
- Convert to number on the Error Checking (Green Triangle flag) context menu
- Copy a blank cell, then Paste Special > Add.
If you Google it, you should find quite a few articles with options.
@@ExcelOffTheGrid Sorry, just seeing your response. I had Googled and done Text to Columns feature, but didn't work and thus was looking for a way to correct the issue with larger data targets.
very useful explanation and advice.
Glad it was helpful!
Great explanation, knew about the extra number after a bunch of zeros, but didn't know it was caused by conversion to binary numbers. Thanks.
You're welcome! 😀
I knew to use the round function, but did not know the reasons behind it. NOW I know. Thanks.
Thanks Warren. You've got no more excuses now. We only expect perfect spreadsheets from you from now on 😁
Interesting. Thanks Mark
Thanks Kebin.
It was great to speak to you in person today. 👍
Superb explanation - many thanks - but I have just created your scenario 3 in Excel 365, and using both TRUE and FALSE in the final argument of the VLOOKUP it does return the correct answer - so have Microsoft fixed this? I don't have the precision option ticked.
It didn’t work on Thursday when I recorded the video. It’s been broken for about 10 years before then. So I would be surprised if they suddenly put in something to fix that scenario.
I think regional settings can also alter the impact.
If they have put in a fix… amazing!!
Actually, I’ve just tried it again… it now appears to calculate correctly. 🤔
Time for a bit more investigation.
@@ExcelOffTheGrid Excellent - I'm glad it wasn't just me. I guess MS are following you closely...
I can still reproduce the same impact with different numbers.
We reported an issue with 17 vs 15 digits of precision earlier in the year. So it might just be a coincidence of timing.
great video , thx for sharing
Thanks for watching! 😁
thank you
Thanks, didn't know excel causes that.
Pretty shocking right... at least now you won't get caught out by it. 😁
🤯💥💥
Pretty shocking, right.
maybe it is 15th digit after a dot?
It is definitely meant to be 15 digits. That's why Credit Card numbers which have 16 digits don't work correctly in Excel. The last digit always gets changed to a zero.
Totally disagree with your assessment the bulk of errors are created by humans.
Who else creates the errors?