British Army does not Excel at spreadsheets: Soldiers' newly announced promotions are revoked after sorting snafu

Red faces abound within the British Army after an Excel spreadsheet cock-up led to a number of soldiers being wrongly promoted. A screenshot of an internal email sent around the Royal Logistic Corps (RLC) and posted on Facebook revealed the snafu, which The Register has been told happened after someone in the RLC personnel …

[Andy Non]

Perhaps the person

who made the cock-up should be demoted to office cleaner? They might excel at that.

[WolfFan]

Re: Perhaps the person

Ahem. No. Whoever made that error should be promoted, and sent to be the British representative at the brand-new Canadian Armed Forces base on Baffin Island, a base set up just to park idiots from various armed forces of the Commonwealth and possibly allies. Britain would have one on South Georgia Island, and Australia one somewhere near New Guinea. The US would put theirs in the Mojave. Or possibly Montana. Every now and again they should exchange a few personnel.

[Nunyabiznes]

Re: Perhaps the person

No, do not send them to Montana. We have plenty already. We used to have Thule AFB in Greenland (sorry about Pres. Trump's faux pas there my Greenland comrades) that we sent the truly inept. Again, sorry Greenland.

We used to entice the exceptionally ignorant ones with the promise of a willing lass behind every tree on Thule. Apparently there is a notable lack of trees on said base.

[disgruntled yank]

Re: Perhaps the person

The FBI used to use the Butte, Montana, office as its equivalent of Siberia.

[Anonymous Coward]

Re: Perhaps the person

The U.S. already has based in the Mojave!! Edwards AFB!

[Cederic]

Re: Perhaps the person

Can I volunteer to run the one "somewhere near New Guinea"? Clear clean water, coral reefs, beautiful weather, untouched miles of sandy beaches..?

[Strahd Ivarius]

Re: Perhaps the person

The US one is located in Washington in a 5-sided building...

[Korev]

Re: Perhaps the person

>who made the cock-up should be demoted to office cleaner

Or maybe they should get corporal punishment...

[Doctor Syntax]

Re: Perhaps the person

It was a major error made using a general purpose application to marshal data.

[John Brown (no body)]

Re: Perhaps the person

Well, at least Private Data didn't get spaffed all over the place when the data bomb went off. I wonder if the offenders name is Will? Might he be fired at?

[Yet Another Anonymous coward]

Re: Perhaps the person

I think Major Error is the staff officer in charge of the process

[Anonymous Coward]

Re: Perhaps the person

> who made the cock-up should be demoted to office cleaner?

If the punishment was being made to clean the latrines then that would be a promotion to loo-tenant.

[Lee D]

Re: Perhaps the person

If you only have one person compiling, checking, and sending out things like that - that's the problem right there. Sack the manager who put that procedure in place.

Even the basics of things like payroll and admissions should have a double-check by someone else. If nothing else, you do NOT want your processes to be in the hands of a single person (who could slip their mate through, or get run over by a bus).

Whoever *checked* it should be sanctioned just as harshly. It's easy to make a mistake, but if you don't have someone checking, it's just human nature. If you do have someone checking, they didn't do their job at all (not just made an easy mistake, but literally didn't do the entire purpose of their job).

That this went from Excel out to emails without something dry-running, then manually checking, is the problem. It's a process problem, not an individual. And, generally speaking, in my experience, the individual in this case would have been crying out for someone to help for years, because they can't be expected to compile, check and distribute emails like this without being a prize target for the slightest cockup.

[Doctor Syntax]

Re: Perhaps the person

But the results must be OK because we got them from the computer.

[Elfoad Regfoad]

There's no point linking to a site that requires logging in to view

Especially one as odious as Facebook.

[Daedalus]

I sympathize

I have yet to get a spreadsheet to sort properly. There's usually an objection to the number of cells selected, or to some of them being merged, without actually pointing out to me what the solution is or which cells are a problem. My usual fix is to copy and paste the data into Word, which still understands sorting.

[David Bird]

Re: I sympathize

I copy and paste into a WSL window, where sorting is definitely understood. Also sometimes the only way to format dates.

[Martin-73]

Re: I sympathize

I read that as format drives... and thought i was even more out of touch than I really am.

[CRConrad]

Sorting...

Just remember to do ALL your sorting EITHER in Excel or with the Unix (Linux) sort utility, but don't mix them: They sort differently.

[Primus Secundus Tertius]

Re: I sympathize

@Daedalus

I have no problems sorting my own data. But other people's data can be difficult: most people have no idea how to make a systematic table. Especially when postal addresses are part of the table. And over the years different people have committed their own different misunderstandings.

[The Oncoming Scorn]

Beer Friday

Canadian electricity generation firm made a similar Excel error that cost it $23m once somebody realised the nature of the copy-and-paste error.

That might explain why Beer Friday at TransAlta as being every two weeks (Yes they treated us to a beer every other Friday in the Canteen) 9 years later.

[chivo243]

Re: Beer Friday

That's bad. The worst I've ever encountered came during a switch replacement project, we were prepping the last few stacks, and were coming up short on our unit count. Report this issue to management, get called into a meeting to discuss the mistake. I produce all my notes, documentation, excel sheets for each stack. Turns out one of the higher ups didn't include the final stack row in the excel auto calculate for the order. The office got very quiet very quickly!

[Yet Another Anonymous coward]

Re: Beer Friday

Most famous is the "austerity is good economics" paper. which is true if you forget to include all countries beginning with A,B or C in the sum().

[FlossyThePig]

Training

So how many people here have had training in any MS Office products?

[renke]

Re: Training

Not enough, though this is a global issue. Like the renaming of genes like SEPT1 and MARCH1 because not enough scientists noticed the automagic conversion to dates.

I don't want to link the paywalled Nature article, but this 2016 paper is funny enough.

"

The problem of Excel software (Microsoft Corp., Redmond, WA, USA) inadvertently converting gene symbols to dates and floating-point numbers was originally described in 2004 [1]. For example, gene symbols such as SEPT2 (Septin 2) and MARCH1 [Membrane-Associated Ring Finger (C3HC4) 1, E3 Ubiquitin Protein Ligase] are converted by default to ‘2-Sep’ and ‘1-Mar’, respectively. Furthermore, RIKEN identifiers were described to be automatically converted to floating point numbers (i.e. from accession ‘2310009E13’ to ‘2.31E+13’).

"

[Joe W]

Re: Training

Yeah. That. Totally not the program's fault, right? That not everybody uses excel, and maybe those who used the abbreviation first used something else (even a flat ASCII file) does of course not come to mind. I have not had to use excel until recently (you cannot do real statistics with excel, one should use a proper data analysis tool / programming language), so I am only now fighting with those brain dead auto convert things. Doesn't help that some data I'm dealing with follows formatting rules from the 70s, and those predate excel. By a lot.

(and yes, I usually use other programs, but manglement likes their excel sheets - so for real work I care sweet Få)

[renke]

Re: Training

> Totally not the program's fault, right?

I am honestly not sure. Personally, I hate all the rather intransparent auto-conversion stuff (and stumble over it all the time) but can see the advantages of it.

Maybe some option like "highlight all automagically converted thingies" would be helpful. _I_ would love and use such a button, especially as I rarely need word/writer/calc/excel/whatever.

[John Brown (no body)]

Re: Training

"some data I'm dealing with follows formatting rules from the 70s, "

Have you tried VisiCalc?

[Mark 85]

Re: Training

Like the renaming of genes like SEPT1 and MARCH1 because not enough scientists noticed the automagic conversion to dates.

Here's a simper link... https://www.theregister.com/2020/08/06/excel_gene_names/

[Wellyboot]

Re: Training

I'd be happy with an automagic on-off option, it can't be that hard to do on a per sheet basis.

[Black Betty]

Re: Training

IIRC it's already possible using cell formatting. Simply force the cell/column data type to TEXT. My bigger beef is that using simple arithmetic operators on explicitly typed dates and times gives nonsensical results.

[Yet Another Anonymous coward]

Re: Training

They've renamed the genes to ";drop table" ?

[CRConrad]

Force to text

IIRC, the trick is to remember to set the cell formatting to text BEFORE you paste in any data. Paste first and set format after, and it will save the auto-formatted version. I think.

[chivo243]

Re: Training

Hehehe You said training! That costs money!

[Binraider]

Re: Training

That's a good question. The last stuff I did formally were part of a HNC in business info tech. Even then, it wasn't specifically on MS. No wonder people can't use word templates correctly (actually, with training, they still suck).

[Doctor Syntax]

Re: Training

"training in any MS Office products"

Isn't it called IT of CS in the school curricula these days?

[Ken Moorhouse]

Was Rank stored in Column E?

If so, the Fifth Column is to blame.

[RM Myers]

"To err is human, to really fcuk things up requires a computer".

This may be old, but it's still true.

[Strahd Ivarius]

Re: "To err is human, to really fcuk things up requires a computer".

Could have been it worse had they let Capita handle the subject?

[Anonymous Coward]

Re: "To err is human, to really fcuk things up requires a computer".

It would definitely have cost more and the system would still be processing battlefield promotions from the Falklands War.

[Yet Another Anonymous coward]

Re: "To err is human, to really fcuk things up requires a computer".

But losses at the Somme would have been greatly reduced if they had only managed to process conscription for 2 soldiers

[Tom 7]

Re: "To err is human, to really fcuk things up requires a computer".

I find computers are generally pretty good. Its when you let people use spreadsheets that problems start and never end. Trying to maintain a mission critical app written in a way that makes it immune to any attempts to apply good software engineering principles or even useful backups in some cases.

I've found its often more of a time saver to re-write peoples spreadsheets in Access or web based db app than trying to get a version out of backups that had the data some idiot had deleted and now needs but trying to put that data into the current stops half the code working the day before the cheque run is never much fun and always your fault and not the company accountant who gets paid 5 times what you do for his lunches alone.

[petef]

Excel users == skiddies?

HUGO have given up the fight on naive use of Excel. There are many pitfalls for average users.

https://www.theregister.com/2020/08/06/excel_gene_names/

[C. P. Cosgrove]

Oh dear !

And to think that the two stripes I had upon my arm once upon a time perhaps should only have been one ( or possibly three ?) . Still, the fact that the very next day, after getting the second, I pinned my best mate's ears to the wall - hard - did no harm to my reputation in the regiment as a hard man.

Then MoD really got it wrong, they commissioned me !

Chris Cosgrove

[ICPurvis47]

Re: Oh dear !

When I was in the ROC, at the time a lowly Observer at 8 Group Headquarters, I was once asked to sit in for the Post Controller (a Leading Observer position) as we were a bit short handed. I was busy fielding queries from and supplying answers to the posts, when our crew's Observer Officer took over in the Commander's position. I smiled at her and drew two fingers across my upper arm, about where the LO's Brevet would be. She glared at me and ignored me for the rest of the shift. Afterwards, she came up to me in the canteen and asked "Why did you make a rude gesture at me?". I was surprised that she'd taken it that way, and said "Oh, no, I wasn't making a rude gesture, I was indicating that I was doing a Leading Observer's job, so where are my stripes?". The following week, when I arrived for the training session, there was a brown envelope addresssed to 98052, L/Observer Purvis IC. It contained my letter of promotion and a pair of brevets to sew onto my uniform.

[ICPurvis47]

Re: Oh dear !

Some time later, on an International Exercise, I was by this time a Chief Observer (equivalent to Sergeant) and was in charge of the Tape Centre, from which messages were sent by teletype to other Group Operation Centres. We were informed that we were to be ready to receive a visit from some very top brass, including the Lord Lieutenant of Warwickshire. Accordingly, we all lined up in front of the equipment, and as my wife was standing next to me (she was a Woman Observer, and was working in the Tape Centre under me), I took her hand. The visit passed off successfully, I was able to answer the Lord Lieutenant's questions, and the party moved on. Some time later, another C/Obs. came to relieve me, and said that my presence was requested in the Officers' Room. I duly presented myself and was taken to task, "Why were you holding hands with Woman Observer Crane?" I explained that I wasn't holding hands with W Obs. Crane, I was holding hands with W Obs. Purvis. "Oh! you're married, are you, when did that happen?" "About ten days ago" I replied. "What the hell are you doing here then? You should be on your honeymoon". I explained that as the Corps, and this Intex exercise was important to both of us, we had decided to split our honeymoon into two parts, spending a week in the New Forest, then attending the Exercise, and then taking another week in the Lake District before returning to work. The Lord Lieutenant roared with laughter and said "That's what I like to see, dedication". Two weeks later my wife and I both received a Lord Lieutenant's Commendation, which was presented to us by a very sheepish Observer Commander.

[Stephen May]

Re: Oh dear !

I'm 99% sure we own where you used to be based - ROC Lawford Heath?

[ICPurvis47]

Re: Oh dear !

Yes, I was there from 1971 until we were stood down in 1991. Before that I was an Observer on a post at Chigwell, in No 4 Group, Colchester from 1966 until 1971.

[Pascal Monett]

"Lessons will be learned"

Lesson #1 : When needing to compile data for thousands upon thousands of employees in order to judge promotions, use a proper database with controls and safeguards, not Excel.

But hey, this the military so the lesson will be Get Somebody Else to Double-Check The Figures.

[Yet Another Anonymous coward]

Re: "Lessons will be learned"

If it's like any government IT system.

They probably had them in a proper database but it couldn't do sorting, or printing, or mail merge -so they export to excel to do any work and then import the results.

[CRConrad]

s/it/they/g

They probably had them in a proper database but THEY couldn't do sorting, or printing, or mail merge.

[Ken Moorhouse]

Re: s/it/they/g

I have a customer I see periodically who does do sorting, printing and mail-merge using Excel. The process still gives him headaches because he does it so rarely, on a variety of unconnected data, so I give him a refresher on the relevance of the steps involved. To give him his due, he is in his 90's!

Even as a staunch critic of spreadsheets, I am prepared to acknowledge that Excel is a good metaphor for *some* aspects of data presentation, but involve Word and mail-merge and it is anything but intuitive for the average man-in-the-street.

[Anonymous Coward]

The whole department once got a 25% bonus where I work

The temp they got in to do the spreadsheet that calculated what that equated to against everyone's salary's just dragged and copied 25 down the whole of the sheet. Unfortunately it was just the guy (in sales) at the top who had a 25% bonus, most of us should have got between 5 and 10%.

It took them a couple of months before they found out because everyone just kept quiet about the surprise bonus bonus in our salaries.

It took them 3 years to claw back the money that we "owed them" - every year we got a bonus letter saying, well you we due to get 6% bonus but because we gave you a 25% the other year you won't be getting it....

[Doctor Syntax]

"The mistake was noticed quickly"

Not quickly enough.

[Winkypop]

Forget the “Right Stuff“

The British Army has the “Correct Sort” (sometimes)

[Ken Moorhouse]

Firing Range

You 'orrible little man, if you don't select the whole range, you are going to end up in the firing range. And as the Army doesn't fire people, you know what that means, don't you?

Gulp.

[John Doe 12]

Copy And Paste

Beware copy and paste in general - depending on the context. I managed to shake off a 3 year business contract I no longer wanted after only 15 months because the genius who drafted the paperwork copy / pasted from a 1 year version and forgot to modify all the exit clauses. This cost them EUR50,000 and saved me almost the same amount - once the solicitor fees were paid :-D

[Anonymous Coward]

They have JPA, a massive HR system, for this stuff

Sadly JPA's a monstrosity built on an Oracle product by CSC. Hilariously the build contract included delivery of zero automated tests, so any modifications require some very expensive manual testing of every tiny bit of every feature.

I've seen MOD HR people use it. It's soul destroyingly terrible. Clientside Java popups pop up other popups, ad infinitum. Built by the lowest bidder, on a "deal" licence with Oracle.

Now I think about it, no wonder they used Excel.

[Anonymous Coward]

Re: They have JPA, a massive HR system, for this stuff

You're half right, CSC didn't build it they took over the monstrosity from HP/EDS and the Civil Service Twonks before them...

[herman]

1 Liner

Well, the old saying in the Army is that 3 lines on the upper arm (Sergeant) means he can read and write, 2 lines (Corporal) means he can read or write and 1 line (Lance Corporal) means he knows someone who can read or write. So the Excel error was probably made by a 1 liner.

[Merrill]

Matthew 20:16

So the last shall be first, and the first last: for many be called, but few chosen.

[Potemkine!]

Does this snafu also apply to...

... the Queen's Own McKamikaze Highlanders? Because one has to hurry up to correct the mistake.

[sitta_europea]

Yeah, I used Excel once.

It was to make a VAT return for the new HMRC FETID programme.

It took three days of farting about and an 850kByte Excel macro-enabled-despite-all-the-warnings-about-it-being-dangerous spreadsheet file to send nine numbers to HMRC.

When it worked, that is, which four out of four times it didn't at the first attempt for a different reason every tome so I had to hack the fucking thing every time.

After a year of dreading having to do it again I binned it, and got something that didn't use Excel.

That worked like a charm, first time, and the CSV file for the data was a whopping 69 bytes.

[Anonymous Coward]

Well....

the article's picture appears to be of a US soldier - can't quite tell if he's wearing RDF or Woodland in the picture, but either way its old kit (The M1 helmet went out of US service around 1985) - I'm assuming our lot are at least dealing with armed forces from the right country (though sometimes I do wonder).

[cosymart]

Photo

Sent a correction :-)