DH has been using a calculator to add things recorded in Excel

[RokaandRoll]

AIBU to think this is absolutely astonishing?

I found out because we were doing a new budget spreadsheet and he read out what we spend on different things each month while I recorded each item in Excel. He then asked me to read the amounts back to him so he could add them up. I was like WHAT??? I'll just add a formula in Excel. He said "really, you can do that?" I asked him what he thought Excel was for, and he said he didn't know as no one had taught him.

Have you ever found out someone was doing something in a completely bizarre and illogical way on a similar level as this? DH is in his 50s and is a quite intelligent person (or so I thought). He has used Excel in his job although obviously not extensively. AIBU to be completely shocked?

My favourite Excel-related story is this genetics one:

https://www.bbc.co.uk/news/technology-37176926

Genes have alphanumeric names assigned by scientists when the genes were discovered. There are a few names: SEPT1 (septin 1) is one and MAR1 is another, which when scientists put lists of genes into Excel, Excel would autocorrect these gene names to dates like 1-SEPT-yyyy and cause a data management problem that is difficult to spot and fix. It is a problem that has plagued genetics research and got so prevalent in the end the scientists tried to rename the genes to attempt to resolve the issue.

As I've said several times, DH doesn't take this as talking down, he has been laughing at himself, he also laughs at me when I do something daft, and this is a lighthearted post. It isn't behind his back!

You're clearly someone who takes herself far too seriously and needs to lighten up before you bore the hell out of anyone near you - DH and I are not like that thank goodness!

And you think I'm narcissistic- how bizarre 😂.

Soooo how do you use the add function on Excel…? Asking for a friend.

=cellA+cellB+cellZ etc. or, if they're all in a column, or row, you can just highlight the cells and the amount will appear in the bottom left corner.

If you have many numbers, like over 20 numbers, you won't want to highlight them with your mouse.

Use =sum(b1:b500) to sum cells b1 to b500

"or so I thought" wow that is so judgemental! Over something he'd never been taught.

I have a question that I would like to ask of the Excel afficionados on here.

I used to be able to use something called Explode on my spreadsheets so that I could split a file by customer and extract multiple worksheets. Since upgrading to a more updated version of Windows it no longer works. I have googled in vain and it has bamboozled the IT guys at work.

Does anyone have any ideas?

No idea, sorry 😔

I've explained so many times in subsequent comments that this was lighthearted and DH is very intelligent and has been laughing over this - so can we give this element of the discussion a rest now please?

People aren’t going to read all the comments before replying.

I’ve never started a thread, so I don’t know: can an OP edit for clarity?

You will probably still get responses from people who have had “it’s only a joke” weaponised against them in a relationship, though.

Not sure I am following what you need?

Do you mean split a work book? Extract the info by customer and separate into separate worksheets?

How you would do it would depend on how the data is stored.

Cannot believe I've just seen knowledge of spreadsheet formulae used as a measuring stick of intellectual curiosity tbh

I tried to edit it to add "light-hearted" but wasn't able to - I think there must be a time limit for editing and I missed it.

I wouldn't expect anyone to read all the comments (I haven't even read them all myself) but I would have thought people would look at a few of the OP's follow-up comments to see if she's addressed the obvious point I was planning to make before posting something insulting 19 pages into a thread. But there I go again assuming people will do what I consider obvious when it should be very clear to me they won't.

Very good point about how people will have experienced weaponising "it's only a joke." That's a terrible thing to do to someone and I think it happens sadly all too frequently. DH and I aren't like that with each other but I can see that's far from clear in my original post. DH is much better than I am with the written word (I'm more of a numbers person) so he wouldn't have made the mistake I did 😊.

In the 1980s I taught word processing in adult education classes. It was routine in those days to install word processors in offices, but provide no training. My students felt guilty and stressed because they hadn't a clue what they were doing. I told them that it wasn't their fault. It was like someone buying you a car and hoping you would somehow work out how to drive.

There should be qualifications you can achieve on software packages. If you are going to use it in your job, you should have a certificate of competence.
My favourite post is the one about the yellow paper.

Haven't a clue how to use excel. Never learnt it at school/college (not even 40 yet so perhaps my school was a bit behind?)
Always jealous when people go bish bash bosh here's a fancy spreadsheet. Just never got around to it/had the time.
-managerial private sector job where all our systems are pre built.

Did have a grown man ask me once what a tomato was though so don't feel so bad about my abilities.

This made me chuckle!

I'm a self-taught advanced Excel user. Google is your friend, i've never had training, except at school (i'm 40 this year)

Can't you just select the sheets you need (multiple use CONTROL SHIFT) and then save to a new book?
(Right click mouse to get options)

I've had similar - protein structures are stored with codes like 1CRN. I really didn't appreciate excel taking it upon itself to convert something like 7JUN into a date - wtf would it do that for one or two values in column in which the vast majority of the data could not be converted to a date?

I think you have to pre-format the cell as text to avoid it changing to a date.
It can be pretty annoying!

Thanks for your suggestion - but I wasn’t looking for a fix. For a sense of the scale of the problem, this issue affects thousands of scientific research papers and studies internationally.

In the end Excel did provide an option to disable this feature entirely in October last year, reported in several places: https://www.theverge.com/2023/10/21/23926585/microsoft-excel-misreading-dates-human-genes-conversion-fixed

My perspective is that Excel is the incorrect tool for storing and analysing genetic data of this type and scale. Scientists ought not be using it for this type of project. This is what one of the most senior genetics researchers internationally says in the BBC article and I agree. I still love this story so much because of how it illustrates the human face of scientific research, how scientists tend to use the tools at hand to do the research they care about and how unexpected problems can arise.

According to The Verge there is now an option to disable the feature. I didn’t know it affected proteins as well but that doesn’t surprise me.

https://www.theverge.com/2023/10/21/23926585/microsoft-excel-misreading-dates-human-genes-conversion-fixed

@erinaceus
Ah thanks for that.

Yes database storage would be much better than a spreadsheet!

According to The Verge there is now an option to disable the feature. I didn’t know it affected proteins as well but that doesn’t surprise me.

Fixing something in 2023 which must be years after the problem was identified isn't exactly impressive. Tbh anything which may alter data should always have been an option, default off not on.

No, because the spreadsheet has about 36,000 lines and about 300 customers, each of which has a different number of lines.
Doing it manually takes ages.

The Exploder used to treat each customer as a unique identitiy and explode the master spreadsheet into 300 spreadsheets in about 30 seconds.

A spreadsheet is almost certainly the wrong tool for a dataset this size.