Excel reall doesn't excell...
Try dealing with serial number 000085545 for one part, and serial number 85545 for a different part....
Microsoft's Surface tablets have again embarrassed the company's bottom line, this time after failing to calculate a simple one accurately. The problem was spotted by folks at Apple Insider who noticed a San Francisco billboard advertising Redmond's latest tablet. When they peered at it closely in the image below, they noticed …
Where the person opening a delimited file, or importing it into Excel:
1) Doesn't know enough to customize the import - taking the extra step to define certain columns as "Text" or something other than rather than the default "General" (there can be numerous reasons they are ignorant as to the need for this);
2) Doesn't know the data well enough to realize there are leading zeroes in it.
Re: 2) zip codes and postal codes regularly have leading zeroes, and anyone who learns Excel or who claims to be an "expert" user should know the General format will truncate these during import.
A good deal of the problem derives from failure to grasp the difference between a database and a spreadsheet, meaning Excel is too often employed in aid of tasks for which it it was not originally designed, and for which it is not the best choice, e.g., for mailing and contact lists.
However, it's nonetheless ridiculous Excel's import routine doesn't deal with leading zeroes in some intelligent way, such as ASKING the user about them during import, or giving a warning when the user clicks "Finish". Maybe a quick pop-up noting leading zeroes exist, and will be removed unless the column format is changed to Text? Giving users a "Go back" button, allowing them to fix the problem before finalizing the import? How hard would that be, really?
On a larger level, this is only a small example of how "User friendly" simply isn't. Microsoft certainly doesn't care to change that, and neither does the IT industry as a whole, given that truly user-friendly hardware and software tends to reduce profits.
Yes I'm cynical, but only because it's so often true.
"Hmmm and now try get a column containing 000846383 to load as text from a CSV."
It will in LibreOffice as long as ALL that column is to be text. You need to select the column in the import preview and change it's type from the pull-down menu to 'text'
With Excel I can't remember although I used to import VERY large CSVs once upon a time.
If you actually know how to do an import, and perform all the steps, including the one where you view the columns, and can change the format of any or all BEFORE you click Finish. Try clicking "Next" sometime, until you run out of "Next" buttons, and read what's on the screen.
That said, however: per my longer comment, it is pretty ridiculous that Excel doesn't pop up a leading-zeroes or long-number warning when one clicks "Finish."
Not everyone is going to be intimately familiar with the data being imported. More to the point, data is not always completely clean.
There's nothing stupider than software that after this many years still fails to tag common, repeated, well-known import errors.
Try a really long serial number or something like an SIM numbers with about 20 digits then unless you prefix it with an apostrophe or something to make sure it treated as text then Excel will be helpful and throw away the last few digits and display it in scientific notation, really helpful that. No too much of an issue if entering by hand but when you are getting lot's of data in CSV files it can be a bit of a problem - I've had to write pre-processors to modify CSV files before loading them into Excel to ensure Excel doesn't trash the content.
It doesn't guess, it can only do what it's told and text is the only way to make it represent the zeros and not truncate the number because it wasn't designed to represent numbers that way but the fact remains why the fuck in gods green earth would anyone have ever devised an identification system that uses zeros as placeholders?
Excel truncates these "numbers" because zeros have no value in the front of a numeric string, only after a number!
I don't care what the reasoning is, the concept of using them as placeholders is daft. If you keep letting idiots make stupid decisions then you get what you deserve. If a character series must have placeholders then make it alpha numeric to begin with.
In most cases if you are the one creating the system, a little planning and forethought can avoid things like this. Doing the same thing repeatedly and expecting better results is the very definition of insanity. Doing things "the way it's always been done" often brings results you won't like.
AGAIN Your problem is the system you designed or inherited NOT EXCEL. How about some more downvotes for speaking the truth.
I agree with you on designing systems with zeros as placeholders. It is nuts. However, when one is forced upon you, there is little choice. But again, this does not excuse Excel guessing that my "00032653453" is a number.
Any programmer worthy of their title should know that it's a string. It seems to me that Excel is looking at the string of characters to import, finds that there's no letter in it, but only digits, and says "it's a number". Wrong.
If they had to make it so that it parses the string at import time, they should have left all fields as text and let the user decide either in the wizard or after import what the data type is.
Same for long integers being shown in exponential format. Very annoying. Excel should either import it as a string, or leave it as a number (if it fits in the 4 bytes data type or whatever Excel is using internally), but show it to me how it is in the original file, do not assume it's an exponential number.
"Such folk might also wonder why anyone would spend $9500 on a trip to Hawaii when the diving's pretty fine around the Caribbean, the surf and diving can be good in Mexico and hotels likely far cheaper in both destinations."
Because you are not a travel agent nor, apparently, have you sought the opinion of one.
Mexico, outside the tourist belt of Cancun, has been suffering from drug gang-related violence and has travel warnings issued from almost all major countries. Even the tourist area of Acapulco is no longer completely safe and Puerto Vallarta had safety concerns in 2011 caused Princess Cruises to cancel trips. Touring around the interior of Mexico has not been recommended by several major foreign governments in comparison to Hawaii, which has some of the most beautiful scenery on planet Earth.
Caribbean has some fantastic diving. But if you live an 'alternate lifestyle", or have empathy for the conditions of those who do, many places in the Caribbean are highly homophobic to the point of legislation. For diving, both the Bahamas and Turks & Caicos are friendly and have beautiful snorkeling and scuba with good flights from almost all major international cities.
"rows to the value of $500 were inserted into the spreadsheet but somebody forgot to press F9 in order to recalculate the spreadsheet formulae."
Why would you have turned off auto mode ? I rarely did even when I had huge sheets. Recalculate was pretty quick.
Even on this old 1.6GHz Celeron laptop using LibreOffice I've just altered the first value in a set of 400000 sine calculations each dependent on the another and it took < 1sec
Within the last couple of years I've had test case spreadsheets (yes I know, but when the budget doesn't extend to test case repositories or investigation time of open source alternatives....) whereby the totals haven't recalculated properly, especially noticable with the wiki plugin which mapped these totals onto a pie chart. The small number of failed test cases were suddenly a big red slice.
Don't worry microsoft, no matter how much you continually screw up your own products you've still got $2bn per year from extorting money from one you can never hope to come close to in a biilion years.
ExFAT is patented so as to be impossible to implement if you don't pay the tax. FAT32 is only patented if the device understands Win 95-style long file names (and some argue read OR write LFNs but not both). Symbian 3 devices display LFNs in lower case which may also be a way around it. The solution is not to implement support for Win 95-style LFNs or if you've got good lawyers limited support for them.
You see, someone has to start using a different file system on portable media. If not Google, that has their OS on millions of devices, then who? It will get traction, if not overnight. If they would have started 3 years ago, everyone would have used it by now. But if they don't start, you have to assume that they are Ok with Android hardware manufacturers paying the Microsoft toll.
What's the big deal anyway in pushing another file system on a MicroSD card? You use it natively on Android, and for reading/writing it on Windows and Mac, you would install a small application.
> for reading/writing it on Windows and Mac, you would install a small application.
That is one of the reasons that Microsoft is moving to the walled garden model. On RT and WP8 - NO, you will NOT install a small app to do that. On Windows 9 it may be that NO, you will NOT install a small application either, we shall see.
UDF is a filesystem which should work on every OS released from XP onwards (OS X and Linux included), possibly even RT too. It's just a case of a big-enough company getting behind it. I think Google could push it if they wanted to, they made people download Android File Transfer for OS X for MTP.
IIRC, the real problem is that FAT/FAT32 weren't patented when Linux (and other unixes) added support for the filesystems. It was only later, after it became the de facto standard for USB drives, that MS decided to patent it. And for some bizarre reason the patent appears to have survived several challenges.
>> If Android uses non-FRAND MS' technologies, the companies producing it have to pay.
If by M$' technologies, you mean broad, generalised, vague, and pre-mobile patents that should have been invalidated years ago, then yes.
>> Google could produce an alternative to FAT32/ExFAT (which seems the main issue), but they don't seem to be bothered.
They don't need to. EXT-4 is already far superior to FAT. The only reason it's included in some android OEM builds is for Windows platform (PC) support. It's unlikely Micro$haft will support any other file system except their own patent encumbered ones, ensuring a stream of revenue from pay up or else patent extortion schemes.
The good news is that the Nexus line of devices eschews sd ports, so that pretty much eliminates the need for FAT support. Of course that still doesn't stop M$ from abusing other broad and dubious patents to extort Android.
The FAT patent in question revolves around the storage of short/long file names, something that's both obvious and has prior art anyway. Clearly software patents in general need to be abolished globally, because it enables evil companies like Micro$haft to bully would-be competitors and forcibly hold on to its creaking desktop PC monopoly.
It won't matter in a few years anyway though, as M$ will be reduced to enterprise exclusivity, and patent trolling only. They're finished in the consumer market. And eventually they'll get kicked out of the enterprise market too.
reinventing new ways of making it impossible to implement 40 years of good software practice.
Saying simply change the column type to text is all well and good but someone has to realise there is a problem first.
Mind you I can make a good fortnights holiday in Cancun with a stop over in Hawaii for doing just that for some poor desperate sod who is under the illusion that software is easy.
This is a bit silly. Some staffer who made this advert clearly didn't think that the hordes would analyse the numbers. He was just knocking up some quick illustration of a spreadsheet, and clearly didn't include the last two rows in the sum calculation. Nothing to do with Surface/Excel; pure pedantry on us interwebbers' part. Why is it newsworthy?
It's newsworthy because at no point in this ad's journey through the agency did any of the highly paid execs (either at the agency or at Microsoft) think that because they are advertising to a numerate customer base it might be worth checking some staffer's arithmetic.
More to the point: Who needs a spreadsheet (with graphic included) to add up less than a dozen straightforward numbers?
And what kind of person would use a pie chart, and a 3D one at that, to convey any kind of useful information?
And lastly, is this ad trying to show us that MS Excel can do straightforward additions (albeit not necessarily adding up to the correct amount) and create pie charts? In 2013? Or is somebody's watch running 30 years slow?
They probably added the last two lines which add up to $500 without extending the range in the SUM rather than having a totals for a table. They were probably added to make the holiday look snazzier or the display fit better. Most of the time Excel will extend ranges properly but there's ways of doing it so it doesn't think you've extended a range. Yep it is fairly intelligent but not super intelligent and some marketing bloke can easily pervert it.
"Whip out your calculators now, dear readers, and punch in that stack of numbers"
Why? You don't need a calculator for this, all you need is to carefully look things over. There are only "clean" numbers in there so it's very easy to do this from mind.
Starting from the bottom: 300 + 200 makes 500. And added up to the 500 above it makes 1000.
The 1500 above that one makes 2500 and that can be added up to the first 2500 making 5000. Now all that's left is 4000 and 500, which should be extremely easy. 5 + 4 is 9 after all, so now we have 9000 and the remaining 500.
No need for your stinkin' calculators ;-)
"how does Excel calculate if you have put the $ at the front of each number?"
Don't remember with Excel but LibreOffice ( presumably on a UK set machine) treats a number starting with a £ symbol as just that and sums to the correct value. Dollars and other currencies need have the cells/rows/columns formatted and then the unadorned number typed. A safer option really, in any case, is to format to use the 3-letter currency code USD, CHF, GBP etc. which also can be chosen from the formatting menu
From the looks of it, this "vacation" is a last minute deal. If you want a hotel, lets say, and desire to pay $400/night, then you get 10 days. A $400/night hotel is pretty pricey, even in Hawaii. I stayed at a nice condo place for "only" $200 a night (it may have been less, I don't remember the details as my wife made the arrangements). The Airfare of $2500 (assuming round trip from Seattle, next to Microsoft) for two people comes to $1250, which is approaching first class/last minute fares. If you did your planning correctly, you could use your frequent flyer miles which would reduce that to $10 per person (lots of miles needed, but you DO have them!!). The rental car of $500 is also pretty pricey. We rented a car (with a tank of gas!) for about $230 or so.
All in all, this is one of those "Luxury" deals (lots of margin for the operator), or somebody's big dream. For that money, I could do a couple of vacations, and have money left over.
So, it is typical Microsoft, in the "let them eat cake" category!
Yes, my trip to Hawaii was back in September, so I don't think the prices have gone up THAT much!
Not only can it not add up, but by the looks of things someone saw this guy coming. A week or so in Hawaii for a family of 4 should cost about a 1/3 of that.. including air fare from the US.
I only speak up because Hawaii has always been a favourite destination of mine since I moved to the US.
Biting the hand that feeds IT © 1998–2022