Does anyone else here spend most of their work life in Microsoft Excel?
If so, then what do you think are the most powerful tools it has to offer?
Several years ago, I learned how to use conditional formatting and–more useful still–conditional formulas. Then, maybe two years ago or so, I learned how to use pivot tables, and that has proved to be the most important thing in Excel by far that I've learned. Then, last week, I finally got around to figuring out how to use lookup functions.
Excel is one of the worst things unleashed upon us by Microsoft. I detest working with it.
If you want to do database work, use a database. If you want to transfer data, use XML, delimited text files or proprietary file formats.
Quote from: GaryV on July 15, 2020, 04:00:17 PM
Excel is one of the worst things unleashed upon us by Microsoft. I detest working with it.
If you want to do database work, use a database. If you want to transfer data, use XML, delimited text files or proprietary file formats.
I do balancing and reporting. Quite useful for my purposes.
The reports I send out every week at the casino are in Excel. They don't use anything more advanced than basic addition and subtraction formulas, though.
The reports I compile for the two businesses I own are done in LibreOffice Calc. Not only does this let me avoid the exorbitant MS Office license costs, it doesn't have the obnoxious ribbon interface that all of the recent Offices have. (Most of the time, when I try to do anything halfway advanced in Excel, I end up hunting around the various ribbons for 30 minutes, give up, Google it, then find that feature isn't enabled on the ribbon by default so I have to manually force it to show up.)
I was pretty impressed when I got to use the Flash Fill feature in Excel for the first time. This detects when string data is changing by some identifiable pattern, and attempts–it's not perfect–to fill in other columns based on it. So far this is the only Excel feature that I miss in LibreOffice.
Quote from: GaryV on July 15, 2020, 04:00:17 PM
Excel is one of the worst things unleashed upon us by Microsoft. I detest working with it.
If you want to do database work, use a database. If you want to transfer data, use XML, delimited text files or proprietary file formats.
Spreadsheets existed before Excel was a thing. If Microsoft had never been founded, you'd be saying the same things about Lotus 1-2-3.
I'm a fan of relational databases as much as the next guy, but there is some degree of setup work to establishing them. Some things just aren't important enough to bother spinning up an SQL server, thinking up a solid database schema, or putting together a comfortable interface. And do
you want to be the one to teach middle manager Sherry what an SQL query is?
You should
never use a proprietary file format, especially if an open one that does the same thing exists. To use a proprietary format is to invite data loss by the responsible company breaking the associated product, not updating it for newer OSes, or going out of business.
I use Excel every day. There's probably over 30 files I need to look at in my normal workday. My largest files have about 160 or so tabs, or individual spreadsheets. My formulas can sometimes be up to 6 lines long (I drag the formula time up top down to see the entire thing at one time. A few of my larger spreadsheets have over 250 columns, representing each business day of the year.
I use multiple imbedded if/then formulas. You mentioned lookup formulas. What I found much more useful for my doing is Index/Match formulas. They work like lookup formulas, but usually you don't need the data to be in ABC or 123 order, so it's a lot more versatile. Hyperlinks - both the option and the formula - are a standard feature in my files. Conditional formatting, once it works right, is so useful there's very important information I don't even look at because the formatting highlights exactly what I need to know.
GaryV hinted at using other programs as a Database, but that's how I utilize Excel the best. Databases would probably work better, but here's the thing: I don't know how to use a database program. Other than Access, I don't have another program available to me at work. And I'm not going to try to learn how to use Access...only for me to need to teach up to 60 others that will need to view the databases. However, everyone I work with has a general knowledge of Excel, and I can show them the few additional things they need to know to utilize my Excel files. Many of my files are either emailed to numerous people, or utilized directly within my section. Very few of my files are only used by me, so I have to make sure others know how to use the data I present.
Even with all of this...I've never used Pivot tables. Some day, I'll learn them. But if you want to know how to write a single formula that includes iferror, if-or-and-then, and index-match statements, let me know!
Quote from: Scott5114 on July 15, 2020, 04:10:58 PM
I was pretty impressed when I got to use the Flash Fill feature in Excel for the first time. This detects when string data is changing by some identifiable pattern, and attempts–it's not perfect–to fill in other columns based on it. So far this is the only Excel feature that I miss in LibreOffice.
What I love is knowing how to override it. Click on a cell that has the number 303, then flash fill-down, and Excel will likely assume you want to increase by one each row: 303, 304, 305, 306... But, if you want it to be 303 for every row instead, then simply hit Ctrl+D after flash filling-down, and they all instantly change to 303.
Not only do I use it a lot for work, but I have five files that have all of our personal financial information. Easy and intuative program.
Quote from: SEWIGuy on July 15, 2020, 04:31:02 PM
I have five files that have all of our personal financial information
yikes
Quote from: Scott5114 on July 15, 2020, 04:32:47 PM
Quote from: SEWIGuy on July 15, 2020, 04:31:02 PM
I have five files that have all of our personal financial information
yikes
Yikes what? I don't have account numbers or personally identifible information in them or anything.
1. Our budget. Details income, expenses and savings targets for the next few months. I have that mapped out right now through December.
2. Credit card charges
3. Charitable contributions for the year
4. Retirement & Investment Balances
5. Estimated federal and state income tax obligations and payments. (This one is the coolest since I have formulas estimating our income and tax obligation based on marginal tax rates, witholding, estimated tax payments, deductions, etc. It's fun to play around in.)
Storing all of that information in an unencrypted Excel file on your computer is still a security risk. If someone gains access to the computer, they now know your total financial picture and where you keep your accounts. Chances are they can get personally identifiable information on you through some other file on your computer, since they're not going to look at just those Excel files. (Do you have zero files on your computer containing your name or address? Are you sure? Not even in your browser cache/autofill? Or your email client?)
From there it's a simple connect-the-dots process to social engineer access to the accounts themselves.
Quote from: Scott5114 on July 15, 2020, 05:10:41 PM
Storing all of that information in an unencrypted Excel file on your computer is still a security risk. If someone gains access to the computer, they now know your total financial picture and where you keep your accounts. Chances are they can get personally identifiable information on you through some other file on your computer, since they're not going to look at just those Excel files. (Do you have zero files on your computer containing your name or address? Are you sure? Not even in your browser cache/autofill? Or your email client?)
From there it's a simple connect-the-dots process to social engineer access to the accounts themselves.
I keep Excel files of my stuff as well.
People have mailboxes outside their home. Anyone can walk up, take it, walk away, and they'll have account numbers, names and addresses of everyone in that house.
People write checks. That is a laughingly insecure document. It has your name, address, bank name, your checking account number, and the bank's routing number. It has everything you need to wipe your account dry.
The security risk on computers is very low. If they're able to break into your computer, chances are they will easily bypass the encryption feature.
The Equifax breach had more to do with IDs being stolen than random personal computer break-ins. And talk about knowing one's entire financial history...it's nearly all right there!
Quote from: Scott5114 on July 15, 2020, 05:10:41 PM
Storing all of that information in an unencrypted Excel file on your computer is still a security risk. If someone gains access to the computer, they now know your total financial picture and where you keep your accounts. Chances are they can get personally identifiable information on you through some other file on your computer, since they're not going to look at just those Excel files. (Do you have zero files on your computer containing your name or address? Are you sure? Not even in your browser cache/autofill? Or your email client?)
From there it's a simple connect-the-dots process to social engineer access to the accounts themselves.
I will take my chances.
I almost like Access better. More automation and aggregation tools. If I need something in a XLSX format I can have Access export a copy.
Quote from: RobbieL2415 on July 15, 2020, 05:36:58 PM
I almost like Access better. More automation and aggregation tools. If I need something in a XLSX format I can have Access export a copy.
Excel is a math/statistics tool; Access is a database tool. Lots of people use Excel for things it isn't meant for, because the interface is more intuitive than Access, or they don't understand what Access is for.
I use it quite often at work but I don't build the data sheets that I once did years ago. I spend most of my time picking apart other people's Excel documents for the data that I want which fortunately is incredibly easy to do.
My father claims he used the software almost every day at his banking job and still uses it to keep track of budgeting. Online school as a result of the pandemic has been very reliant on the Google apps suite, particularly Google Docs and Slides. Google's equivalent to Excel is Google Sheets, which is used much less than the other two. If an assignment requires a table or a graph/cart, sheets is the go-to app. The rationale for using Google over Office is the district's usage of cheap Google Chromebooks, as well as its integration with Google Drive (district accounts have no storage cap, though relying on it as a long-term storage method is a bad idea, as the data will be wiped in two years).
The sharing of Google Docs and the ability to have multiple people working on it together is much better than Word. Extremely convenient.
Quote from: Scott5114 on July 15, 2020, 05:45:29 PM
Quote from: RobbieL2415 on July 15, 2020, 05:36:58 PM
I almost like Access better. More automation and aggregation tools. If I need something in a XLSX format I can have Access export a copy.
Excel is a math/statistics tool; Access is a database tool. Lots of people use Excel for things it isn't meant for...
Heck...i used it to design my kitchen/utility room area when I was remodeling it. I resized the rows and columns so the cells were squares. Each cell equaled 1 square inch of the room. It's a small area, roughly 10'6" by 13'10", and I really needed to maximize the space. I went thru about 50 various designs. Some were nearly identical with minor tweaks. Other designs were unworkable. But with Excel I could constantly save a design, open a new tab, copy the design over and play with it some more.
There is an interesting book "Humble Pi: When Math Goes Wrong in the Real World" by Matt Parker with a whole chapter dedicated to excel.
Fair use quote:
QuoteIn 2012 JPMorgan Chase lost a bunch of money; it's difficult to get a hard figure, but the agreement seems to be that it was around $6 billion. As is often the case in modern finance, there are a lot of complicated aspects to how the trading was done and structured (none of which I claim to understand). But the chain of mistakes featured some serious spreadsheet abuse, including the calculation of how big the risk was and how losses were being tracked. A Value at Risk (aka VaR) calculation gives traders a sense of how big the current risk is and limits what sorts of trades are allowed within the company's risk policies. But when that risk is underestimated and the market takes a turn for the worse, a lot of money can be lost.
Amazingly, one specific Value at Risk calculation was being done in a series of Excel spreadsheets with values having to be manually copied between them. I get the feeling it was a prototype model for working out the risk that was put into production without being converted over to a real system for doing mathematical modelling calculations. And enough errors accumulated in the spreadsheets to underestimate the VaR. An overestimation of risk would have meant that more money was kept safe than should have been, and because it was limiting trades it would have caused someone to investigate what was going on. An underestimation of VaR silently let people keep risking more and more money....
...
Specifically, after subtracting the old rate from the new rate, the spreadsheet divided by their sum instead of their average, as the modeler had intended. This error likely had the effect of muting volatility by a factor of two and of lowering the VaR.
One thing I learned about spreadsheets, especially complicated, formula-intense spreadsheets: try not to make any changes that will take effect on Friday, or before taking time off. If there's an error that's not discovered until late in the day, the effects can last all weekend, or you have to deal with it on your time off.
In the example kalvado posted above, those issues can be incredibly hard to detect. But it also speaks to the limitations many people have with Excel. It's like driving a car. Some people may say they're good drivers, but are they? Some people may say they're good Excel users, but when you find out the basic sum formula confuses them, it's too late.
Quote from: Scott5114 on July 15, 2020, 05:45:29 PM
Quote from: RobbieL2415 on July 15, 2020, 05:36:58 PM
I almost like Access better. More automation and aggregation tools. If I need something in a XLSX format I can have Access export a copy.
Excel is a math/statistics tool; Access is a database tool. Lots of people use Excel for things it isn't meant for, because the interface is more intuitive than Access, or they don't understand what Access is for.
I haven't used Access for a long while because I haven't needed to, but even when I might have, I learned to use Excel if I could, because for me it's so much more intuitive.
If I learn something in Excel, it's usually like riding a bike. I never forget it, even though I may get a little rusty and have to brush up a tad. With Access, for me, it's use-it-or-lose-it. If I didn't use Access for a week I'd forget practically everything I knew about it and need to learn it all over again. I just don't need that kind of aggravation if I can help it.
(https://imgs.xkcd.com/comics/algorithms.png)
The Humble Pi example is true in other top companies in the US. A friend of mine works at one and has built elaborate business processes around macro heavy pivot table filled Excel spreadsheets. I shake my head at that mess, much of it can be replaced with REAL code with no manual cutting and pasting of data coming from reports. Although I'm partly to blame when I showed him what that Macro button was for. Most people use the program as a flat file database though, something it wasn't meant for.
A real relational database with full SQL query capabilities is much better suited for that, but there is the setting up curve and the learning curve that most won't deal with. Access technically can do all this, but becomes annoyingly limited in other areas. Its likely best being used as a front end to something hosted on MS SQL Server and not much else!
Google kinda "bridged the gap" with Sheets though.
(https://imgs.xkcd.com/comics/spreadsheets.png)
Fun thread!
Microsoft hierarchy: Excel > Word > Power Point
I use Excel a
lot. I took an Excel course in school, so I know quite a bit about it, and would call myself a better-than-average Excel user with some degree of confidence - although one can never be
too sure about that.
When I first saw this thread, I was going to say I mostly use Excel for fun. But the more I think about it, I use it quite a bit at work, too. Probably at least 5-6 times a day: nothing too intense, but it's definitely an essential tool for data entry, reporting, and staying organized. Auto Sum is a fan fave, of course - that goes without saying. I'd go crazy without the ability to drag formulas down columns. And the ability see the sum and average of highlighted cells is super handy, too.
As for the fun stuff: there's the old history of me starting stats-related threads on this forum, which I would have used Excel to compile at the time. Least said, soonest mended on that subject, but that's really the least of my Excel adventures: I have many other sheets going at any given time. They're mostly road or population-related in some way. The biggest one has about 12 sheets, comparing 2000, 2010, and (soon) 2020 census population data for every region, county, and town in New York State. I got all the prep work done earlier in the year, so it's just been sitting around waiting for the 2020 census results for several months now.
One other quirk: I almost always use the "borders" feature and add my own borders to whatever cells I'm using, rather than just using "print gridlines" if needed. I often have several distinct tables on a single sheet, so border options like "outside border", "thick outside border", and "bottom double border" really come in handy.
Quote from: jeffandnicole on July 15, 2020, 07:26:27 PM
I resized the rows and columns so the cells were squares.
Guilty as charged. I have done that many more times than is healthy! :-D
Quote from: jeffandnicole on July 15, 2020, 07:26:27 PM
Quote from: Scott5114 on July 15, 2020, 05:45:29 PM
Quote from: RobbieL2415 on July 15, 2020, 05:36:58 PM
I almost like Access better. More automation and aggregation tools. If I need something in a XLSX format I can have Access export a copy.
Excel is a math/statistics tool; Access is a database tool. Lots of people use Excel for things it isn't meant for...
Heck...i used it to design my kitchen/utility room area when I was remodeling it. I resized the rows and columns so the cells were squares. Each cell equaled 1 square inch of the room. It's a small area, roughly 10'6" by 13'10", and I really needed to maximize the space. I went thru about 50 various designs. Some were nearly identical with minor tweaks. Other designs were unworkable. But with Excel I could constantly save a design, open a new tab, copy the design over and play with it some more.
Worst spreadsheet abuse I've done was during the pandemic, when I did a small pixel art icon for a friend and we were going back and forth over changes that needed to be done. Rather than trying to communicate which pixel needed to be moved where (which got frustrating), it ended up being easier to set up a Google Sheets spreadsheet with square cells, use cell background colors to recreate the icon, then set it as editable so we could both tinker with it at the same time.
For a room design project, I'd probably reach for Inkscape before I considered using Excel.
Creating art in Excel is a thing: https://mymodernmet.com/excel-art/
Quote from: jeffandnicole on July 15, 2020, 09:15:44 PM
One thing I learned about spreadsheets, especially complicated, formula-intense spreadsheets: try not to make any changes that will take effect on Friday, or before taking time off. If there's an error that's not discovered until late in the day, the effects can last all weekend, or you have to deal with it on your time off.
In the example kalvado posted above, those issues can be incredibly hard to detect. But it also speaks to the limitations many people have with Excel. It's like driving a car. Some people may say they're good drivers, but are they? Some people may say they're good Excel users, but when you find out the basic sum formula confuses them, it's too late.
forgetting a factor of 2 somewhere is more on a misprint side of things. And disadvantage of excel here is lack of ability to overview the formula and setup proper unit tests. If you use 6-line formulas in your spreadsheets, question is not IF you have mistakes in there, but HOW MANY you have.
ANother quote from Humble Pi
QuoteIn the aftermath of the Enron scandal of 2001, the Federal Energy Regulatory Commission published the results of its investigation into the corporation and the evidence behind it — which included around 0.5 million emails [....] a lot of spreadsheets as attachments.
Hermans and her colleagues searched through the email archive and were able to assemble a corpus of 15,770 real-world spreadsheets
[...]
Hermans found that 2,205 spreadsheets had one or more Excel error messages. Which means that around 24 per cent of all formula-containing spreadsheets contained an error. And those errors had company: the error-prone spreadsheets had an average of 585.5 mistakes each. An astonishing 755 spreadsheets had over a hundred errors, and one spreadsheet took first place with 83,273 errors.
[...]
But this is only a tiny subset of mistakes in spreadsheets: the obvious ones. Many more formula errors will be unaccounted for
To expand on my comments, I definitely use Excel for tracking things. Sure, I could use some project manager software, but for what I need to do that would be overkill.
For example, today I had to evaluate a list of jobs in our database, find out if they were active or scheduled, who is responsible for maintaining them, and find whether they did some specific activity on a specific server for which we will need to make changes. A colleague gave me a list in Excel, and I filled in the details about the jobs.
My wife uses a spreadsheet to calculate how much she is spending on buying music for church - song titles, publisher info, number of copies needed, price. Then calculate copies*price, apply any discount, and come up with a grand total. Sure, that's a great use for Excel.
But please don't make me try to consume Excel data into a database. The database has strictly defined columns. You can put anything you want into a spreadsheet cell. I had to attempt to use one spreadsheet that had 3 different dates in one cell, and then determine how those dates were being used. We finally gave up on it.
I use Excel a bunch, though not as seriously as before. Back around 1997-98, I taught myself how to use it by using a lot of pointless data. I rarely need as many formulas as before, but it still comes in handy for a lot of different things and I sometimes enjoy pointlessly formatting the heck out of a spreadsheet to make look just right.
I think it's one of the few Microsoft* products that's intuitive enough to use easily, or with complexity, because it usually tells you when and what mistake you've made. Contrast that with Word, which still has a mind of its own, requiring too many manual set-up restraints just to achieve a lot of things which could be easily performed in good 'ol Wordpad. I find myself typing or copying a lot more into Notepad and Wordpad and then copying it into Word for formatting to get around of its stubborn difficulties.
* copied Lotus 1-2-3
Quote from: Max Rockatansky on July 15, 2020, 06:02:50 PM
I spend most of my time picking apart other people's Excel documents for the data that I want which fortunately is incredibly easy to do.
This is primarily what I do at work. I hardly ever build an Excel file from the ground up, except for small personal endeavors. For work, I get daily and weekly Excel reports from our MSO, and manipulate those files for my purposes.
My Office hierarchy:
Outlook -> Word -> Access -> Excel -> Powerpoint -> Publisher
Quote from: kalvado on July 16, 2020, 08:32:32 AM
Quote from: jeffandnicole on July 15, 2020, 09:15:44 PM
One thing I learned about spreadsheets, especially complicated, formula-intense spreadsheets: try not to make any changes that will take effect on Friday, or before taking time off. If there's an error that's not discovered until late in the day, the effects can last all weekend, or you have to deal with it on your time off.
In the example kalvado posted above, those issues can be incredibly hard to detect. But it also speaks to the limitations many people have with Excel. It's like driving a car. Some people may say they're good drivers, but are they? Some people may say they're good Excel users, but when you find out the basic sum formula confuses them, it's too late.
forgetting a factor of 2 somewhere is more on a misprint side of things. And disadvantage of excel here is lack of ability to overview the formula and setup proper unit tests. If you use 6-line formulas in your spreadsheets, question is not IF you have mistakes in there, but HOW MANY you have.
ANother quote from Humble Pi
QuoteIn the aftermath of the Enron scandal of 2001, the Federal Energy Regulatory Commission published the results of its investigation into the corporation and the evidence behind it — which included around 0.5 million emails [....] a lot of spreadsheets as attachments.
Hermans and her colleagues searched through the email archive and were able to assemble a corpus of 15,770 real-world spreadsheets
[...]
Hermans found that 2,205 spreadsheets had one or more Excel error messages. Which means that around 24 per cent of all formula-containing spreadsheets contained an error. And those errors had company: the error-prone spreadsheets had an average of 585.5 mistakes each. An astonishing 755 spreadsheets had over a hundred errors, and one spreadsheet took first place with 83,273 errors.
[...]
But this is only a tiny subset of mistakes in spreadsheets: the obvious ones. Many more formula errors will be unaccounted for
(apologies...this is gonna be long!)
I, of course, take exception to long formulas are prone to errors. When creating large spreadsheets that are data intense, formulas can be written 2 ways: Long, which often includes multiple imbedded formulas, or short, but more helper cells, rows and columns are needed. Both strategies work based on the level of the user and what info you want to see along the way. My various spreadsheets use both options.
I can't comment about the Enron quote you cited because I don't know anything about it. But you can imagine the data that comes into play if a spreadsheet has over 83,000 errors. A lot of it comes down to testing, as you hinted. When I create my large spreadsheets, I know the results I want. If there's an error, it's usually clear cut...but not always. However, an error can happen within that long formula just as easily as it could happen in the original data, a simple formula, or a helper cell. I've seen errors occur as simple as this: In cell B2, the formula is =A1. Except, it should've been =A2. Copy this down 100,000 rows, and you have 100,000 errors.
And with testing, comes more testing. Get the answer right once, and you got one right answer. One error didn't reveal itself until a Monday, and this goes back to what I said earlier about Fridays and taking a day off: The error happened on a Friday. I wasn't in work that day. Mid-morning that Monday, someone from another department was confused why their activity on Friday wasn't recorded. I took a quick glance and saw we received their data. Then I took a look at another sheet and saw the numbers were wrong. I realized quickly what happened: When we enter negative figures into a field, the result is a certain dataset of accounts are used. When positive numbers are entered, which in this case only happens on Friday, I would get a different set of accounts. But because of a change I did earlier in the week within the workbook, the change caused something else to change within the workbook which I wasn't expecting...and the error happened.
So, for a few days the result I predicted would happen did. On that Friday, it wasn't. And the error lasted all weekend long, rather than being caught by me the same day it happened if I was in, or the next day, if it happened to occur on another day of the week.
To relate this to the Enron issue...which could have happened is someone wrote the formulas. They looked fine, and may have even resulted in correct answers....that day. But when more info was compiled, and without continuous secondary checks, the data may have resulted in incorrect answers that went undetected for a long time.
Another issue is copying and cutting. For what we do, with the formulas we utilize, I try to get my team to copy, paste, then delete the original data, rather than cut and paste. The formulas looking at the specific cells can change dramatically when cutting. This happens more so when the first or last cell in a formula is affected.
And, with apologies to Webny, I think too many people take a class in Excel, then believe they are sharper than most. Excel is a huge program, with thousands and thousands of formulas. A semester-long class will give someone knowledge of a few formulas, builds pivot tables, create a graph (bar AND pie), and learn how to widen margins. In my Division of 60, I'm one of 3 that are regarded as having above average Excel skills. And I probably know a few dozen formula/function types. However, what I also excel in (get it...excel, Excel...lol) is that I can look up other formulas and understand how to use them. And if I need help, there's some websites that will show you examples for almost anything you need, and if they don't, there are websites forums that are great to seek out answers. Mr. Excel is one such website. The people in there know their shit better than everyone on this forum combined about Excel.
And even of us 3 that know Excel well, none of us use it the same way. Take the Copy function. I like to right click, Copy. Another may use the ribbon and go to Home, then Copy. And the other person uses Control-C. None of us are wrong. It's just personal preference.
Here's a formula I use in a spreadsheet, and it's a fun one because of the data that is revealed
=if(iferror(index('Schedule'!A:A,match (rows('Schedule'!$1:133), 'Schedule'!$M:M,0)),"")<$H$2,iferror (index('Schedule'!A:A,Match (rows('Schedule'!$1:133), 'Schedule'!$M:M,0)),""),"")
It's hard to understand without the rest of the data shown, but the result is it's looking at a different sheet, and if we need more than a certain amount of money over and above what has been placed in other cells, then the result will be a date of when more money is needed. The dates are always listed in order, up to a specific number of dates in the future, which is also a separate input field.
But the point of showing that: You ain't gonna learn this in a semester course if that course is also showing you how to use a bolded boarder. This is gonna be learned by continuing to expand your Excel horizons. And if you like Excel for the basic stuff, find reasons to learn more about Excel.
Quote from: jeffandnicole on July 16, 2020, 02:54:02 PM
A semester-long class will give someone knowledge of a few formulas, builds pivot tables, create a graph (bar AND pie), and learn how to widen margins. In my Division of 60, I'm one of 3 that are regarded as having above average Excel skills. And I probably know a few dozen formula/function types.
And I once took a half-day class on Access that was offered at the company I worked for. After that class, I knew just enough to have an idea of what Access was capable of, but also to know that I wasn't really cut out to go any deeper. I then developed a plan for a database I wanted to use at work, but I pawned it off on a co-worker who designed Access databases as a hobby. Within a few weeks, he handed me the result of his efforts.
Quote from: jeffandnicole on July 16, 2020, 02:54:02 PM
Here's a formula I use in a spreadsheet, and it's a fun one because of the data that is revealed
=if(iferror(index('Schedule'!A:A,match (rows('Schedule'!$1:133), 'Schedule'!$M:M,0)),"")<$H$2,iferror (index('Schedule'!A:A,Match (rows('Schedule'!$1:133), 'Schedule'!$M:M,0)),""),"")
It's hard to understand without the rest of the data shown, but the result is it's looking at a different sheet, and if we need more than a certain amount of money over and above what has been placed in other cells, then the result will be a date of when more money is needed. The dates are always listed in order, up to a specific number of dates in the future, which is also a separate input field.
But the point of showing that: You ain't gonna learn this in a semester course if that course is also showing you how to use a bolded boarder. This is gonna be learned by continuing to expand your Excel horizons. And if you like Excel for the basic stuff, find reasons to learn more about Excel.
Examples of formulas I use at my job:
=IF(AND(B93=B92,C93=C92),"DUPLICATE","TRUCKROLL")
=VLOOKUP(A26,TechFiles!$A$2:$B$94,2,FALSE)
That's really as complicated as I get. But, six years ago, I didn't even have a clue how to do either one of those.
Quote from: jeffandnicole on July 16, 2020, 02:54:02 PM
This is gonna be learned by continuing to expand your Excel horizons. And if you like Excel for the basic stuff, find reasons to learn more about Excel.
Absolutely.
The only reason I learned how to do a pivot table is that fundamental changes happened to one aspect of my job, and someone at work showed me how to create one. Now that I've poked around and figured out how they actually work, I use pivot tables all the time.
Quote from: jeffandnicole on July 16, 2020, 02:54:02 PM
Excel is a huge program, with thousands and thousands of formulas.
This comment piqued my curiosity, and so I dug around a little bit, finding this list (https://support.microsoft.com/en-us/office/excel-functions-alphabetical-b3944572-255d-4efb-bb96-c6d90033e188), a quick count of which (I used Excel) shows that there are about 492 different Excel functions.
I would submit that if you're using the IF() function you should probably be writing an actual program to handle the data instead. Conditional logic gets complicated enough when you're using tools made for it; cramming it into a spreadsheet cell is just tempting fate.
I use if a lot with conditional formatting. It's good for seeing the status of a project at a glance.
Conditional formatting is one thing (if it produces the wrong result, it should be obvious). I was more referring to actual data synthesis using IF(), especially nested IF()s, since the syntax quickly becomes write-only and a real pain to debug.
I use Excel a lot at work and at home.
At work, mostly for tracking daily job activities and purchases, especially items we have used for jobs but have not been invoiced for yet. For this, it meets our needs better than QuickBooks, which we also use.
At home, I use it for some of my finances. I built a pretty sophisticated spreadsheet for projecting my income/expenses into retirement. I can plug in different retirement dates, and have it recalculate income, SS income, etc.
I use pivot tables a lot. My other favorite tools are CONCATENATE and VLOOKUP.
Quote from: frankenroad on July 17, 2020, 03:36:39 PM
CONCATENATE
I didn't know about that one. What is the advantage to using it? That is, what is the advantage of this...
=CONCATENATE(C5, " ", D5)
...over this...
=C5&" "&D5
?
It seems like a bunch of extra work for the same result. Is there something I'm missing?
Do they perform differently if C5 and D5 are integers (and without the space)? In most programming languages, & does bitwise addition.
Quote from: 1 on July 17, 2020, 04:39:28 PM
Do they perform differently if C5 and D5 are integers (and without the space)? In most programming languages, & does bitwise addition.
Nope, it doesn't appear they perform differently.
(https://i.imgur.com/Aowvm8x.jpg)
I believe that the CONCATENATE function existed prior to the ability to use the ampersand for the same thing. They just never got rid of it.
Quote from: SEWIGuy on July 17, 2020, 04:50:43 PM
I believe that the CONCATENATE function existed prior to the ability to use the ampersand for the same thing. They just never got rid of it.
If that's true, then it makes total sense. There would be no reason to get rid of it, or else a bunch of users' documents would stop functioning properly.
Quote from: CtrlAltDel on July 16, 2020, 03:37:49 PM
Quote from: jeffandnicole on July 16, 2020, 02:54:02 PM
Excel is a huge program, with thousands and thousands of formulas.
This comment piqued my curiosity, and so I dug around a little bit, finding this list (https://support.microsoft.com/en-us/office/excel-functions-alphabetical-b3944572-255d-4efb-bb96-c6d90033e188), a quick count of which (I used Excel) shows that there are about 492 different Excel functions.
Yeah, I did exaggerate that a bit, but what's relevant is most people know very few functions. That most people rarely use more than a few, maybe a dozen if they're going more intense work with it.
I'm a little annoyed they chose the ampersand for that. Most programming languages use the period as the concatenate operator, or else overload the "+" operator (meaning the formula =A1+66 would return "US 66" if C5="US ", or "76" if C5="10").
Excel's a great tool, but even after (30?) years, it still deals with the system clipboard like this:
(https://media3.giphy.com/media/G5BI94RrCkl8s/giphy.webp?cid=ecf05e47534121e5e8f7a8a461e2c2b185a960a774ceae62&rid=giphy.webp)
Quote from: Scott5114 on July 18, 2020, 02:00:36 AM
or else overload the "+" operator (meaning the formula =A1+66 would return "US 66" if C5="US ", or "76" if C5="10").
*whacks Scott with rolled-up newspaper* no! bad! no coercing numbers into strings!
I guess it's not so much the operator overloading that I'd have a problem with, but IMO if you want string concatenation, you need both operands to be strings (so
=A1+"66" would be fine, but not
=A1+66).
*grumble* this is why I don't work in JavaScript...
It's okay, I'm allowed to do that! I code in Perl! :P
I have used Excel and Access extensively in my career, although less though now that I'm an...upper middle manager, I suppose.
I don't believe VLOOKUP still requires data to be entered in order. It just finds the first instance of what you're looking for now.
So...lookup formulas, index/match if needed...there's a lot in Excel. The new-ish what-if capabilities are also helpful in my line of work.
But regarding Pivot Tables, I am constantly disappointed in them. I find just using formulas to give my workbooks more flexibility. Keep waiting for Microsoft to provide less stringent functionality as I hear people rave about them and haven't seen it come to fruition yet.
I also use Excel for non-work purposes as well. Love crunching numbers.
Quote from: SEWIGuy on July 17, 2020, 04:50:43 PM
I believe that the CONCATENATE function existed prior to the ability to use the ampersand for the same thing. They just never got rid of it.
That well could be - I've been using CONCATENATE for probably 20 years - I've never tried the & - but I will now that I know about it!! You CAN teach an old dog new tricks.
Quote from: frankenroad on July 20, 2020, 12:35:38 PM
Quote from: SEWIGuy on July 17, 2020, 04:50:43 PM
I believe that the CONCATENATE function existed prior to the ability to use the ampersand for the same thing. They just never got rid of it.
That well could be - I've been using CONCATENATE for probably 20 years - I've never tried the & - but I will now that I know about it!! You CAN teach an old dog new tricks.
It depends. I still use "+" instead of "=" to start a formula - a leftover from Quattro Pro for DOS days. At least in Office 2016 it still works - input is converted into "=+... "
Now my problem is that Office 365 no longer recognizes that as legitimate approach...
Has anyone noticed this change that seems to have taken place a few years ago?
If you copy a range of cells in Excel, then do any other non-viewing action (such as typing or formatting somewhere), then the data drops out of your clipboard and you cannot paste it–neither elsewhere in Excel nor in another program.
I had not. Normally if I'm copying something, I'm pasting directly thereafter.
Quote from: kphoger on April 14, 2022, 11:01:38 AM
Has anyone noticed this change that seems to have taken place a few years ago?
If you copy a range of cells in Excel, then do any other non-viewing action (such as typing or formatting somewhere), then the data drops out of your clipboard and you cannot paste it–neither elsewhere in Excel nor in another program.
If I copy data in any program and don't immediately paste it somewhere, it disappears from the clipboard, and I don't remember it being any different before.
To answer the original question, I took a data analytics course in the fall of 2020, and I use it extensively for work (and for its intended purpose). There are a lot of things that make it impressive, but my favorite feature is probably the ability to create geographic maps (say, a map of the continental US with each state shaded by how flat it is).
Quote from: abefroman329 on April 14, 2022, 01:24:12 PM
If I copy data in any program and don't immediately paste it somewhere, it disappears from the clipboard, and I don't remember it being any different before.
Really? Not for me.
I just did a test:
1. Type text ABCD into Word.
2. Highlight that text and hit Ctrl+C to copy.
3. Type a different line of text, EFGH.
4. Highlight ABCD and hit Delete.
5. Save the file to my desktop.
6. Close Word.
7. Open Excel, open a new blank worksheet.
8. Open a (second) new blank worksheet.
9. Type text WXYZ in a cell.
10. Click on a different cell, hit Ctrl+V to paste.
And, do you know what happened? ABCD (which I had copied in step 2) pasted into that cell just fine.
Now let's say I want to copy a table from Excel and paste it into an e-mail, which is something I do all the time at work.
1. Highlight the cells, hit Ctrl+C to copy.
2. Open a new blank e-mail.
3. Hit Ctrl+V to paste.
That works fine, the table pastes into the e-mail without a hitch.
But what
doesn't work is this:
1. Highlight the cells, hit Ctrl+C to copy.
2. Change the color of a completely different set of cells–maybe even from a completely different file.
3. Open a new blank e-mail.
4. Hit Ctrl+V to paste.
As soon as I do Step #2 above, the cells I had copied are no longer stored in my clipboard, and pasting into my e-mail does nothing.
Quote from: kphoger on April 14, 2022, 11:01:38 AM
Has anyone noticed this change that seems to have taken place a few years ago?
If you copy a range of cells in Excel, then do any other non-viewing action (such as typing or formatting somewhere), then the data drops out of your clipboard and you cannot paste it–neither elsewhere in Excel nor in another program.
That's been standard behavior in Excel since at least Office 2013 (which I still use very frequently), and perhaps a few versions before that.
Put another way, I don't remember Excel not behaving that way, and I have spent an embarrassingly large amount of time working in Excel over the past 27 years.
Weird. I distinctly remember it not being the case within the last dozen years.
Microsoft is basically loosing track of what they are doing. Three levels of copying feature in Excel are CutCopyPaste mode within excel; office clipboard; and system clipboard. Aaand... I think they lost the vision of how things should work, so whatever happens - it is no longer a bug, it is a feature.
Y'all are gonna be mad if you ever hear about Linux's middle-click paste...
I often use Excel to format the data both prior to importing the data into a database as well as data extracted from a database. In fact, there are web libraries that create Excel and OpenDocument files.
One powerful function is with the import of CSV files. If you had too many mangled imports in Excel by double-clicking on the CSV file, try Data → From Text/CSV. This will pre-load the data and you can transform the data formatting from Excel's guess to, oh say, a value into a text field. Especially when your number is a sixteen digit unique identifier, and you don't want Excel to change it to a exponential number.
Oh, yeah, opening CSVs in Excel (or any spreadsheet program) is asking for trouble, thanks to autoformatting. I had to help a friend of mine with his homework for over an hour because we just couldn't figure out how to get it to work in ArcGIS, because ArcGIS needed an X-digit number padded with leading zeroes and Excel kept throwing away the zeroes.
Generally I will only glance at a CSV in a spreadsheet program to get an idea of what the data is supposed to look like, then make any sort of changes in a text editor (or for really substantial/repetitive changes, with a Perl script).
Quote from: Scott5114 on April 16, 2022, 05:44:11 PM
Oh, yeah, opening CSVs in Excel (or any spreadsheet program) is asking for trouble, thanks to autoformatting. I had to help a friend of mine with his homework for over an hour because we just couldn't figure out how to get it to work in ArcGIS, because ArcGIS needed an X-digit number padded with leading zeroes and Excel kept throwing away the zeroes.
If you switch the format from "General" to "Number", and then switch to Custom you can set up the correct number of digits. The conversion to CSV will format the output the same way as the Custom format. Thus, a 10-digit number with a bunch leading zeroes is formatted using "0000000000" and can include a decimal place such as "0000000000.00". Just make sure you don't format using commas as thousands placeholders "0,000,000,000.00" because that will mess up your CSV output.
Over the years, I've used a combination of MS-Excel and MS-Notepad to clean up or reformat a bunch of different database formats (including old-fashioned Hollerith input tables). It's not been fun, but I've never found a commercially-available product that can handle all the junk that they've thrown at me. When the data got too messy, I just wrote a simple Visual Basic program to grab some of the data and clean up all of the excessive commas so that Excel could handle the rest of the problem.
On much older versions of Windows, these techniques also worked on ASCII, EBCDIC and hexadecimal codes (but the newer versions of MS-Excel wants to interpret those for you nowadays). If you are trying to preserve that type of data in Excel, you will need to format the fields as text field where the codes are in quotes (and then remove all of the quotes in a simple text editor). Geesh, does anybody else miss the days that HEDIT.EXE was a standard feature on a personal computer?
I don't have access to Excel, but I do have vi and emacs, so...
Ick, large numbers are something I deal with every day at work. Unfortunately, I get some of the data sent to me as an Excel file via e-mail with the numbers already having been converted to exponentials–such that changing them back into text rounds them to the nearest million or whatever. Which isn't exactly useful, considering that those numbers are serial numbers and the last several digits kind of matter.
Quote from: kalvado on April 15, 2022, 03:40:29 PM
Microsoft is basically loosing track of what they are doing. Three levels of copying feature in Excel are CutCopyPaste mode within excel; office clipboard; and system clipboard. Aaand... I think they lost the vision of how things should work, so whatever happens - it is no longer a bug, it is a feature.
Personally, the more ways they have to do something, the better.
Quote from: kphoger on April 16, 2022, 07:07:50 PM
Ick, large numbers are something I deal with every day at work. Unfortunately, I get some of the data sent to me as an Excel file via e-mail with the numbers already having been converted to exponentials–such that changing them back into text rounds them to the nearest million or whatever. Which isn't exactly useful, considering that those numbers are serial numbers and the last several digits kind of matter.
There is a book, "humble pi" - about mathematical and near-math issues all over the place. Excel is a subject of a separate chapter.
Phone numbers distorted is a simple one. Issue I really like, though, is more involved:
QuoteIn 2016 three intrepid researchers in Melbourne analysed eighteen journals which had published genome research between 2005 and 2015 and found a total of 35,175 publicly available Excel files associated with 3,597 different research papers...... the researchers were left with 987 Excel spreadsheets which had gene name errors introduced by Excel. In their sample, they found that 19.6 per cent of gene research crunched in Excel contained errors.
Examples are SEP15 and MARCH5 genes which Excel converts into dates.
You see things like that because there is no easy-to-use, well-understood database software that is as widely available as Excel is. So when people have a data grid, they don't know how to make the determination between when to use a spreadsheet and when to use a database...so a spreadsheet program gets pressed into use because it's "good enough".
Quote from: Scott5114 on April 16, 2022, 09:57:26 PM
You see things like that because there is no easy-to-use, well-understood database software that is as widely available as Excel is. So when people have a data grid, they don't know how to make the determination between when to use a spreadsheet and when to use a database...so a spreadsheet program gets pressed into use because it's "good enough".
It is more than that. Complex Excel spreadsheets are essentially programs made by non-programmers, with no testing, often no planning.
From the same book
QuoteThe European Spreadsheet Risks Interest Group (yes, that is a real organization, one dedicated to examining the moments when spreadsheets go wrong) estimates that over 90 per cent of all spreadsheets contain errors. Around 24 per cent of spreadsheets that use formulas contain a direct maths error in their computations.
Quote from: Scott5114 on April 16, 2022, 09:57:26 PM
You see things like that because there is no easy-to-use, well-understood database software that is as widely available as Excel is. So when people have a data grid, they don't know how to make the determination between when to use a spreadsheet and when to use a database...so a spreadsheet program gets pressed into use because it's "good enough".
That's what I do. Also...I know Excel. Everyone who I send the file to knows Excel. I don't know Access or any other database program. Most others I send the file to doesn't know Access or any other database program. So, I make it work for everyone involved.
Quote from: kalvado on April 16, 2022, 10:02:09 PM
From the same book
QuoteThe European Spreadsheet Risks Interest Group (yes, that is a real organization, one dedicated to examining the moments when spreadsheets go wrong) estimates that over 90 per cent of all spreadsheets contain errors. Around 24 per cent of spreadsheets that use formulas contain a direct maths error in their computations.
Guilty as charged. Also, some of the formulas I use are more complicated than they need to be because of my knowledge at the time of its creation.
That said, many people who say they "know Excel" can do =A1+A2, or a simple common formula. I mean, sure, I can get a great grade if a spelling test consists of 3 letter animals words. But the more complicated a test is, the more likely I'll get something wrong. No different with a more intense Excel file. It just takes someone...or a few people...to analyze the results to confirm they calculated properly.
Quote from: jeffandnicole on April 16, 2022, 10:30:39 PM
Quote from: Scott5114 on April 16, 2022, 09:57:26 PM
You see things like that because there is no easy-to-use, well-understood database software that is as widely available as Excel is. So when people have a data grid, they don't know how to make the determination between when to use a spreadsheet and when to use a database...so a spreadsheet program gets pressed into use because it's "good enough".
That's what I do. Also...I know Excel. Everyone who I send the file to knows Excel. I don't know Access or any other database program. Most others I send the file to doesn't know Access or any other database program. So, I make it work for everyone involved.
Well, I know how to use a hammer. Everyone who I talk to knows how to use a hammer. I don't know how to use an air conditioner manifold gauge or any other manifold gauge. Most others I talk to don't know how to use an air conditioner manifold gauge or any other manifold gauge.
But if I went to your house and started smacking your air conditioner around with a hammer you'd kick me off your property.
Now tell us how many Access databases have bad data or bad joins.
I'm not suggesting that Access is the solution, because it's not. That's the problem–there is no good, easy database application for your day-to-day office worker. Access is the closest to it, but it has a lot of failings, and the open-source competitors have even more because open-source programmers are content with writing SQL and see no real need to provide a graphical data-management tool.
DB Browser for SQLite is about the best solution that I've found, and it works well for my purposes, but I wouldn't feel comfortable giving it to Lynette from Accounting or anything. Something like phpMyAdmin could work, but it requires a server, and managing user-level access control is way too much for most people to want to mess with for the applications that tend to fall into the trap of spreadsheet-as-a-database.
Maybe I should write something.
Quote from: Scott5114 on April 16, 2022, 11:30:44 PM
Quote from: jeffandnicole on April 16, 2022, 10:30:39 PM
Quote from: Scott5114 on April 16, 2022, 09:57:26 PM
You see things like that because there is no easy-to-use, well-understood database software that is as widely available as Excel is. So when people have a data grid, they don't know how to make the determination between when to use a spreadsheet and when to use a database...so a spreadsheet program gets pressed into use because it's "good enough".
That's what I do. Also...I know Excel. Everyone who I send the file to knows Excel. I don't know Access or any other database program. Most others I send the file to doesn't know Access or any other database program. So, I make it work for everyone involved.
Well, I know how to use a hammer. Everyone who I talk to knows how to use a hammer. I don't know how to use an air conditioner manifold gauge or any other manifold gauge. Most others I talk to don't know how to use an air conditioner manifold gauge or any other manifold gauge.
But if I went to your house and started smacking your air conditioner around with a hammer you'd kick me off your property.
This isn't relevant because I'm sending what I created to a specific group of people who know how to use what I'm sending them (How do I know that? Because we converse about the data contained within.) If a new person is involved, I inform them of the features of the spreadsheets, as certain things such as hyperlinks aren't really visible, and I've hid the tabs at the bottom as the names aren't related to the data within each sheet.
Also, how many conversations do you really have with people about hammers and air conditioning manifold gauges?
Quote from: jeffandnicole on April 17, 2022, 12:16:22 AM
Quote from: Scott5114 on April 16, 2022, 11:30:44 PM
Quote from: jeffandnicole on April 16, 2022, 10:30:39 PM
Quote from: Scott5114 on April 16, 2022, 09:57:26 PM
You see things like that because there is no easy-to-use, well-understood database software that is as widely available as Excel is. So when people have a data grid, they don't know how to make the determination between when to use a spreadsheet and when to use a database...so a spreadsheet program gets pressed into use because it's "good enough".
That's what I do. Also...I know Excel. Everyone who I send the file to knows Excel. I don't know Access or any other database program. Most others I send the file to doesn't know Access or any other database program. So, I make it work for everyone involved.
Well, I know how to use a hammer. Everyone who I talk to knows how to use a hammer. I don't know how to use an air conditioner manifold gauge or any other manifold gauge. Most others I talk to don't know how to use an air conditioner manifold gauge or any other manifold gauge.
But if I went to your house and started smacking your air conditioner around with a hammer you'd kick me off your property.
This isn't relevant because I'm sending what I created to a specific group of people who know how to use what I'm sending them (How do I know that? Because we converse about the data contained within.) If a new person is involved, I inform them of the features of the spreadsheets, as certain things such as hyperlinks aren't really visible, and I've hid the tabs at the bottom as the names aren't related to the data within each sheet.
Also, how many conversations do you really have with people about hammers and air conditioning manifold gauges?
Your training department failed you, then. None of that is relevant to the difference between a spreadsheet and a database.
Also, since I'm working on a construction project and friends with an HVAC tech, a lot?
Quote from: Scott5114 on April 17, 2022, 12:41:06 AM
Quote from: jeffandnicole on April 17, 2022, 12:16:22 AM
Quote from: Scott5114 on April 16, 2022, 11:30:44 PM
Quote from: jeffandnicole on April 16, 2022, 10:30:39 PM
Quote from: Scott5114 on April 16, 2022, 09:57:26 PM
You see things like that because there is no easy-to-use, well-understood database software that is as widely available as Excel is. So when people have a data grid, they don't know how to make the determination between when to use a spreadsheet and when to use a database...so a spreadsheet program gets pressed into use because it's "good enough".
That's what I do. Also...I know Excel. Everyone who I send the file to knows Excel. I don't know Access or any other database program. Most others I send the file to doesn't know Access or any other database program. So, I make it work for everyone involved.
Well, I know how to use a hammer. Everyone who I talk to knows how to use a hammer. I don't know how to use an air conditioner manifold gauge or any other manifold gauge. Most others I talk to don't know how to use an air conditioner manifold gauge or any other manifold gauge.
But if I went to your house and started smacking your air conditioner around with a hammer you'd kick me off your property.
This isn't relevant because I'm sending what I created to a specific group of people who know how to use what I'm sending them (How do I know that? Because we converse about the data contained within.) If a new person is involved, I inform them of the features of the spreadsheets, as certain things such as hyperlinks aren't really visible, and I've hid the tabs at the bottom as the names aren't related to the data within each sheet.
Also, how many conversations do you really have with people about hammers and air conditioning manifold gauges?
Your training department failed you, then.
Also, since I'm working on a construction project and friends with an HVAC tech, a lot?
My training department is supposed to teach me every formula, option and nuance of Excel?
You're not getting what I'm saying. I'm saying, there are certain applications which shouldn't be attempted inside Excel to begin with because it's not the right tool for the job.
If you need to basically automate calculation of the same formulas over and over again–like, say, you're taking the length and width of a thing to calculate its area, then taking that area to calculate its weight–a spreadsheet is the right tool for the job.
If what you're really wanting to do is store a number of items, all of which have the same properties–like, say, making a list of customer names, addresses, contact information, and such–and you do that in Excel, you are creating an unholy mess. A database is the right tool for that job. If you use a spreadsheet, it is not a matter of if but when that decision is going to bite you in the ass. Worse yet, most people don't even have the training to realize the source of that sharp pain in their ass. That is the problem.
Quote from: Scott5114 on April 15, 2022, 09:59:28 PM
Y'all are gonna be mad if you ever hear about Linux's middle-click paste...
omg how cool is that, right?
plus copy/paste works to other devices, even. i use kde connect for my phone, so if i copy something on the computer, it's on the phone ready to be pasted, and vice versa.
Quote from: Scott5114 on April 16, 2022, 06:34:55 PM
I don't have access to Excel, but I do have vi and emacs, so...
some serious nerd cred, right there. respect.
i'm a nano man, myself.
Quote from: zachary_amaryllis on April 17, 2022, 01:49:01 AM
Quote from: Scott5114 on April 16, 2022, 06:34:55 PM
I don't have access to Excel, but I do have vi and emacs, so...
some serious nerd cred, right there. respect.
i'm a nano man, myself.
nano is pretty cool, and probably better than
vi for most people, but
vi is the one I learned first and is usually more likely to be preinstalled. My actual daily driver for plain-text editing is
kate.
emacs is a break-glass-in-case-of-emergency sort of thing; I have it installed but mostly to open it up once every few months to remind myself that I don't like pressing Ctrl
that much.
Haven't heard of KDE Connect, so I need to look into that . . .
Quote from: kphoger on April 16, 2022, 07:07:50 PM
Ick, large numbers are something I deal with every day at work. Unfortunately, I get some of the data sent to me as an Excel file via e-mail with the numbers already having been converted to exponentials–such that changing them back into text rounds them to the nearest million or whatever. Which isn't exactly useful, considering that those numbers are serial numbers and the last several digits kind of matter.
Quote from: kalvado on April 16, 2022, 09:46:38 PM
There is a book, "humble pi" - about mathematical and near-math issues all over the place. Excel is a subject of a separate chapter.
Phone numbers distorted is a simple one. Issue I really like, though, is more involved:
QuoteIn 2016 three intrepid researchers in Melbourne analysed eighteen journals which had published genome research between 2005 and 2015 and found a total of 35,175 publicly available Excel files associated with 3,597 different research papers...... the researchers were left with 987 Excel spreadsheets which had gene name errors introduced by Excel. In their sample, they found that 19.6 per cent of gene research crunched in Excel contained errors.
Examples are SEP15 and MARCH5 genes which Excel converts into dates.
Quote from: Scott5114 on April 16, 2022, 09:57:26 PM
You see things like that because there is no easy-to-use, well-understood database software that is as widely available as Excel is. So when people have a data grid, they don't know how to make the determination between when to use a spreadsheet and when to use a database...so a spreadsheet program gets pressed into use because it's "good enough".
Quote from: kalvado on April 16, 2022, 10:02:09 PMIt is more than that. Complex Excel spreadsheets are essentially programs made by non-programmers, with no testing, often no planning.
From the same book
QuoteThe European Spreadsheet Risks Interest Group (yes, that is a real organization, one dedicated to examining the moments when spreadsheets go wrong) estimates that over 90 per cent of all spreadsheets contain errors. Around 24 per cent of spreadsheets that use formulas contain a direct maths error in their computations.
You are all venturing into my world. Amazingly, I have caught Microsoft Excel mistakes on complex system simulations on several different occasions. When we analyzed the mistakes, there was nothing wrong with the algorithms. But either Microsoft or Windows itself was incorrectly computing the cell contents. The issue was reproducible several times, but the error would go away after the computer was rebooted. If you are wondering whether the computer itself could have generated these errors, indeed it could. But Microsoft's error detection/error correction is so efficient that it is unlikely to fail once much less on back-to-back occasions. (The old computer that I'm on right now probably has a million bit errors per second and hardly any of them cause any problems). But Microsoft is quite sloppy by not detecting whether its own Excel code encounters a glitch that generates an error.
Anyhow, this all creates a nightmare for guys like me who serve as the line of defense in safety certification. (Fortunately, our simulations were not for safety purposes even though they could be used for such, because that was always outside of our scope of work).
Quote from: Scott5114 on April 16, 2022, 11:30:44 PM
But if I went to your house and started smacking your air conditioner around with a hammer you'd kick me off your property.
Hammer time. One of my former lives on the railroad included repair work on 1950s electronic optic sensor systems interfaced with 1970s microprocessor-based data analytical systems (plus radio interfaces). Some of the interface boards were handmade. I would get an emergency call to fix a "computer problem" in the field and I would show up with rubber hammer and an old blow dryer with the heating element turned off. Invariably, they thought I was joking. The fellow I replaced always soldered-out a new capacitor (or two) on the homemade circuit boards, which I was certainly capable of doing. I fixed 100% of the problems my way, no soldering involved. Believe it or not, I learned the hammer and blower technique in a college course.
Quote from: Dirt Roads on April 17, 2022, 06:01:10 PM
Quote from: kphoger on April 16, 2022, 07:07:50 PM
Ick, large numbers are something I deal with every day at work. Unfortunately, I get some of the data sent to me as an Excel file via e-mail with the numbers already having been converted to exponentials–such that changing them back into text rounds them to the nearest million or whatever. Which isn't exactly useful, considering that those numbers are serial numbers and the last several digits kind of matter.
Quote from: kalvado on April 16, 2022, 09:46:38 PM
There is a book, "humble pi" - about mathematical and near-math issues all over the place. Excel is a subject of a separate chapter.
Phone numbers distorted is a simple one. Issue I really like, though, is more involved:
QuoteIn 2016 three intrepid researchers in Melbourne analysed eighteen journals which had published genome research between 2005 and 2015 and found a total of 35,175 publicly available Excel files associated with 3,597 different research papers...... the researchers were left with 987 Excel spreadsheets which had gene name errors introduced by Excel. In their sample, they found that 19.6 per cent of gene research crunched in Excel contained errors.
Examples are SEP15 and MARCH5 genes which Excel converts into dates.
Quote from: Scott5114 on April 16, 2022, 09:57:26 PM
You see things like that because there is no easy-to-use, well-understood database software that is as widely available as Excel is. So when people have a data grid, they don't know how to make the determination between when to use a spreadsheet and when to use a database...so a spreadsheet program gets pressed into use because it's "good enough".
Quote from: kalvado on April 16, 2022, 10:02:09 PMIt is more than that. Complex Excel spreadsheets are essentially programs made by non-programmers, with no testing, often no planning.
From the same book
QuoteThe European Spreadsheet Risks Interest Group (yes, that is a real organization, one dedicated to examining the moments when spreadsheets go wrong) estimates that over 90 per cent of all spreadsheets contain errors. Around 24 per cent of spreadsheets that use formulas contain a direct maths error in their computations.
You are all venturing into my world. Amazingly, I have caught Microsoft Excel mistakes on complex system simulations on several different occasions. When we analyzed the mistakes, there was nothing wrong with the algorithms. But either Microsoft or Windows itself was incorrectly computing the cell contents. The issue was reproducible several times, but the error would go away after the computer was rebooted. If you are wondering whether the computer itself could have generated these errors, indeed it could. But Microsoft's error detection/error correction is so efficient that it is unlikely to fail once much less on back-to-back occasions. (The old computer that I'm on right now probably has a million bit errors per second and hardly any of them cause any problems). But Microsoft is quite sloppy by not detecting whether its own Excel code encounters a glitch that generates an error.
Anyhow, this all creates a nightmare for guys like me who serve as the line of defense in safety certification. (Fortunately, our simulations were not for safety purposes even though they could be used for such, because that was always outside of our scope of work).
That's really disturbing. If it was fixed upon reboot, makes me wonder about the calculation engine completing properly.
Quote from: Scott5114 on April 16, 2022, 06:34:55 PM
I don't have access to Excel, but I do have vi and emacs, so...
:clap:
Hey fellas, if I shouldn't be using Excel to edit CSVs, what should I be using?
Quote from: abefroman329 on April 18, 2022, 08:29:58 AM
Hey fellas, if I shouldn't be using Excel to edit CSVs, what should I be using?
I have never had an issue importing .csvs into Excel.
Quote from: Rothman on April 17, 2022, 11:23:33 PM
Quote from: Dirt Roads on April 17, 2022, 06:01:10 PM
Quote from: kphoger on April 16, 2022, 07:07:50 PM
Ick, large numbers are something I deal with every day at work. Unfortunately, I get some of the data sent to me as an Excel file via e-mail with the numbers already having been converted to exponentials–such that changing them back into text rounds them to the nearest million or whatever. Which isn't exactly useful, considering that those numbers are serial numbers and the last several digits kind of matter.
Quote from: kalvado on April 16, 2022, 09:46:38 PM
There is a book, "humble pi" - about mathematical and near-math issues all over the place. Excel is a subject of a separate chapter.
Phone numbers distorted is a simple one. Issue I really like, though, is more involved:
QuoteIn 2016 three intrepid researchers in Melbourne analysed eighteen journals which had published genome research between 2005 and 2015 and found a total of 35,175 publicly available Excel files associated with 3,597 different research papers...... the researchers were left with 987 Excel spreadsheets which had gene name errors introduced by Excel. In their sample, they found that 19.6 per cent of gene research crunched in Excel contained errors.
Examples are SEP15 and MARCH5 genes which Excel converts into dates.
Quote from: Scott5114 on April 16, 2022, 09:57:26 PM
You see things like that because there is no easy-to-use, well-understood database software that is as widely available as Excel is. So when people have a data grid, they don't know how to make the determination between when to use a spreadsheet and when to use a database...so a spreadsheet program gets pressed into use because it's "good enough".
Quote from: kalvado on April 16, 2022, 10:02:09 PMIt is more than that. Complex Excel spreadsheets are essentially programs made by non-programmers, with no testing, often no planning.
From the same book
QuoteThe European Spreadsheet Risks Interest Group (yes, that is a real organization, one dedicated to examining the moments when spreadsheets go wrong) estimates that over 90 per cent of all spreadsheets contain errors. Around 24 per cent of spreadsheets that use formulas contain a direct maths error in their computations.
You are all venturing into my world. Amazingly, I have caught Microsoft Excel mistakes on complex system simulations on several different occasions. When we analyzed the mistakes, there was nothing wrong with the algorithms. But either Microsoft or Windows itself was incorrectly computing the cell contents. The issue was reproducible several times, but the error would go away after the computer was rebooted. If you are wondering whether the computer itself could have generated these errors, indeed it could. But Microsoft's error detection/error correction is so efficient that it is unlikely to fail once much less on back-to-back occasions. (The old computer that I'm on right now probably has a million bit errors per second and hardly any of them cause any problems). But Microsoft is quite sloppy by not detecting whether its own Excel code encounters a glitch that generates an error.
Anyhow, this all creates a nightmare for guys like me who serve as the line of defense in safety certification. (Fortunately, our simulations were not for safety purposes even though they could be used for such, because that was always outside of our scope of work).
That's really disturbing. If it was fixed upon reboot, makes me wonder about the calculation engine completing properly.
Millions of bit errors is a joke. If I remember correctly, current error rate is 1 per gigabyte of RAM per day.
However, complex systems are prone to bugs. Often one of a kind, hard to reproduce and so on. My gut feeling is that MS is not even trying to catch such things anymore.
Quote from: Dirt Roads on April 17, 2022, 06:11:54 PM
Quote from: Scott5114 on April 16, 2022, 11:30:44 PM
But if I went to your house and started smacking your air conditioner around with a hammer you'd kick me off your property.
Hammer time. One of my former lives on the railroad included repair work on 1950s electronic optic sensor systems interfaced with 1970s microprocessor-based data analytical systems (plus radio interfaces). Some of the interface boards were handmade. I would get an emergency call to fix a "computer problem" in the field and I would show up with rubber hammer and an old blow dryer with the heating element turned off. Invariably, they thought I was joking. The fellow I replaced always soldered-out a new capacitor (or two) on the homemade circuit boards, which I was certainly capable of doing. I fixed 100% of the problems my way, no soldering involved. Believe it or not, I learned the hammer and blower technique in a college course.
Network interface devices/units for analog telephony have been know to work
better after taking them off the side of the house and kicking them around the yard for a few minutes...
Quote from: abefroman329 on April 18, 2022, 08:29:58 AM
Hey fellas, if I shouldn't be using Excel to edit CSVs, what should I be using?
A CSV file is, at its base level, a text file that has each record on its own line and field values separated by commas. If a field has a comma, space, or line break in it, it must be surrounded by quotes that tell the parser to ignore anything between the quotes.
The following is a valid CSV.
"People posting on this page of this thread","Total post count"
zachary_amaryllis,719
zachary_amaryllis,719
Scott5114,14590
"Dirt Roads",1461
"Dirt Roads",1461
Rothman,9594
kkt,6107
abefroman329,3781
Rothman,9594
kalvado,4567
kphoger,21016
Note the quotes that are required around "Dirt Roads".
And indeed, if we open it in a spreadsheet program (LibreOffice in this case), we see that it functions as expected:
(https://i.imgur.com/BcT7gCG.png)
However, you can see the spreadsheet has right-aligned the post numbers, indicating that it recognizes them as numbers and is applying autoformatting to them. That autoformatting is what makes editing CSVs in spreadsheets so insidious, because it can result in data loss if the type of data being stored is a "string of numbers" like a serial number, as opposed to a number used to represent value.
So if you want to be 100% certain that you won't lose data, you need to open it in a plain text editor. (At least to sanity-check the CSV output after it's been edited in a spreadsheet.) Unfortunately, the plain text editor Windows ships with is Notepad, which is borderline useless for any sort of in-depth editing. As mentioned above, I like the Kate (https://kate-editor.org/) editor, which is available for Windows, but there are plenty of others.
Quote from: Scott5114 on April 18, 2022, 03:58:53 PM
Quote from: abefroman329 on April 18, 2022, 08:29:58 AM
Hey fellas, if I shouldn't be using Excel to edit CSVs, what should I be using?
A CSV file is, at its base level, a text file that has each record on its own line and field values separated by commas. If a field has a comma, space, or line break in it, it must be surrounded by quotes that tell the parser to ignore anything between the quotes.
The following is a valid CSV.
"People posting on this page of this thread","Total post count"
zachary_amaryllis,719
zachary_amaryllis,719
Scott5114,14590
"Dirt Roads",1461
"Dirt Roads",1461
Rothman,9594
kkt,6107
abefroman329,3781
Rothman,9594
kalvado,4567
kphoger,21016
Note the quotes that are required around "Dirt Roads".
And indeed, if we open it in a spreadsheet program (LibreOffice in this case), we see that it functions as expected:
(https://i.imgur.com/BcT7gCG.png)
However, you can see the spreadsheet has right-aligned the post numbers, indicating that it recognizes them as numbers and is applying autoformatting to them. That autoformatting is what makes editing CSVs in spreadsheets so insidious, because it can result in data loss if the type of data being stored is a "string of numbers" like a serial number, as opposed to a number used to represent value.
So if you want to be 100% certain that you won't lose data, you need to open it in a plain text editor. (At least to sanity-check the CSV output after it's been edited in a spreadsheet.) Unfortunately, the plain text editor Windows ships with is Notepad, which is borderline useless for any sort of in-depth editing. As mentioned above, I like the Kate (https://kate-editor.org/) editor, which is available for Windows, but there are plenty of others.
And of course there is a way to avoid that specific problem:
(https://i.stack.imgur.com/BvTLG.png)
It doesn't mean you cannot face a different issue, though.
I would say that CSV import, if files of similar format are imported once, needs to be worked out and possibly automated. At a base level, Excel is just too universal to fit specific purposes. (replace CSV import and Excel with your favorite task as you like)
If your task and file content is very different every time, though... Any software may give users hard time with that.
Putting the serial number in quotes in the CSV makes it interpreted as text, right?
Quote from: 1 on April 18, 2022, 05:29:53 PM
Putting the serial number in quotes in the CSV makes it interpreted as text, right?
Quotes merely tell a CSV parser that all values from there to the next quote are to be treated as one field. Otherwise a value like "Minneapolis, Minnesota" would incorrectly induce a field break. (This leads to the question of "what if a value includes a quote?" which is answered by treating two consecutive quotes as a literal quote character.) The CSV format makes no distinction between data types stored in its fields; it doesn't know if a string of characters is a literal string or a number. That determination is made by whatever tool is used to open and parse the data, which is why a text editor will not introduce errors in the file; it makes no attempt to determine data types and therefore the display will always reflect the actual data stored in the file.
Accordingly, then, the string
test,"02134" in LibreOffice using the default CSV-import settings is displayed as
2134, indicating that it has been parsed as a number.
To be fair to spreadsheets, the CSV format is notoriously messy and difficult to parse. (If you wanted to represent two consecutive literal quotes in a field, you would have to encode it as
"""""".) Eric S. Raymond specifically called out the failures of the CSV format in his book
The Art of Unix Programming (which is an excellent read for anyone interested in computers, whether or not they are programmers or Unix users, as it is more a chronicle of good software design practices) in a thorough enough way that I won't bother rehashing them here. But CSVs are easy to generate and, unlike more robust text-file formats like XML, don't bake any particulars about the data (such as field names or data types) into the structure of the file. That means that once you have gone through the ordeal of writing a CSV parser, you can reuse it for many different types of data. That flexibility is why the CSV format has stuck around, despite its flaws.
Doesn't seem like anyone uses TSVs anymore. It solves issues like your Minneapolis, Minnesota example.
Quote from: JayhawkCO on April 18, 2022, 06:06:20 PM
Doesn't seem like anyone uses TSVs anymore. It solves issues like your Minneapolis, Minnesota example.
Tabs have the downside of being difficult to distinguish from runs of spaces when viewed by a human in a text editor. Especially when a field is long enough that it is only 1 or 2 characters away from the next tab stop. Some editors compensate for this by displaying tabs differently (Kate adds a small chevron graphic to the beginning of tabs) but it's still frustrating.
On Unix, you usually see colon-delimited fields, with literal colons represented by
\:, literal newlines represented by
\n, and literal backslashes represented by
\\. (This could theoretically lead to backslash explosion much like the quote example given above, but it's much cleaner since there will always be an even number of backslashes, and backslashes are far less likely to occur in most data than quotes are.) This is so simple to parse I'm pretty confident that I, a rank amateur, could probably have a working parser going in 30 minutes.
Quote from: 1 on April 18, 2022, 05:29:53 PM
Putting the serial number in quotes in the CSV makes it interpreted as text, right?
It helps to convert your cells to text format
before pasting-as-values into them. But that doesn't help when the data I get
from outside the company has already been number-format-ruined.
So the whole number to date thing Excel forces is fun.
I get that if I put in "9-21" and it wants to change it to "21-Sep", OK, I get it.
When I put in "9-41" and it changes it to "41-Sep", I get a little pissed off.
Quote from: SectorZ on April 18, 2022, 08:33:09 PM
So the whole number to date thing Excel forces is fun.
I get that if I put in "9-21" and it wants to change it to "21-Sep", OK, I get it.
When I put in "9-41" and it changes it to "41-Sep", I get a little pissed off.
In Numbers, the Mac equivalent of Excel:
9-20 → September 20, current year
9-40 → September 1, 2040
9/20 → September 20, current year
9/40 → 0.225
1-49 → January 1, 2049
1-50 → January 1, 1950
Quote from: SectorZ on April 18, 2022, 08:33:09 PM
So the whole number to date thing Excel forces is fun.
I get that if I put in "9-21" and it wants to change it to "21-Sep", OK, I get it.
When I put in "9-41" and it changes it to "41-Sep", I get a little pissed off.
Did you ever try to microwave something for 7 min 77 sec ( 7-7-7-start)?
Joke making the rounds: What do Excel and incels have in common? They often think it's a date when it really isn't.
Quote from: kurumi on April 19, 2022, 12:22:50 PM
Joke making the rounds: What do Excel and incels have in common? They often think it's a date when it really isn't.
:-D
Quote from: kurumi on April 19, 2022, 12:22:50 PM
Joke making the rounds: What do Excel and incels have in common? They often think it's a date when it really isn't.
This is the first time I've heard an incel joke that was actually funny. :D
Quote from: kalvado on April 18, 2022, 09:07:05 PM
Quote from: SectorZ on April 18, 2022, 08:33:09 PM
So the whole number to date thing Excel forces is fun.
I get that if I put in "9-21" and it wants to change it to "21-Sep", OK, I get it.
When I put in "9-41" and it changes it to "41-Sep", I get a little pissed off.
Did you ever try to microwave something for 7 min 77 sec ( 7-7-7-start)?
I am often microwaving things for 90 seconds:
9 0 start
instead of
1 3 0 start
Quote from: kurumi on April 19, 2022, 12:22:50 PM
Joke making the rounds: What do Excel and incels have in common? They often think it's a date when it really isn't.
:-D
Yeah, Excel is a great quick and dirty tool for getting a general picture of data or a one-shot calculation. But if you're using the data a lot or if it's really critical it's not a substitute for a database which will barf if you try to add numbers and words.
At least on my microwave, if you set it for 7:77, it automatically changes to 8:17 when you hit [Start].
Another common Excel date format issue for me is when it stores them as timestamps but only displays them as dates. So, for example, I might have a dataset with 2000 lines. Within that set are data relating to three different dates. Then I go to filter the sheet by the "Date" column, only to find out that it only looks like dates. It's actually 60 different timestamps:
3/30/2022 12:00:05 AM
3/30/2022 12:00:12 AM
3/30/2022 12:01:10 AM
3/30/2022 12:01:41 AM
3/31/2022 12:00:04 AM
3/31/2022 12:00:33 AM
3/31/2022 12:01:01 AM
etc.
This, apparently because the original export data that created the file I was sent also included times, and the data wasn't all generated at the exact same second each day.
Aarrgghh!
Quote from: kkt on April 19, 2022, 02:08:22 PM
Quote from: kalvado on April 18, 2022, 09:07:05 PM
Did you ever try to microwave something for 7 min 77 sec ( 7-7-7-start)?
I am often microwaving things for 90 seconds:
9 0 start
instead of
1 3 0 start
Or 8 - 8 - start or 9 - 9 - start - because then you're moving your finger one less time.
Quote from: GaryV on April 19, 2022, 02:28:23 PM
Quote from: kkt on April 19, 2022, 02:08:22 PM
Quote from: kalvado on April 18, 2022, 09:07:05 PM
Did you ever try to microwave something for 7 min 77 sec ( 7-7-7-start)?
I am often microwaving things for 90 seconds:
9 0 start
instead of
1 3 0 start
Or 8 - 8 - start or 9 - 9 - start - because then you're moving your finger one less time.
Yes, exactly. And on mine the display shows however you set the timer. If you set the timer to 99 seconds, it'll display seconds only as it counts down. If you set it to 1:09 it'll display minutes and seconds as it counts down.
Quote from: GaryV on April 19, 2022, 02:28:23 PM
Or 8 - 8 - start or 9 - 9 - start - because then you're moving your finger one less time.
You certainly aren't the only one!
Quote from: kphoger on February 11, 2015, 11:41:18 PM
The numbers I punch into the microwave appear random to the uninitiated eye, but really I just try to keep the buttons close together. I'm much more likely to zap for 41 seconds than 40 seconds because the 1 is directly above the 4. Earlier today, I reheated a mug of tea for 71 seconds. After hitting Start, I was kicking myself because I realized I wouldn't have had to skip over any buttons if only I had entered it as 1:11 instead. Wow, this sure sounds really weird when I explain it in text.
Quote from: Tom958 on February 12, 2015, 05:40:46 AM
Quote from: Pete from Boston on February 12, 2015, 12:27:30 AM
I use one number on the microwave at a pop: :33, 2:22, :66, etc. This is a habit I call laziness.
Me, too!
Quote from: kkt on April 19, 2022, 02:08:22 PM
Quote from: kalvado on April 18, 2022, 09:07:05 PM
Quote from: SectorZ on April 18, 2022, 08:33:09 PM
So the whole number to date thing Excel forces is fun.
I get that if I put in "9-21" and it wants to change it to "21-Sep", OK, I get it.
When I put in "9-41" and it changes it to "41-Sep", I get a little pissed off.
Did you ever try to microwave something for 7 min 77 sec ( 7-7-7-start)?
I am often microwaving things for 90 seconds:
9 0 start
instead of
1 3 0 start
Quote from: kurumi on April 19, 2022, 12:22:50 PM
Joke making the rounds: What do Excel and incels have in common? They often think it's a date when it really isn't.
:-D
Yeah, Excel is a great quick and dirty tool for getting a general picture of data or a one-shot calculation. But if you're using the data a lot or if it's really critical it's not a substitute for a database which will barf if you try to add numbers and words.
90 seconds is a legitimate timing, especially if other intervals in same batch are something like 30 and 60 seconds; or you deal with the pump doing 0.5 oz per second. But 7 min 77 sec is not a good representation. Two of microwaves I used over time responded differently. One did 77 seconds and then 7 full minutes, the other instantly switched to 8.17
Better than 41st of september, though.
Quote from: kalvado on April 19, 2022, 03:13:40 PM
Two of microwaves I used over time responded differently. One did 77 seconds and then 7 full minutes, the other instantly switched to 8.17
So they did the same thing, then?
Quote from: kphoger on April 19, 2022, 03:16:35 PM
Quote from: kalvado on April 19, 2022, 03:13:40 PM
Two of microwaves I used over time responded differently. One did 77 seconds and then 7 full minutes, the other instantly switched to 8.17
So they did the same thing, then?
They both accepted the input and ran for same amount of time. But displays were different.
I wonder if more modern smarter ones would give out a error in such case, though.
Quote from: kkt on April 19, 2022, 02:08:22 PMI am often microwaving things for 90 seconds:
9 0 start
instead of
1 3 0 start
I often use a stove timer that works similarly (except with hours and minutes rather than minutes and seconds). However, if I enter "9 0," it flashes "Err" briefly (to tell me I should be ashamed of myself) before it starts displaying the time remaining as "1:30."
I don't see how a microwave would accept 90 seconds but not 7:77. A better challenge would be to try microwaving something for 99:61.
In the interest of science, I just tried it here at work:
:90 → changes to 1:30 when I hit [Start]
98:70 → changes to 99:10 when I hit [Start]
99:99 → no change, just starts counting down to 99:98 then 99:97 et seq.
Quote from: kkt on April 19, 2022, 02:08:22 PM
Quote from: kalvado on April 18, 2022, 09:07:05 PM
Quote from: SectorZ on April 18, 2022, 08:33:09 PM
So the whole number to date thing Excel forces is fun.
I get that if I put in "9-21" and it wants to change it to "21-Sep", OK, I get it.
When I put in "9-41" and it changes it to "41-Sep", I get a little pissed off.
Did you ever try to microwave something for 7 min 77 sec ( 7-7-7-start)?
I am often microwaving things for 90 seconds:
9 0 start
instead of
1 3 0 start
On my microwave, I'd hit the "+30 seconds" button 3 times, since it automatically starts when that button is pressed, so that also saves me a keystroke over entering any time 2:00 or less.
Quote from: Scott5114 on April 19, 2022, 04:27:58 PM
On my microwave, I'd hit the "+30 seconds" button 3 times, since it automatically starts when that button is pressed, so that also saves me a keystroke over entering any time 2:00 or less.
Simply pressing [1] on mine starts it for one minute, and [Start] adds 30 seconds. So I actually press [1] [Start] in order to heat something for 90 seconds.
Quote from: kphoger on April 19, 2022, 04:43:39 PM
Quote from: Scott5114 on April 19, 2022, 04:27:58 PM
On my microwave, I'd hit the "+30 seconds" button 3 times, since it automatically starts when that button is pressed, so that also saves me a keystroke over entering any time 2:00 or less.
Simply pressing [1] on mine starts it for one minute, and [Start] adds 30 seconds. So I actually press [1] [Start] in order to heat something for 90 seconds.
I used to have a microwave like that (1-6 were all labeled with "quick start" and would start the microwave for 1-6 minutes) but I hated it because I tend to have an allergic reaction to non-orthogonality. There was an "enter time" button you could press to bypass it, but I feel like I should be able to freely enter time by default and any quick-start buttons should be separate functions.
Quote from: kphoger on April 19, 2022, 02:26:24 PM
At least on my microwave, if you set it for 7:77, it automatically changes to 8:17 when you hit [Start].
Another common Excel date format issue for me is when it stores them as timestamps but only displays them as dates. So, for example, I might have a dataset with 2000 lines. Within that set are data relating to three different dates. Then I go to filter the sheet by the "Date" column, only to find out that it only looks like dates. It's actually 60 different timestamps:
3/30/2022 12:00:05 AM
3/30/2022 12:00:12 AM
3/30/2022 12:01:10 AM
3/30/2022 12:01:41 AM
3/31/2022 12:00:04 AM
3/31/2022 12:00:33 AM
3/31/2022 12:01:01 AM
etc.
This, apparently because the original export data that created the file I was sent also included times, and the data wasn't all generated at the exact same second each day.
Aarrgghh!
=LEFT...
Done.
Quit your whining.
Quote from: Scott5114 on April 19, 2022, 05:01:17 PM
Quote from: kphoger on April 19, 2022, 04:43:39 PM
Quote from: Scott5114 on April 19, 2022, 04:27:58 PM
On my microwave, I'd hit the "+30 seconds" button 3 times, since it automatically starts when that button is pressed, so that also saves me a keystroke over entering any time 2:00 or less.
Simply pressing [1] on mine starts it for one minute, and [Start] adds 30 seconds. So I actually press [1] [Start] in order to heat something for 90 seconds.
I used to have a microwave like that (1-6 were all labeled with "quick start" and would start the microwave for 1-6 minutes) but I hated it because I tend to have an allergic reaction to non-orthogonality. There was an "enter time" button you could press to bypass it, but I feel like I should be able to freely enter time by default and any quick-start buttons should be separate functions.
really depends on how fine control you want. If 30 seconds is the minimum step you need, those quick buttons make things easy. Stopping the run few seconds early is another option.
Quote from: Rothman on April 19, 2022, 05:30:58 PM
Quote from: kphoger on April 19, 2022, 02:26:24 PM
Another common Excel date format issue for me is when it stores them as timestamps but only displays them as dates. So, for example, I might have a dataset with 2000 lines. Within that set are data relating to three different dates. Then I go to filter the sheet by the "Date" column, only to find out that it only looks like dates. It's actually 60 different timestamps:
3/30/2022 12:00:05 AM
3/30/2022 12:00:12 AM
3/30/2022 12:01:10 AM
3/30/2022 12:01:41 AM
3/31/2022 12:00:04 AM
3/31/2022 12:00:33 AM
3/31/2022 12:01:01 AM
etc.
This, apparently because the original export data that created the file I was sent also included times, and the data wasn't all generated at the exact same second each day.
Aarrgghh!
=LEFT...
Done.
Quit your whining.
I just tried that by using
=LEFT(A2,5) but can't seem to get the resultant number back into date format. It just sticks there as a 5-digit number.
However, what does appear to work is using
=INT(A2).
Quote from: kphoger on April 19, 2022, 06:11:04 PM
Quote from: Rothman on April 19, 2022, 05:30:58 PM
Quote from: kphoger on April 19, 2022, 02:26:24 PM
Another common Excel date format issue for me is when it stores them as timestamps but only displays them as dates. So, for example, I might have a dataset with 2000 lines. Within that set are data relating to three different dates. Then I go to filter the sheet by the "Date" column, only to find out that it only looks like dates. It's actually 60 different timestamps:
3/30/2022 12:00:05 AM
3/30/2022 12:00:12 AM
3/30/2022 12:01:10 AM
3/30/2022 12:01:41 AM
3/31/2022 12:00:04 AM
3/31/2022 12:00:33 AM
3/31/2022 12:01:01 AM
etc.
This, apparently because the original export data that created the file I was sent also included times, and the data wasn't all generated at the exact same second each day.
Aarrgghh!
=LEFT...
Done.
Quit your whining.
I just tried that by using =LEFT(A2,5) but can't seem to get the resultant number back into date format. It just sticks there as a 5-digit number.
However, what does appear to work is using =INT(A2).
Text to Columns, uncheck the box next to Tab on step 2, select Text on step 3. From there, you can use
=LEFT(A2,5) to get the month, day, and year, and then convert those five digits to dates.
You're welcome.
Or just do =INT(A2). :rolleyes:
Quote from: kphoger on April 19, 2022, 08:52:56 PM
Or just do =INT(A2). :rolleyes:
Going full circle, you could just do what one of my old bosses always wanted us to do dump the output from Excel into a CSV file (or TXT file) to clean up the data. :spin:
Quote from: Scott5114 on April 19, 2022, 04:27:58 PM
Quote from: kkt on April 19, 2022, 02:08:22 PM
Quote from: kalvado on April 18, 2022, 09:07:05 PM
Quote from: SectorZ on April 18, 2022, 08:33:09 PM
So the whole number to date thing Excel forces is fun.
I get that if I put in "9-21" and it wants to change it to "21-Sep", OK, I get it.
When I put in "9-41" and it changes it to "41-Sep", I get a little pissed off.
Did you ever try to microwave something for 7 min 77 sec ( 7-7-7-start)?
I am often microwaving things for 90 seconds:
9 0 start
instead of
1 3 0 start
On my microwave, I'd hit the "+30 seconds" button 3 times, since it automatically starts when that button is pressed, so that also saves me a keystroke over entering any time 2:00 or less.
I don't have a +30 seconds button. I have a +1 minute button, though.
Quote from: abefroman329 on April 18, 2022, 08:29:58 AM
Hey fellas, if I shouldn't be using Excel to edit CSVs, what should I be using?
emacs :)
One thing about people saying to use a proper database instead...
My Excel files are not just export data: they're also import data. For example, I export data from an MSO's live online database (accessed via VPN) in Excel format, then I do all my data manipulation with sorting and formulas and pivot tables and all that jazz. But then I take the final product and upload it into our own company's payroll WMS (created in-house). So, the data initially comes to me in Excel format, and then I need it to be in Excel format at the end of the process as well.
Quote from: kphoger on April 20, 2022, 10:16:05 AM
One thing about people saying to use a proper database instead...
My Excel files are not just export data: they're also import data. For example, I export data from an MSO's live online database (accessed via VPN) in Excel format, then I do all my data manipulation with sorting and formulas and pivot tables and all that jazz. But then I take the final product and upload it into our own company's payroll WMS (created in-house). So, the data initially comes to me in Excel format, and then I need it to be in Excel format at the end of the process as well.
Yeah.
Another thing about people saying to use a proper database instead: I don't know how to use Access, my company isn't going to pay for Access training, and neither am I. And SQL would be a total nonstarter, as I barely know how to use it, yet that would make me the SQL expert at my company.
SQL isn't hard in and of itself. There's basically only 10 commands or so. It's how to make it actually return the info you want that's the challenge.
Quote from: kphoger on April 19, 2022, 08:52:56 PM
Or just do =INT(A2). :rolleyes:
Yeah, dates are stored as integers and times as fractional parts of a day, so, using INT(A2) will give you what you want. If you want just the time, use A2 − INT(A2), since there's no FRAC() function.
Quote from: CtrlAltDel on April 20, 2022, 01:45:54 PM
since there's no FRAC() function
You can just take it modulo 1.
I have trained myself to use both Excel and Access. While I wouldn't consider myself an expert by any means, I'd say they are straightforward for someone accustomed to thinking of data in tabular format.
What seems to me to be de-emphasized, if not missing, in the preceding discussion of importing/manipulating/exporting data in CSV (i.e., a pure text-based format) for tasks that need to be performed periodically is consideration of how the workflow can be structured so that a given task can be handled noninteractively from start to finish. Excel's apparently dysfunctional parsing behavior, and the possibility of using Access as an alternative, don't come into play when it is a program or script that handles the importing, parsing, calculation/analysis, and export from start to finish.
Most of my downloader scripts are designed to do this in one fashion or another--go to this web server, identify available documentation packages, determine which ones haven't already been downloaded before, download them, and log them so they aren't downloaded again in the future. So are a few selector scripts (e.g., find the 10% or so of Ohio DOT, PennDOT, and Michigan DOT contracts that have signing quantities) and signing sheet extractor scripts (e.g., find the sheets in a PDF plans set that have keywords associated with signing and extract them).
Quote from: kphoger on April 20, 2022, 10:16:05 AM
One thing about people saying to use a proper database instead...
My Excel files are not just export data: they're also import data. For example, I export data from an MSO's live online database (accessed via VPN) in Excel format, then I do all my data manipulation with sorting and formulas and pivot tables and all that jazz. But then I take the final product and upload it into our own company's payroll WMS (created in-house). So, the data initially comes to me in Excel format, and then I need it to be in Excel format at the end of the process as well.
In an ideal world, those should be SQL files at both ends of the process, and (depending on the type of data manipulation you do) the manipulation could probably be done more efficiently in SQL as well.
Is SQL easy? Not really. But then again, once you get to the point you're pivoting tables and having to figure out odd edge behaviors like what the integer function does to dates, you're not doing anything easy in Excel either.
Quote from: Scott5114 on April 20, 2022, 03:54:43 PM
Quote from: kphoger on April 20, 2022, 10:16:05 AM
One thing about people saying to use a proper database instead...
My Excel files are not just export data: they're also import data. For example, I export data from an MSO's live online database (accessed via VPN) in Excel format, then I do all my data manipulation with sorting and formulas and pivot tables and all that jazz. But then I take the final product and upload it into our own company's payroll WMS (created in-house). So, the data initially comes to me in Excel format, and then I need it to be in Excel format at the end of the process as well.
In an ideal world, those should be SQL files at both ends of the process, and (depending on the type of data manipulation you do) the manipulation could probably be done more efficiently in SQL as well.
Is SQL easy? Not really. But then again, once you get to the point you're pivoting tables and having to figure out odd edge behaviors like what the integer function does to dates, you're not doing anything easy in Excel either.
1. My process has a LOT of steps. But I do the same steps every day, so I hardly have to think about them anymore.
2. If I really felt like it, I could probably condense a lot of them into a macro.
Quote from: kphoger on April 20, 2022, 04:02:48 PM
1. My process has a LOT of steps. But I do the same steps every day, so I hardly have to think about them anymore.
2. If I really felt like it, I could probably condense a lot of them into a macro.
This does seem like it could be an ideal candidate for automation (though the rest of my post discusses caveats to that). A macro would probably save you a lot of time and effort, though generally I eschew in-program macros as much as possible in favor of general-purpose scripting languages like Perl and Python. Perl is less likely to break my scripts through an update (and if it does I can specify a particular version number for it to use), and it avoids tying me to a specific spreadsheet package because I have a macro library based on it. (Of course, in a corporate environment, "relationship ended with X software package, now Y software package is my best friend" is an additional occupational hazard of macro usage.)
My current "boss" has extensive experience as a Perl programmer himself and has a mantra that if we find ourselves doing the same thing more than three times we should raise the possibility of scripting it. This has led to things like the preparation of thumbnail images in Photoshop, a somewhat straightforward task that usually took ten minutes or so, getting collapsed down to merely typing 6 letters into the command line and verifying the result is correct.
Quote from: J N Winkler on April 20, 2022, 02:21:01 PM
What seems to me to be de-emphasized, if not missing, in the preceding discussion of importing/manipulating/exporting data in CSV (i.e., a pure text-based format) for tasks that need to be performed periodically is consideration of how the workflow can be structured so that a given task can be handled noninteractively from start to finish. Excel's apparently dysfunctional parsing behavior, and the possibility of using Access as an alternative, don't come into play when it is a program or script that handles the importing, parsing, calculation/analysis, and export from start to finish.
This is another excellent point–it is far easier to deal with data fresh from a database programmatically, rather than parsing a spreadsheet file and hoping to recreate the behavior of any formulas found therein. Most modern programming languages have modules in the standard distribution for querying a database file and returning the result set in whatever native data structure (such as an array or hash/dictionary) makes the most sense for that language.
Unfortunately, the concept of scripting is so de-emphasized on Windows as to be nonexistent by default in anything other than the form of batch scripting. This means that in situations where there's a corporate IT overseeing the environment and denying an ordinary user from installing software of their choice, including a script interpreter, slapping together a script to automate a repetitive task is simply not an option. And most computer users just...don't care to learn how to automate tasks, because it is rare that a business's culture encourages it, despite the fact that it could save them many man-hours on labor that doesn't need to be done by a human and free them up to do things that are more important.
Which is kind of a shame for business productivity. I took pity on a friend of mine once, wrote her a Perl script, and gave her instructions for how to smuggle it onto an OS X machine and run it. The script allowed her to accomplish a year's worth of work in a single day.
I've avoided macros thus far because I don't want the process to become so automated that I forget how to do it manually–and then the format of the export data change such that I have to do it manually.
Quote from: kphoger on April 20, 2022, 04:21:35 PM
I've avoided macros thus far because I don't want the process to become so automated that I forget how to do it manually–and then the format of the export data change such that I have to do it manually.
Couldn't you just read the macro code at that point to refresh your memory as to what it was doing?
If the problem is "what if I forget how to read the code I wrote"–couldn't you just use comments to document it for your later usage? (Which is what a good programmer should do anyway.)
Quote from: Scott5114 on April 20, 2022, 04:28:33 PM
Quote from: kphoger on April 20, 2022, 04:21:35 PM
I've avoided macros thus far because I don't want the process to become so automated that I forget how to do it manually–and then the format of the export data change such that I have to do it manually.
Couldn't you just read the macro code at that point to refresh your memory as to what it was doing?
If the problem is "what if I forget how to read the code I wrote"–couldn't you just use comments to document it for your later usage? (Which is what a good programmer should do anyway.)
Yes.
Another problem is that one or two of the steps would likely need to be changed at least once a year (for various reasons having to do with how contractors get paid by an MSO). I'd rather use muscle memory every day and also be thoroughly engaged in the process than have to periodically re-record a dozens-step-long macro.
Quote from: kphoger on April 20, 2022, 04:32:23 PM
Quote from: Scott5114 on April 20, 2022, 04:28:33 PM
Quote from: kphoger on April 20, 2022, 04:21:35 PM
I've avoided macros thus far because I don't want the process to become so automated that I forget how to do it manually–and then the format of the export data change such that I have to do it manually.
Couldn't you just read the macro code at that point to refresh your memory as to what it was doing?
If the problem is "what if I forget how to read the code I wrote"–couldn't you just use comments to document it for your later usage? (Which is what a good programmer should do anyway.)
Yes.
Another problem is that one or two of the steps would likely need to be changed at least once a year (for various reasons having to do with how contractors get paid by an MSO). I'd rather use muscle memory every day and also be thoroughly engaged in the process than have to periodically re-record a dozens-step-long macro.
Microsoft macros are actually programs written in a scripting language called Visual Basic for Applications (VBA). It is baked into both Excel and Word (and probably PowerPoint too, though I don't remember). This was the scripting language of choice for 13-year-old me when I wanted to screw around in computer class and actually do something interesting after I'd already done the assignment of "type this example business letter into Word and format it". It is a full-on scripting language that is basically a pared-down version of the full Visual Basic.NET programming language that can be used to create full-fledged Windows programs. With VBA, you can even create dialog boxes with fully functional buttons, text boxes, check boxes, and so on (not that that's too useful for most Excel macros, but it makes 13-year-old kids who hadn't yet convinced their mom to buy them a copy of Visual Basic 6 on eBay feel like they're hot shit).
When you record a macro, Excel is actually translating what you do to VBA code and saving it. You can pop this macro open in the in-program code editor and inspect and change it to your liking. You can also write macros from scratch without using the record function. So rather than re-recording the macro, when the process changes, you merely have to open the code editor and change the relevant code. (This would be easier if you wrote it from scratch than if you used the record function, because to get it to work you would already be familiar with what each of the functions you're invoking do.)
Quote from: Scott5114 on April 20, 2022, 04:28:33 PM
Quote from: kphoger on April 20, 2022, 04:21:35 PM
I've avoided macros thus far because I don't want the process to become so automated that I forget how to do it manually–and then the format of the export data change such that I have to do it manually.
Couldn't you just read the macro code at that point to refresh your memory as to what it was doing?
If the problem is "what if I forget how to read the code I wrote"–couldn't you just use comments to document it for your later usage? (Which is what a good programmer should do anyway.)
Ideally one is writing code for one's workplace, not for oneself. The problem isn't "what if I suddenly forget what my code does", the problem is "what if I am hit by a truck crossing the street and my successor has to figure out what's going on".
Quote from: kkt on April 20, 2022, 06:47:51 PM
Ideally one is writing code for one's workplace, not for oneself. The problem isn't "what if I suddenly forget what my code does", the problem is "what if I am hit by a truck crossing the street and my successor has to figure out what's going on".
Well, in my case, the answer to that question is "they're screwed". My co-worker (yes, one co-worker and no one else) can cover my daily duties while I'm out of the office, but nobody but me knows how to do my weekly, bi-weekly, and monthly tasks.
I started writing up SOPs, but it gets tedious after a dozen pages of step-by-step Excel functions.
Many times what really happens is not ideal.
Quote from: kphoger on April 20, 2022, 06:56:10 PM
Quote from: kkt on April 20, 2022, 06:47:51 PM
Ideally one is writing code for one's workplace, not for oneself. The problem isn't "what if I suddenly forget what my code does", the problem is "what if I am hit by a truck crossing the street and my successor has to figure out what's going on".
Well, in my case, the answer to that question is "they're screwed". My co-worker (yes, one co-worker and no one else) can cover my daily duties while I'm out of the office, but nobody but me knows how to do my weekly, bi-weekly, and monthly tasks.
I started writing up SOPs, but it gets tedious after a dozen pages of step-by-step Excel functions.
What's really funny is when you have someone like you and management either fires them for some frivolous reason, or else pisses them off enough that they quit with no notice. Then the whole department finds out in excruciating detail exactly how much smoother things were having that person around.
subthread "A"
Quote from: J N Winkler on April 20, 2022, 02:21:01 PM
What seems to me to be de-emphasized, if not missing, in the preceding discussion of importing/manipulating/exporting data in CSV (i.e., a pure text-based format) for tasks that need to be performed periodically is consideration of how the workflow can be structured so that a given task can be handled noninteractively from start to finish. Excel's apparently dysfunctional parsing behavior, and the possibility of using Access as an alternative, don't come into play when it is a program or script that handles the importing, parsing, calculation/analysis, and export from start to finish.
Quote from: Scott5114 on April 20, 2022, 04:14:08 PM
This is another excellent point–it is far easier to deal with data fresh from a database programmatically, rather than parsing a spreadsheet file and hoping to recreate the behavior of any formulas found therein. Most modern programming languages have modules in the standard distribution for querying a database file and returning the result set in whatever native data structure (such as an array or hash/dictionary) makes the most sense for that language.
Unfortunately, the concept of scripting is so de-emphasized on Windows as to be nonexistent by default in anything other than the form of batch scripting. This means that in situations where there's a corporate IT overseeing the environment and denying an ordinary user from installing software of their choice, including a script interpreter, slapping together a script to automate a repetitive task is simply not an option. And most computer users just...don't care to learn how to automate tasks, because it is rare that a business's culture encourages it, despite the fact that it could save them many man-hours on labor that doesn't need to be done by a human and free them up to do things that are more important.
Which is kind of a shame for business productivity. I took pity on a friend of mine once, wrote her a Perl script, and gave her instructions for how to smuggle it onto an OS X machine and run it. The script allowed her to accomplish a year's worth of work in a single day.
subthread "B"
Quote from: kkt on April 20, 2022, 06:47:51 PM
Ideally one is writing code for one's workplace, not for oneself. The problem isn't "what if I suddenly forget what my code does", the problem is "what if I am hit by a truck crossing the street and my successor has to figure out what's going on".
Quote from: kphoger on April 20, 2022, 06:56:10 PM
Well, in my case, the answer to that question is "they're screwed". My co-worker (yes, one co-worker and no one else) can cover my daily duties while I'm out of the office, but nobody but me knows how to do my weekly, bi-weekly, and monthly tasks.
I started writing up SOPs, but it gets tedious after a dozen pages of step-by-step Excel functions.
Quote from: Scott5114 on April 20, 2022, 07:24:55 PM
What's really funny is when you have someone like you and management either fires them for some frivolous reason, or else pisses them off enough that they quit with no notice. Then the whole department finds out in excruciating detail exactly how much smoother things were having that person around.
I was planning on responding to subthread "A" earlier, and now subthread "B" shows up. These themes were closely related at my employer. We had a situation where the firm developed simulation software for its bread-and-butter work and then won a project in another market that was keenly interested in that particularly type of analytical process for their market. Then I got hired with background in three of those four areas. Oddly, this project required me to develop simulation software for their market (ergo, that fourth area that I didn't have experience). We were extremely successful with the rapid development phase, but things got tough when trying to standardize the software into a one-size-fits-all platform.
Anyhow, we tackled a project that was too complicated for the one-size-fits-all platform and the other programmer couldn't keep up with the software changes for the project. We were still in the DOS days, and had to hand-crank the simulations using Lotus 1-2-3. There were all sorts of crazy issues trying to pass data back-and-forth between databases, the modeling input tables, the modeling output tables and the simulation spreadsheets. Pretty much all the issues in subthread "A" and subthread "B" occurred on that project.
Quote from: kphoger on April 18, 2022, 07:12:53 PM
Quote from: 1 on April 18, 2022, 05:29:53 PM
Putting the serial number in quotes in the CSV makes it interpreted as text, right?
It helps to convert your cells to text format before pasting-as-values into them. But that doesn't help when the data I get from outside the company has already been number-format-ruined.
New frustration!
My job title is 'Audit', and the bulk of my job description is twofold:
1. To make sure field techs are billing our company correctly for the work they perform, because we don't want to pay them for more than they're entitled to. Techs are not paid hourly, but rather based on what sort of work is required for each job (piecework).
2. To make sure the MSOs we work for are paying us correctly for the work our field techs perform, because we don't want them to pay us less than we're entitled to.
Part of this process involves tracking chargebacks for things like damages, lost equipment, failed QCs, etc. When it comes to lost cable/internet/phone/security CPE (customer premise equipment–such as a rental modem or DVR box), I keep a running list of chargebacks, just in case we ever end up getting charged twice for the same item. This could happen if the item were later found, recycled through the system, reissued to our company, and then subsequently lost again later. The other day, I thought I had my first case of that happening.
I have the serial number column in Excel formatted to highlight duplicate values, and I had one pop up red the other day. And I was all prepared to dispute the charges–about ready, in fact, to hit 'Send' on my e-mail to alert the MSO of my dispute. But then I noticed something...
This particular model of modem uses an 18-digit all-numeric serial number. Because Excel likes to convert such numbers into scientific notation and lop off significant digits, I have to format cells as text–which I do
before pasting-as-values into the cell. But apparently that's not quite how things work in Excel.
You see, even though the cells are supposedly formatted as text, and even though each cell correctly displays all 18 digits of the serial number, Excel nevertheless apparently doesn't bother to read all of the digits when checking for duplicate values. As it turns out, the two serial numbers on my list–that Excel determined were duplicates–were only identical to the first 15 digits.
Specifically, they were these two numbers:
212930061077217513
212930061077217161
When the data came over from the MSO, they were each treated as numbers. The cell displayed '2.1293E+17', and the formula bar showed a stored value of '212930061077217000' (doing away with the last three significant digits). However, on my own running list files, they are formatted as text and show the full number string in both display and formula bar.
I just barely caught this error in time to not dispute the chargeback.
(https://i.imgur.com/ZWilsSz.jpg)
Geez. That's a hell of a showstopper there.
I wonder if you might be better served by storing that data in some sort of database. Microsoft Access would work if you have access (snrk) to it. Personally, I keep track of that kind of thing in DB Browser for SQLite, but it is probably just a hair technical for most users, so if you have to share that data with someone else it may not be the best choice.
I am so distrustful anymore of programs mangling important data that I'll save it in a .txt file if it's not too complicated. Lately I've gotten in the habit of using JSON for anything that looks like a series of key/value pairs:
{
"invoice date": "2022-10-14",
"market": "TUL",
"tech_nbr": 71336,
"serial_number": "212930061077217513", # quotes force this to be parsed as a string
"moved_to_lost_status": "2022-10-07"
}
{
"invoice date": "2022-10-14",
"market": "TUL",
"tech_nbr": 71336,
"serial_number": "M11639TRO692",
"moved_to_lost_status": "2022-10-07"
}
The benefit of this, of course, is that it works on every computer under the sun with no special software, just Notepad. It can be easily searched with the same. If you have access to a scripting facility, it is very easy to parse and do something interesting with (most scripting languages have a prebuilt JSON parser these days). The downside is that it is pretty tedious to add large amounts of data to by hand.
You lost me at Access. :-D
Well, that and the three or four terms I don't even understand...
Maybe a picture will help.
(https://i.imgur.com/WWIRpvc.png)
This database, which contains all of the road signs I've drawn for work, may as well be one of my internal organs at this point. (Portions pixelated to protect the innocent.) It works more or less just like a spreadsheet, except for the fact that there's no formatting, no formulas, and data types can only be declared on a per-column, rather than per-cell, basis. (Unlike some database programs, SQLite is more like a spreadsheet in that it doesn't really care if you put text in a number field or vice-versa. It will just by default treat data as what type you told it it was. So my "invoice" column there is a text column, so even though I am entering numeric data in there, it's treating it as text, thus why it's left-aligned. What it does not do is force the data into the column's format; I couldn't take that "244" on line 756 and do math to it, because it's in text format.) You can also tell it you want a column to only contain unique values, and it will pop up an error message if you try to enter a duplicate.
If I want to filter the data, those filter boxes up top do that. So if I want to see only, say, signs from Minnesota, I can put "MN" in the state filter and it will show only the MN designs. If I want to filter it further and show only completed designs from Minnesota, I can add "1" to the "completed" column and it will show only rows where state = "MN" and completed = "1".
If I want to get more advanced, and I have a really complicated query I want to do, I can go to the "Execute SQL" tab there and write an expression in Structured Query Language that will get me the precise data I want. (Or modify it, even. If, say, Texas seceded from the union I would be able to write a SQL statement that would take all instances where state = TX and change it to country = "TEX" and state = NULL, by writing one statement, rather than manually changing every row.)
Quote from: kphoger on October 31, 2022, 02:31:36 PMYou lost me at Access. :-D
Well, that and the three or four terms I don't even understand...
I'd just add to what Scott is saying by noting that you are trying to perform blockout: in this case each object (lost or damaged piece of equipment) is blocked out by an unique identifier (serial number) so that the associated operation (reimbursement) is performed only once.
This is literally what I do almost every time I write a downloader to keep me up to date on a given agency's highway construction plans--I look for an unique identifier for each plans set (or documentation package), and then design the downloader to look for it, log it when it downloads the corresponding file, and filter all new prospective downloads by the log to ensure nothing is downloaded twice. Sometimes the identifier is the URL, sometimes a filename, sometimes a database key number, and sometimes a combination of these and other parameters.
There are many, many ways to do it, but it nearly always is easier when the information you need is in plain text format and is organized in a structured way. It doesn't have to be JSON, XML, or CSV--as long as it's plain text, find and findstr (NT batch) or grep and awk (Unix) can handle it at the command line or within batch files (NT batch) or shell scripts (Unix). If you structure the information such that each object is on its own line in a text file, then you can go line-by-line using for loops of various types.
Even if I thought I were capable of learning all this technobabble you guys are saying... at this point in my life, suggesting I switch away from Excel is akin to suggesting I divorce my wife just because she elbows me in the face every so often when rolling over in bed.
(No, my wife doesn't elbow me in bed.)
I find that if I want to convert numbers to text, especially when Excel wants to default to scientific notation, just formatting the cell doesn't work. Neither does format tjen copy/paste values.
Instead, highlight the column, then do a Data : Text to Columns step. Most times this is used to split data from one column to multiple columns. But in this case, you're not going to split. In the first step, select Fixed Width, then in the second step don't add any columm breaks, and in the third step,you can select Text instead of Number, and that actually converts the value to a text value within the same column.
I'm like kphoger. I used Excel a lot before I retired. It was a handy tool. I used it for calculations. I used it as a quick-and-dirty database at times. I used it to organize requirements for developmental projects. It does a lot of things I'll never use like macros, functions, and pretty graphs. It's like the other Office programs that I use, Word and PowerPoint. They have a zillion capabilities I'll never need and I hate them all but find them all too useful to give up, even in retirement. At least Office is reasonably priced at $70/year these days, which I find is worth it rather than rely on the free Sun Office.
I don't understand not using functions with Excel. They take tasks that take an hour or more and reduce the time down to minutes you can count on one hand.
I've used macros in Excel by just copying code of the Internet ("Oh, that looks like something I'm trying to do...").
I've used Access for real database work and data organization, but I invariably run a query through Access' interface (sometimes modifying SQL directly a little -- I have very limited knowledge) and export to Excel.
Ah, JSON. I've only used that to view code from the Internet a few times.
Excel is very useful. I used it often at work too - mostly for projecting budgets out to the end of the fiscal year to see if we're in trouble. It is way too enthusiastic about automatically reformatting numbers to scientific notation. That's the scorpion napping in your boot.
Quote from: Rothman on October 31, 2022, 06:17:32 PMI don't understand not using functions with Excel. They take tasks that take an hour or more and reduce the time down to minutes you can count on one hand.
I wouldn't be much of an Excel user if it wasn't for the data analytics bootcamp I completed a couple years ago, and even now, I will do some calculations manually if it's going to take too long to clean the data.
Quote from: kphoger on October 31, 2022, 01:38:52 PM
New frustration!
I have the serial number column in Excel formatted to highlight duplicate values, and I had one pop up red the other day. And I was all prepared to dispute the charges–about ready, in fact, to hit 'Send' on my e-mail to alert the MSO of my dispute. But then I noticed something...
This particular model of modem uses an 18-digit all-numeric serial number. Because Excel likes to convert such numbers into scientific notation and lop off significant digits, I have to format cells as text–which I do before pasting-as-values into the cell. But apparently that's not quite how things work in Excel.
You see, even though the cells are supposedly formatted as text, and even though each cell correctly displays all 18 digits of the serial number, Excel nevertheless apparently doesn't bother to read all of the digits when checking for duplicate values. As it turns out, the two serial numbers on my list–that Excel determined were duplicates–were only identical to the first 15 digits.
Specifically, they were these two numbers:
212930061077217513
212930061077217161
When the data came over from the MSO, they were each treated as numbers. The cell displayed '2.1293E+17', and the formula bar showed a stored value of '212930061077217000' (doing away with the last three significant digits). However, on my own running list files, they are formatted as text and show the full number string in both display and formula bar.
Just in case you haven't solved this issue yet, a simple way to manually add a single quote character " ' " to the start of each long number that is red flagged. This will force the $number$ to be treated as a $string$.
Specifically, change the two numbers to forced strings:
'212930061077217513
'212930061077217161
If you don't want to manipulate the original data (as an auditor, I wasn't allowed to) you can create a separate column that uses a VisualBasic function to force the number to be convered to text. In the older versions of VisualBasic (and Excel), there was a simple function STR$(variable) that would allow you to not manipulate the original data. The new function is TEXT(variable,length) and it appears that setting length to zero will adapt to numbers of any length (someone might need to double check this).
Anywhoosit, the two cells that have long numbers are E2 and E48. In the other column you would want to have the function =TEXT($E$2,0) and =TEXT($E$48,0) to force both of those numbers to text. All of the other cells in that column can have =E1, =E3, =E4 etc. since those values are already text strings.
Quote from: jemacedo9 on October 31, 2022, 05:25:46 PM
Instead, highlight the column, then do a Data : Text to Columns step. Most times this is used to split data from one column to multiple columns. But in this case, you're not going to split. In the first step, select Fixed Width, then in the second step don't add any columm breaks, and in the third step,you can select Text instead of Number, and that actually converts the value to a text value within the same column.
I just tried this. No difference, non-duplicate values are still highlighted as duplicates.
As I said earlier, my solution for turning them into text is to format an empty column as text, then paste-as-values into that column–not the other way around.
Quote from: Dirt Roads on October 31, 2022, 09:23:04 PM
Anywhoosit, the two cells that have long numbers are E2 and E48. In the other column you would want to have the function =TEXT($E$2,0) and =TEXT($E$48,0) to force both of those numbers to text. All of the other cells in that column can have =E1, =E3, =E4 etc. since those values are already text strings.
I just tried this. I added a column and entered =TEXT(E2,0) in the cell. (I tried it with dollar signs too.) Still not useful, because it the value it returns still ends up lopping off the last three significant digits, turning them into zeroes instead.
Quote from: Dirt Roads on October 31, 2022, 09:23:04 PM
Just in case you haven't solved this issue yet, a simple way to manually add a single quote character " ' " to the start of each long number that is red flagged. This will force the $number$ to be treated as a $string$.
Specifically, change the two numbers to forced strings:
'212930061077217513
'212930061077217161
Nope. Not a solution. They still flag as duplicate values.
While it won't be the actual serial number anymore, you could add a non-numeric character. Something like "A212930061077217513", "Serial 212930061077217513", etc., maybe even something outside ASCII that doesn't look like it changes the number but is enough for it to know it's not a number...
Quote from: 1 on November 01, 2022, 10:18:11 AM
While it won't be the actual serial number anymore, you could add a non-numeric character. Something like "A212930061077217513", "Serial 212930061077217513", etc., maybe even something outside ASCII that doesn't look like it changes the number but is enough for it to know it's not a number...
Then you have to remember to ALWAYS add that same thing to ALL numbers, and that is a bigger mess
Quote from: kalvado on November 01, 2022, 11:43:39 AM
Quote from: 1 on November 01, 2022, 10:18:11 AM
While it won't be the actual serial number anymore, you could add a non-numeric character. Something like "A212930061077217513", "Serial 212930061077217513", etc., maybe even something outside ASCII that doesn't look like it changes the number but is enough for it to know it's not a number...
Then you have to remember to ALWAYS add that same thing to ALL numbers, and that is a bigger mess
And remember to remove it whenever I copy-and-paste from Excel into an e-mail... which I do every time...
Quote from: kphoger on November 01, 2022, 11:50:42 AM
Quote from: kalvado on November 01, 2022, 11:43:39 AM
Quote from: 1 on November 01, 2022, 10:18:11 AM
While it won't be the actual serial number anymore, you could add a non-numeric character. Something like "A212930061077217513", "Serial 212930061077217513", etc., maybe even something outside ASCII that doesn't look like it changes the number but is enough for it to know it's not a number...
Then you have to remember to ALWAYS add that same thing to ALL numbers, and that is a bigger mess
And remember to remove it whenever I copy-and-paste from Excel into an e-mail... which I do every time...
You could use CONCAT to add a letter to the start of the string and the RIGHT function to remove it.
Quote from: Dirt Roads on October 31, 2022, 09:23:04 PM
Anywhoosit, the two cells that have long numbers are E2 and E48. In the other column you would want to have the function =TEXT($E$2,0) and =TEXT($E$48,0) to force both of those numbers to text. All of the other cells in that column can have =E1, =E3, =E4 etc. since those values are already text strings.
Quote from: kphoger on November 01, 2022, 10:10:27 AM
I just tried this. I added a column and entered =TEXT(E2,0) in the cell. (I tried it with dollar signs too.) Still not useful, because it the value it returns still ends up lopping off the last three significant digits, turning them into zeroes instead.
It turns out that the second field in the =TEXT(x,format) function is a standard format code. So the correct function is =TEXT(E2,"000000000000000000") with 18 zeroes (or more). Tried it on my version of Excel and it still didn't work, which reveals the main problem: both the E2 cell and the E8 cell have the number 212930061077217000 which reveals the loss of three significant digits.
Quote from: Dirt Roads on October 31, 2022, 09:23:04 PM
Just in case you haven't solved this issue yet, a simple way to manually add a single quote character " ' " to the start of each long number that is red flagged. This will force the $number$ to be treated as a $string$.
Specifically, change the two numbers to forced strings:
'212930061077217513
'212930061077217161
Quote from: kphoger on November 01, 2022, 10:10:27 AM
Nope. Not a solution. They still flag as duplicate values.
Indeed, if you manually add the single quote character " ' " at the beginning of each cell without retyping the entire number, Excel indeed continues to lose the last three significant digits and the cell reads as '212930061077217000. The only way I was able to force Excel to accept the data was to manually enter '212930061077217513 into a cell and '212930061077217161 into another cell. No loss of data and Excel correctly compares the two strings as non-identical. Sorry for the runaround.
Excel > Outlook > Visio > PowerPoint > Word >>>>>>>>>>>> everything else
Who needs to waste time writing a program when you can use a spreadsheet to write test steps - unless of course there's repetition when one can then turn to a node.js Yeoman generator with ejs templates.
The problem I have with using complex spreadsheet formulas is much the same as I have with Parserfunctions in MediaWiki–the syntax is so dense it makes my eyes glaze over. Debugging a chain of nested IF()s in that tiny little box is a special kind of hell. And as a result I never feel confident that what I wrote is actually right.
I can usually write a short Perl script in the same time it would take me to do anything other than simple calculations in a spreadsheet program, and be entirely confident that it's right because I can actually keep track of which test goes with what code.
Counting parentheses is a special kind of irritation.
Also... when you're in the middle of writing an Excel formula... and need to check something on another spreadsheet... when you click on that other sheet, the cell reference pops into your formula.
Quote from: Scott5114 on November 07, 2022, 10:23:05 PMDebugging a chain of nested IF()s in that tiny little box is a special kind of hell.
You can always copy and paste the formula into Notepad. Adding a hard return between each IF statement helps too. But there's no getting around the fact that, sooner or later, you're gonna be banging your head against the desk, trying to figure out why the function won't work, when it won't work because you forgot a comma.
Quote from: abefroman329 on November 08, 2022, 10:25:49 AM
Quote from: Scott5114 on November 07, 2022, 10:23:05 PMDebugging a chain of nested IF()s in that tiny little box is a special kind of hell.
You can always copy and paste the formula into Notepad. Adding a hard return between each IF statement helps too. But there's no getting around the fact that, sooner or later, you're gonna be banging your head against the desk, trying to figure out why the function won't work, when it won't work because you forgot a comma.
As if something like that never happened in (insert your favorite language)
Quote from: kalvado on November 08, 2022, 10:43:15 AM
Quote from: abefroman329 on November 08, 2022, 10:25:49 AM
Quote from: Scott5114 on November 07, 2022, 10:23:05 PMDebugging a chain of nested IF()s in that tiny little box is a special kind of hell.
You can always copy and paste the formula into Notepad. Adding a hard return between each IF statement helps too. But there's no getting around the fact that, sooner or later, you're gonna be banging your head against the desk, trying to figure out why the function won't work, when it won't work because you forgot a comma.
As if something like that never happened in (insert your favorite language)
Python doesn't use nested parentheses for if statements; it uses whitespace and linebreaks. And if a comma is missed (which typically occurs for data that spans multiple lines, not for code), it will immediately tell you as a syntax error when trying to run it.
Tradeoffs that the programming languages research community has argued about for decades...
Bear in mind that I've never used any of those other whatchamagoozits y'all are talking about, but...
There's something I like about how Excel does it. There's one particular report I run every pay-period, and it includes conditional formulae and conditional formatting.
For example, =IF(OR(Y1221="YES",AND(K1221=K1220,X1221=X1220)),"REMOVE","PAY")
I add to this spreadsheet every day until payroll comes around. While I'm adding data, I leave that column blank. Then, when it's time to run payroll, I sort my data and then fill in that column. After that, it's pivot table and VLOOKUP time.
Anyway, I start a new spreadsheet every pay-period. It's very convenient to simply copy from the formula bar on Cell Z3 in the old sheet, then paste into the formula bar on Cell Z3 in the new sheet. No need to figure out the syntax every time.
Quote from: kphoger on November 08, 2022, 01:32:16 PM
=IF(OR(Y1221="YES",AND(K1221=K1220,X1221=X1220)),"REMOVE","PAY")
The equivalent Perl would be:
if ( ($k1221 eq $k1220 and $x1221 eq $x1220) or ($y1221 eq "YES") )
{
print "REMOVE";
}
else
{
print "PAY";
}
There's still a little bit of parenthesis nastiness, but I think that's going to happen no matter what just because of the nature of the comparison you're making.
"eq" does a string comparison (i.e. it compares non-numerical data). If you were comparing numbers, you'd use "==" instead. (Most newer languages just have "==" handle both types of comparison.)
I find it a lot easier to understand what Perl is doing there. But of course the tricky part isn't getting Perl to do the comparison, it's getting the data into Perl to get to where you can do the comparison.
I feel like the best of both worlds would be a spreadsheet program that let you run code in a small programming language in each cell, rather than using Excel-style formulas. But then again most programmers would rather just write a script than faff about with a spreadsheet at all, so that's a cool idea that will probably never happen.
According to Excel, the time between 12:42:02pm and 12:42:06pm yesterday is equal to 12:00:04 AM on January 0th, 1900.
Took like 30 minutes of texting people and hunting around the various options to figure how to resolve that one. Apparently you have to change the format to "General" (not an easy setting to find) and then multiply by 86400 because it gives you a number in hours as default.
There's been a lot of discussion about issues resolving parentheses in MS Excel, but one of the features of the =IF function is that each parenthesis gets color-coded every time you type one. I have to watch carefully, because I tend to "type" in the "function bar" located at the top of the spreadsheet. The real function is being entered in the cell itself. So if you get confused with how many open and closed parentheses are inside of your formula, simply type a closed parenthesis and it will come up bold and in the same color as the open one. When you finish correctly, the first open and the last closed parentheses will come up in bold black (and then you know that you are done).
=IF(A3>B3,IF(A3>C3,IF(A3>D3,IF(A3>E3,"Giant"),"Huge"),"Big"),"Small")
Don't get confused with the colors, because the cell tags "A3", "B3", "C3", "D3", "E3" are also color-coded with the same colors, and any correlation with the parenthesis color is a coincidence.
By the way, I tend to fill text cells with multiple lines of text where each one is separated by an old-fashioned carriage return. Depending on which spreadsheet that I'm working with, I've got my function bar expanded to be able to view two or three lines of text at a time. It makes editing large amounts of data and text much more like working in a miniature text editor, but it distracts me away from some of the nifty stuff that goes on in the cell itself when you are entering a function.
Quote from: Dirt Roads on November 11, 2022, 09:42:27 AM
There's been a lot of discussion about issues resolving parentheses in MS Excel, but one of the features of the =IF function is that each parenthesis gets color-coded every time you type one. I have to watch carefully, because I tend to "type" in the "function bar" located at the top of the spreadsheet. The real function is being entered in the cell itself. So if you get confused with how many open and closed parentheses are inside of your formula, simply type a closed parenthesis and it will come up bold and in the same color as the open one. When you finish correctly, the first open and the last closed parentheses will come up in bold black (and then you know that you are done).
=IF(A3>B3,IF(A3>C3,IF(A3>D3,IF(A3>E3,"Giant"),"Huge"),"Big"),"Small")
Don't get confused with the colors, because the cell tags "A3", "B3", "C3", "D3", "E3" are also color-coded with the same colors, and any correlation with the parenthesis color is a coincidence.
By the way, I tend to fill text cells with multiple lines of text where each one is separated by an old-fashioned carriage return. Depending on which spreadsheet that I'm working with, I've got my function bar expanded to be able to view two or three lines of text at a time. It makes editing large amounts of data and text much more like working in a miniature text editor, but it distracts me away from some of the nifty stuff that goes on in the cell itself when you are entering a function.
Yes, you can make things more readable. At some point, though, it is easy to get lost in the code.
That is true for excel, that is true for any other language I dealt with. There are ways to avoid that. There are users and programmers who neglect those approaches. There are companies which neglect that (I am thinking a major car maker as a great example of such mess).
There are many syntax highlight tools, syntax analysis tools. There are still people who get lost.
Welcome to computer world...
Quote from: US 89 on November 11, 2022, 12:42:52 AM
According to Excel, the time between 12:42:02pm and 12:42:06pm yesterday is equal to 12:00:04 AM on January 0th, 1900.
Took like 30 minutes of texting people and hunting around the various options to figure how to resolve that one. Apparently you have to change the format to "General" (not an easy setting to find) and then multiply by 86400 because it gives you a number in hours as default.
Huh. I find the difference between dates in Excel all the time at work (multiple times every day), and all I have to do is format the cells to "Number" with zero decimal places; then I know how many days off they are from each other. Every so often, I also track e-mail lag in Excel down to the minute, which means I need to multiply by 1440. I've never needed accuracy down to the second, so I never thought about what that factor would be.
Quote from: kphoger on November 11, 2022, 11:02:43 AM
Quote from: US 89 on November 11, 2022, 12:42:52 AM
According to Excel, the time between 12:42:02pm and 12:42:06pm yesterday is equal to 12:00:04 AM on January 0th, 1900.
Took like 30 minutes of texting people and hunting around the various options to figure how to resolve that one. Apparently you have to change the format to "General" (not an easy setting to find) and then multiply by 86400 because it gives you a number in hours as default.
Huh. I find the difference between dates in Excel all the time at work (multiple times every day), and all I have to do is format the cells to "Number" with zero decimal places; then I know how many days off they are from each other. Every so often, I also track e-mail lag in Excel down to the minute, which means I need to multiply by 1440. I've never needed accuracy down to the second, so I never thought about what that factor would be.
86,400. I forget why I needed to know that, but it's one of those things burned into my head. Also, that there are 63,360 inches in a mile (it's 360 backwards concatenated with 360). And of course, there are 525,600 minutes in a nonleap year.
Quote from: US 89 on November 11, 2022, 12:42:52 AM
According to Excel, the time between 12:42:02pm and 12:42:06pm yesterday is equal to 12:00:04 AM on January 0th, 1900.
Took like 30 minutes of texting people and hunting around the various options to figure how to resolve that one. Apparently you have to change the format to "General" (not an easy setting to find) and then multiply by 86400 because it gives you a number in hours as default.
I'm not sure what you're trying to say here. In Excel, times are stored internally as fractions of a day. So, 12:42:02 pm is 0.529189814814815 and 12:42:06 pm is 0.529236111111111, while 12:00:04 am is 0.000046296296296. Now, there is some error involved here, since the fractions will only rarely be exact, but it won't be off by 12 hours.
Quote from: CtrlAltDel on November 11, 2022, 11:44:11 AM
Quote from: US 89 on November 11, 2022, 12:42:52 AM
According to Excel, the time between 12:42:02pm and 12:42:06pm yesterday is equal to 12:00:04 AM on January 0th, 1900.
Took like 30 minutes of texting people and hunting around the various options to figure how to resolve that one. Apparently you have to change the format to "General" (not an easy setting to find) and then multiply by 86400 because it gives you a number in hours as default.
I'm not sure what you're trying to say here. In Excel, times are stored internally as fractions of a day. So, 12:42:02 pm is 0.529189814814815 and 12:42:06 pm is 0.529236111111111, while 12:00:04 am is 0.000046296296296. Now, there is some error involved here, since the fractions will only rarely be exact, but it won't be off by 12 hours.
The "time between 12:42:02pm and 12:42:06pm" is 4 seconds, which corresponds to 4 seconds after midnight as if it can't tell a time from a duration.
Quote from: 1 on November 11, 2022, 11:47:32 AM
Quote from: CtrlAltDel on November 11, 2022, 11:44:11 AM
Quote from: US 89 on November 11, 2022, 12:42:52 AM
According to Excel, the time between 12:42:02pm and 12:42:06pm yesterday is equal to 12:00:04 AM on January 0th, 1900.
Took like 30 minutes of texting people and hunting around the various options to figure how to resolve that one. Apparently you have to change the format to "General" (not an easy setting to find) and then multiply by 86400 because it gives you a number in hours as default.
I'm not sure what you're trying to say here. In Excel, times are stored internally as fractions of a day. So, 12:42:02 pm is 0.529189814814815 and 12:42:06 pm is 0.529236111111111, while 12:00:04 am is 0.000046296296296. Now, there is some error involved here, since the fractions will only rarely be exact, but it won't be off by 12 hours.
The "time between 12:42:02pm and 12:42:06pm" is 4 seconds, which corresponds to 4 seconds after midnight as if it can't tell a time from a duration.
Oh, that makes sense. Yeah, that's just a display issue. If you set the number format to [hh]:mm:ss or [mm]:ss, it'll show up right. Or multiply it by 86,400 to get the seconds as an integer, as mentioned above.
Quote from: CtrlAltDel on November 11, 2022, 11:37:10 AM
And of course, there are 525,600 minutes in a nonleap year.
How can you measure the life of a woman or a man?
Quote from: kphoger on November 11, 2022, 12:00:52 PM
Quote from: CtrlAltDel on November 11, 2022, 11:37:10 AM
And of course, there are 525,600 minutes in a nonleap year.
How can you measure the life of a woman or a man?
I love
Rent! A high school in my city put it on about six years ago. I don't know how a high school got permission to put on a show with such adult themes, but they did a great job. Enthusiasm more than made up for lack of experience.
Can't stand that song. Overplayed and had to hear it sung by far too many school choirs.
Quote from: Scott5114 on November 08, 2022, 02:20:29 PM
Quote from: kphoger on November 08, 2022, 01:32:16 PM
=IF(OR(Y1221="YES",AND(K1221=K1220,X1221=X1220)),"REMOVE","PAY")
The equivalent Perl would be:
if ( ($k1221 eq $k1220 and $x1221 eq $x1220) or ($y1221 eq "YES") )
{
print "REMOVE";
}
else
{
print "PAY";
}
There's still a little bit of parenthesis nastiness, but I think that's going to happen no matter what just because of the nature of the comparison you're making.
"eq" does a string comparison (i.e. it compares non-numerical data). If you were comparing numbers, you'd use "==" instead. (Most newer languages just have "==" handle both types of comparison.)
I find it a lot easier to understand what Perl is doing there. But of course the tricky part isn't getting Perl to do the comparison, it's getting the data into Perl to get to where you can do the comparison.
I feel like the best of both worlds would be a spreadsheet program that let you run code in a small programming language in each cell, rather than using Excel-style formulas. But then again most programmers would rather just write a script than faff about with a spreadsheet at all, so that's a cool idea that will probably never happen.
I finally ran into a scenario on a test case I'm writing where I'd like to have more customization and ended up switching to Typescript (JavaScript with a type checking layer)
JavaScript? [shudder]
I have to do JavaScript for work every now and again and it never fails to parse something in a completely stupid way that keeps me from doing what I want. Of course, this is browser-based JS, so maybe the version you're using is less insane.
Quote from: Scott5114 on November 13, 2022, 04:20:08 PM
JavaScript? [shudder]
I have to do JavaScript for work every now and again and it never fails to parse something in a completely stupid way that keeps me from doing what I want. Of course, this is browser-based JS, so maybe the version you're using is less insane.
Node.js
Quote from: SSOWorld on November 13, 2022, 08:48:09 PM
Quote from: Scott5114 on November 13, 2022, 04:20:08 PM
JavaScript? [shudder]
I have to do JavaScript for work every now and again and it never fails to parse something in a completely stupid way that keeps me from doing what I want. Of course, this is browser-based JS, so maybe the version you're using is less insane.
Node.js
Interesting. For someone with only passing familiarity with JavaScript, does it provide much benefit over other standalone interpreted languages like Python, Perl, Lua, etc.?
Quote from: Scott5114 on November 13, 2022, 09:15:58 PM
Quote from: SSOWorld on November 13, 2022, 08:48:09 PM
Quote from: Scott5114 on November 13, 2022, 04:20:08 PM
JavaScript? [shudder]
I have to do JavaScript for work every now and again and it never fails to parse something in a completely stupid way that keeps me from doing what I want. Of course, this is browser-based JS, so maybe the version you're using is less insane.
Node.js
Interesting. For someone with only passing familiarity with JavaScript, does it provide much benefit over other standalone interpreted languages like Python, Perl, Lua, etc.?
Not completely. Python and perl provide easier interfaces IMO, but Perl is a legacy language and Python, well, it's python.
Quote from: SSOWorld on November 13, 2022, 09:27:48 PM
Quote from: Scott5114 on November 13, 2022, 09:15:58 PM
Quote from: SSOWorld on November 13, 2022, 08:48:09 PM
Quote from: Scott5114 on November 13, 2022, 04:20:08 PM
JavaScript? [shudder]
I have to do JavaScript for work every now and again and it never fails to parse something in a completely stupid way that keeps me from doing what I want. Of course, this is browser-based JS, so maybe the version you're using is less insane.
Node.js
Interesting. For someone with only passing familiarity with JavaScript, does it provide much benefit over other standalone interpreted languages like Python, Perl, Lua, etc.?
Not completely. Python and perl provide easier interfaces IMO, but Perl is a legacy language and Python, well, it's python.
Looks like there are few packages, with different pricing models, which should allow python and excel integration. I may play with that a bit later
Quote from: SSOWorld on November 13, 2022, 09:27:48 PM
Quote from: Scott5114 on November 13, 2022, 09:15:58 PM
Quote from: SSOWorld on November 13, 2022, 08:48:09 PM
Quote from: Scott5114 on November 13, 2022, 04:20:08 PM
JavaScript? [shudder]
I have to do JavaScript for work every now and again and it never fails to parse something in a completely stupid way that keeps me from doing what I want. Of course, this is browser-based JS, so maybe the version you're using is less insane.
Node.js
Interesting. For someone with only passing familiarity with JavaScript, does it provide much benefit over other standalone interpreted languages like Python, Perl, Lua, etc.?
Not completely. Python and perl provide easier interfaces IMO, but Perl is a legacy language and Python, well, it's python.
Perl is only a legacy language on Windows. On Linux, it's still very much alive (although C is still the lingua franca, naturally). There's really not anything you can do with Python that you can't do with Perl, unless you're relying on a library that some jerk insists on only releasing for Python or whatever. (GUI programming is a bit sketchier in Perl than it is Python, but Python is still sketchy compared to C++, so if that's what you're doing you probably should just bite the bullet and do C++.)
Also, my boss knows Perl and not Python, so if I get stuck I can ask him for help :P
Quote from: Scott5114 on November 13, 2022, 11:59:30 PM
Also, my boss knows Perl and not Python, so if I get stuck I can ask him for help :P
Stack Overflow can help too ;)
Quote from: Scott5114 on November 13, 2022, 11:59:30 PM
Perl is only a legacy language on Windows. On Linux, it's still very much alive (although C is still the lingua franca, naturally). There's really not anything you can do with Python that you can't do with Perl, unless you're relying on a library that some jerk insists on only releasing for Python or whatever.
I was originally a Perl guy, and still prefer the way it handles regexes, but: there are a lot of "jerks" in machine learning (Pandas, Numpy, PyTorch, sklearn, pyspark, ...)
Hey, what do you guys think of Aptos, Microsoft's new default font?
Quote from: kphoger on January 10, 2024, 11:26:40 AM
Hey, what do you guys think of Aptos, Microsoft's new default font?
It's no Comic Sans.
Quote from: JayhawkCO on January 10, 2024, 11:31:41 AM
Quote from: kphoger on January 10, 2024, 11:26:40 AM
Hey, what do you guys think of Aptos, Microsoft's new default font?
It's no Comic Sans.
If you heat up a frozen burrito in the microwave, bring it back to your desk, cut into the middle with a knife, and the filling squirts all over your monitor—then that bean splatter will certainly end up looking better than Comic Sans.
So, other than that . . . is Aptos a good or bad font? I'm still deciding.
Quote from: kphoger on January 10, 2024, 11:33:55 AM
Quote from: JayhawkCO on January 10, 2024, 11:31:41 AM
Quote from: kphoger on January 10, 2024, 11:26:40 AM
Hey, what do you guys think of Aptos, Microsoft's new default font?
It's no Comic Sans.
If you heat up a frozen burrito in the microwave, bring it back to your desk, cut into the middle with a knife, and the filling squirts all over your monitor—then that bean splatter will certainly end up looking better than Comic Sans.
So, other than that . . . is Aptos a good or bad font? I'm still deciding.
I'm not picky when it comes to straight forward fonts.
Quote from: kphoger on January 10, 2024, 11:26:40 AMHey, what do you guys think of Aptos, Microsoft's new default font?
I had to look it up in Wikipedia, since the latest versions of Office I use still have Times New Roman as the default, at least for Word. I'm not impressed, but then I was not thrilled with Calibri, either. I greatly prefer serif type for sustained screen reading, and my tastes within that category are fairly conservative--for example, I have Baskerville rather than Bookerly set as the default on the Kindle app for my tablet, which is my ebook workhorse.
Quote from: kphoger on January 10, 2024, 11:26:40 AM
Hey, what do you guys think of Aptos, Microsoft's new default font?
They should have used ClearView!
But a change for the sake of change is certainly something MS would happily go for.
Quote from: kphoger on January 10, 2024, 11:26:40 AM
Hey, what do you guys think of Aptos, Microsoft's new default font?
All five of the new fonts they've recently introduced (https://presentationpoint.com/blog/bierstadt-grandview-seaford-skeena-tenorite-fonts/) (note that Bierstadt was renamed Aptos) are pretty bland in my view, although that's probably what they were going for. Of them, I'd say that Seaford is the best.
They're all better than Calibri, though. I never liked the roundedness to the ends of the various strokes, preferring something more rectangular in my sans serifs.
Quote from: kphoger on January 10, 2024, 11:26:40 AM
Hey, what do you guys think of Aptos, Microsoft's new default font?
It's okay as a font but I'm not a huge fan of how it looks; I'll be sticking to the classic Calibri.
What annoys me more is that Outlook on browser defaults to Aptos, but Outlook on desktop defaults to Calibri. I feel inclined to keep fonts consistent regardless of what client I'm using to send email, and defaulting to Aptos makes it more difficult for me to achieve. Either roll it out everywhere, or nowhere; don't slowly phase it in.
I really don't think there was anything wrong with having Times New Roman as the default font anyways, especially in Microsoft Word. Everyone asks for Times New Roman 12 font.
Aw, hell, they messed with colors too. Look what happens when I copy from last week's file (left) and paste into a new file (right):
(https://i.imgur.com/Zk9GL18.png)
Not a happy camper about this. :angry:
ETA — If anyone else is frustrated by the color change and wants to avoid it when copying-and-pasting, I figured out how to avoid it:
Right-click
Paste Special...
All using Source theme
Aptos looks like someone couldn't decide whether they wanted to rip off Roboto or Univers, so they did both. It's better than Calibri, I guess.
I sort of wish that these kinds of programs didn't have a default font at all, and required users to actively choose a font before they could do anything. (Or at least prompt them to choose their own default the first time they use the program.) As it is now, Calibri (and Times New Roman before that) are a marker that someone doesn't know or care how to change the font.
Quote from: Scott5114 on January 10, 2024, 07:34:39 PM
Aptos looks like someone couldn't decide whether they wanted to rip off Roboto or Univers, so they did both. It's better than Calibri, I guess.
I sort of wish that these kinds of programs didn't have a default font at all, and required users to actively choose a font before they could do anything. (Or at least prompt them to choose their own default the first time they use the program.) As it is now, Calibri (and Times New Roman before that) are a marker that someone doesn't know or care how to change the font.
honestly speaking, in many cases any font (other than comic sans) would work just fine. But changing things randomly on manufacturer (or system admin) side isn't smart - meaning some document features may suddenly change since something now fits where it didn't fit or the other way around.
I understand that MS is out of ideas and is entered a destructive phase, though... just another confirmation.
It's a sign that the marketing department is running the show—marketing has a pathological need to change a company's graphical style periodically to keep it "fresh". They don't seem to realize that applying this to things like fonts can negatively impact software usability, though.
I didn't really care for Calibri; its rounded corners made it a bit too informal for my liking, so I don't know that I ever actually used it on a finished document in the rare event I composed a document in that era's Microsoft Office.
I spend my working life in Microsoft Excel, and actually Calibri treats me well. I prefer it to Arial, which is how certain things come over to me. Honestly, the only time I change the font from Calibri to something else within Excel is when I need everything to be monospaced, in which case I use Consolas or everyone's old friend Courier New (but preferring Consolas).
However, in Word, I almost always switch to a more classy-looking serif font. I've become fond of Cambria recently as a decent "normal" reading font.
But something about Aptos just looks.... I don't know.... weird. I can quite put my finger on it.
Quote from: kalvado on January 10, 2024, 07:49:10 PM
honestly speaking, in many cases any font (other than comic sans) would work just fine.
There's also Papyrus (https://www.youtube.com/watch?v=jVhlJNJopOQ). :-D
Quote from: kphoger on January 11, 2024, 10:08:41 AM
But something about Aptos just looks.... I don't know.... weird. I can quite put my finger on it.
Well, let's take a gander:
(https://presentationpoint.com/wp-content/uploads/2021/12/Microsoft-new-Bierstadt-font.jpg)
For me, what sticks out is the spacing between the characters as well as their width.
Quote from: CtrlAltDel on January 11, 2024, 12:42:40 PM
There's also Papyrus
Papyrus is a font that I really
wish could work well, but it just doesn't. Whenever I use it, I have to take screenshots and adjust stuff in Paint.
I miss my days of using Courier New in high school when I needed to write an x page paper.
Just poked around in Office, checking out the Aptos fonts.
I like that it's a well-rounded family, including "display", "monospace", and "serif" variants.
It's OK, I guess. I don't hate it, but I also don't feel a burning need to change. If there were some reason it were superior for some reason....
Any guesses as to how long it is until we see a contractor erect a highway sign using Aptos?
I liked Calibri when it was introduced, largely because I didn't hate it, it was different from Arial (which everyone was using at the time), and it was becoming "standard" enough that I didn't have to worry much about whether other people had the font installed on their machine.
A few years ago, I changed to Roboto as my sans-serif standard, because it seemed most readable to me, is either open source or at least has few copyright/trademark concerns, it has plenty of the glyphs I use, and it renders well on my printer (as opposed to my flirtation with Inter, which I really liked, but wouldn't print bold-faced)...and because it's not over-used on Windows machines. (Android, is obviously another matter.)
(And, FWIW, when in "personal branding" mode, I'll use Stix Two when I need something with serifs, or Cormorant when I'm being fancy.)
Annoyingly, at work, corporate branding standards say that I have to use Arial on everything....but in spreadsheets, I see people whatever the Microsoft default was when the spreadsheet was first created. That actually helps provide some clues as to how old some files are.
I personally have become exhausted by Calibri, yet still appreciate Times New Roman.
Quote from: epzik8 on January 11, 2024, 06:23:50 PM
I personally have become exhausted by Calibri, yet still appreciate Times New Roman.
I can no longer stand the look of Times New Roman.
I don't mind Calibri.
Quote from: Rothman on January 11, 2024, 06:42:39 PM
I can no longer stand the look of Times New Roman.
I don't mind Calibri.
Wait a minute! Everybody, stop what you're doing! Rothman and I agree about something!
Quote from: kphoger on January 11, 2024, 07:07:24 PM
Quote from: Rothman on January 11, 2024, 06:42:39 PM
I can no longer stand the look of Times New Roman.
I don't mind Calibri.
Wait a minute! Everybody, stop what you're doing! Rothman and I agree about something!
Did you say something about times new roman, though?
I am not sure it counts without that part.
Great, even more fonts to despise that that style of lower-case "l" (Trebuchet, Clearview, and now Aptos...probably others.)
They do it to make it easier to distinguish between I and l... I would prefer that it be done with a crossbar on the capital I, though, and leaving the l as a simple bar. (I have a modified version of Overpass running on one of my web apps that has crossbars on the I for this reason.)
I prefer the curvy lowercase letter l. Whenever I'm handwriting and want to make it especially clear that that's what it is, I do the same thing. Serifs on the I and the 1, curvy bit on the l. That's what I prefer.
Quote from: formulanone on January 11, 2024, 09:40:13 PM
Great, even more fonts to despise that that style of lower-case "l" (Trebuchet, Clearview, and now Aptos...probably others.)
Yeah, blech.
An optimist will tell you that the glass is ½ full.
A pessimist will tell you that the glass is ½ empty.
Microsoft Excel will tell you that the glass is January 2nd.
Quote from: kphoger on January 12, 2024, 01:18:08 PM
I prefer the curvy lowercase letter l. Whenever I'm handwriting and want to make it especially clear that that's what it is, I do the same thing. Serifs on the I and the 1, curvy bit on the l. That's what I prefer.
It wouldn't bug me so much except for it being the only abnormally curvy letter. Even the descender on "q" is oddly missing. The counters in the 6 and 9 are odd. Maybe it's juxtaposition of lower case and capitals, but the latter just seem overly wide.
I'll get used to it; I'm sure it looks fine on a screen where it will usually be viewed at 8-14 points and much of my little nitpicks disappear.
Quote from: NWI_Irish96 on January 12, 2024, 01:27:36 PM
An optimist will tell you that the glass is ½ full.
A pessimist will tell you that the glass is ½ empty.
Microsoft Excel will tell you that the glass is January 2nd.
:-D I wind up using the apostrophe before numbers very frequently.
Quote from: NWI_Irish96 on January 12, 2024, 01:27:36 PM
An optimist will tell you that the glass is ½ full.
A pessimist will tell you that the glass is ½ empty.
Microsoft Excel will tell you that the glass is January 2nd.
Actually, Microsoft corrects the stuck bits and tries to tell you that the glass is January 2nd. But every once in a while, it says the glass is September 10th. If in doubt, reboot and you might get the correct answer (or January 2nd, whichever comes first).
Quote from: formulanone on January 12, 2024, 02:13:07 PM
It wouldn't bug me so much except for it being the only abnormally curvy letter. Even the descender on "q" is oddly missing.
Speaking of q, isn't it weird that all four of these are letters?:
(https://i.imgur.com/5LToAyZ.png)
It's very convenient when designing a font, since I can normally just draw one of the four and mirror it three ways.
Today, I finished up a project in Excel that had a LOT of data. Like, the main worksheet had nearly 109,000 rows of data. I wanted it to display everything in a monospaced font, so I considered changing it to Aptos Mono. However, because the Department of Labor will be combing through the spreadsheet, I didn't want to take the chance that her computer isn't yet compatible with the new font family. So I just used Consolas instead.
I use Excel nearly every day. It's extremely useful for ad-hoc data manipulation that a database never had code written to do. In a corporate environment, access to writing SQL scripts--even just queries--is often prohibited to users. I was the assigned IT person for an entire division and I wasn't allowed to write custom queries. So, most of my data manipulation was done in Excel because there was no option for me to do it otherwise.
I have experience using and even designing Access databases. The main problems I have with Access are 1) initial setup that often involves writing extensive SQL and even VBA scripts plus designing a user interface for people to follow, and 2) the inherent slowness of the software when not paired with a SQL server. I developed a database to help the sales team quote jobs (something our ERP should have been designed to do, but wasn't). It worked just fine, but even simple queries with a very limited record set took a long time to run and display. My sales team just stopped using it. I asked to have it uploaded to the SQL server, and our MIS department rejected it without any consideration. "Too likely to introduce instability."
In my present job, we have an ancient ERP system. I spend a lot of time converting text files to excel so that I can run them through macros (at least that can be done with a script and some software like Monarch). I paste the results into emails.
As for the font question, serif fonts are always better, and Georgia is the correct answer.
Quote from: fhmiii on January 16, 2024, 06:29:47 PMAs for the font question, serif fonts are always better, and Georgia is the correct answer.
I reach for that serif font the most, it's quite underrated (that is, I don't think I've ever heard any generalized hate for it).
Quote from: formulanone on January 19, 2024, 09:59:18 AM
Quote from: fhmiii on January 16, 2024, 06:29:47 PMAs for the font question, serif fonts are always better, and Georgia is the correct answer.
I reach for that serif font the most, it's quite underrated (that is, I don't think I've ever heard any generalized hate for it).
No generalized hate, no, although I don't especially like it. Except for the numbers, with their ascenders and descenders.
(https://i.imgur.com/f1wV6FG.png)
Using lining numerals would definitely give a different vibe to route shields:
(https://i.imgur.com/XnhyFQV.png)
I've started using Aptos Mono for things in Excel. Even 10 pt seems a bit large, but I'm going with it.
Has anyone else's Microsoft programs switched back again from Aptos to Calibri?
Quote from: kphoger on February 15, 2024, 10:59:27 AM
Has anyone else's Microsoft programs switched back again from Aptos to Calibri?
Mine hasn't changed in the first place.
Quote from: kphoger on February 15, 2024, 10:59:27 AM
Has anyone else's Microsoft programs switched back again from Aptos to Calibri?
Huh. Looks like mine did this.
Quote from: kphoger on February 15, 2024, 10:59:27 AM
Has anyone else's Microsoft programs switched back again from Aptos to Calibri?
I use customized default templates....so no clue.
My latest issue with Excel:
Even though it's all still on MS Office 365, some of the recent updates have broken--or at least, caused instability in--the VBA scripts written in my some of my more complex documents. Workbooks where buttons tied to macros have automated the process of doing long, tedious data manipulation, adjusting lookups, copy/paste, etc., that would otherwise take hours but the macro takes maybe 20 seconds.
I'm on my third iteration of trying to eliminate the instability but it appears that it may be a flaw with how VBA is now handled by the software.