[Closed] Use of pre-written spreadsheets for work tasks

do you work for F1F9?

No, never heard of them either 🙂

just checking 🙂

Try the NHS. IME, if it absolutely shouldn't have been done using Excel, it will have been.

I was at an Excel Geekoid conference about 10 years ago where someone admitted to being the guardian of an 8gb workbook that over an hour to open 😯 😯

Some that are straightforward extrapolations of volume per month e.g. if percentage growth last month was 8% and December 2016 was 100 widgets, expect 108 widgets in December 2017.

Lots of analysis of cpu/disk/memory to ensure we don't run out.

We also have a formal process for "End User Written Tools" that requires an owner, an author and a reviewer to try and make it less likely that a wrong calculation loses us a few million or in trouble with the regulator.

Hospital Theatre Physician Preference Cards.

Costed List of all of the Products & Equipment required for each Surgeon & each Type of Surgery

Calibration worksheets. I’ve unpicked a few using trace precedents and trace dependents function and it end up looking like a hyperactive spider web.

A few years ago I wrote one for working out duty rosters. You could enter various shift types against each persons name and it calculated total hours per week for individuals and total staff, daily hours, skill mix and so on. Made working out off duty a much simpler task

Oil and Gas Reserve reporting....

We have one that is basically an EDM for BIM projects.

Controls documents. Controls revisions. Ensures naming structure is complied. Checks files existance. Generates issue sheets. Plus making things mire useable.

My input is making it generate files that automatically updates CAD drawings reflect all this information.

Its shite and there is plenty of software that does all this AND provide storage at the same time. Tight fisted to biy the software but happy to spunk hours of peoples time.

Years ago I'd have advised people (not in a professional capacity) to use Excel for these sorts of things as it's a ready made environment for doing calculations and it runs on your computer without needing any hosting or faffing about.

However nowadays we have cloud based platforms where you can knock together small services in all sorts of technologies, many of which are highly optimised for simplifying certain concepts, and string them together, and all the hosting and whatnot is done for you. But you can also link it up to a proper database rather than storing it all in a spreadsheet. And it's all in a centra location, multi-user and so on. You'd end up using Javascript or something, I suppose, but that's not much worse than using VB script.

This is all very interesting thanks folks.

EDM for BIM projects

Electronic document management for.. Building information management?

Do any of you use big complex spreadsheets for your work tasks? The kind that was written by Steve in Accounts ten years ago to let you calculate your clients return on thingy from their initial parameters in whatnots and has ballooned in complexity so no-one knows how it works.

Sort of, I write and maintain large complex spreadsheets doing this sort of thing. But I know how they work and all the logic is in VBA as it's much easier to manage than cell formulae. My tools have been evolving over more than 10 years and are used by all our customers as well as staff. I was in Nigeria this week, running training courses on them...

Would be interesting to see what happens if I get run over by a bus, I've been doing this solo for 10+ years and no one else has taken any interest (small company, so no spare bandwidth).

Hmm footflaps, you might be my target market since I feel that things can end up growing too complex for excel...

Footflaps is me 🙂

But I spend my time moving it to databases as quickly as possible

EBA and PRA FDSF templates.

I’ll just leave that there.

Also "supply and demand" - resource vs projects as most PMs won't accept the output of some new fangled tool like Agile Central.

So you've modelled how supply affects demand, and used excel to calculate the models?

Bikebouy - eh?

Not sure if it's complex enough, but I have written spreadsheets to compare students' unit grades and exam performance with their targets and to work out how they need to perform in various scenarios in order to "succeed".

The front sheet of the workbook is very simple and allows other teachers to just add in grades and see outcomes, then there are plenty of nested "IF" type routines running behind that.

Google it, it’s Friday and I’m knackered.
😆

since I feel that things can end up growing too complex for excel...

Yes and no....

Using Excel with cell formulae gets complex and unmaintainable very quickly, debugging / tracing with trace precedents etc is horrible.

But if you use VBA for your logic then you can structure and annotate it much better and make very complex thing easy to debug, modify and understand. Most of the source data comes from Excel sheets and everyone wants the output analyses in Excel, so using it makes I/O very simple.

I use Excel as the front end and do everything in VBA as I just think it's a neater solution.

Most of the source data comes from Excel sheets

Hmmm..

I wonder if you could collect data in excel and call a rest/json service from it.

Cost value reconciliations in construction.

Cobie data drops in construction.

Used to do the calibration curves for an x-ray generator using quatro-pro as it allowed the techs to see the curves as they entered data and tweak things if necessary. Does that count?

In my experience actuaries tend to be rather fond of it.
Personally I did write a few things in excel/vba for call centre use which was what started me programming.
Since switched to using more entrprisey stuff. I can see though why people do go the knock something together route rather than managing to get my teams time though.

We do.

And there usually wrong or outdated or people with no idea what they are doing have made changes and made them wrong.

They are quite dangerous really in my line of work as people follow them blindly without verifying the maths on their copy

An incredible number of those in banking/finance and imo and ime a huge business risk. I have been in more than a few big rows at work refusing to accept people doing things that way. The alternative is of course to spend hundreds of thousands if not millions on proper customised software but people think they can just bodge it. Nightmare.

It's because the project approval and budget process is too constraining teams have tried to create solutions locally.

User forms to capture inputs for a risk assessment, vba script to populate calculation spreadsheet that has metadata sheets to drive calcs that are extracted back to user form results. Had to be documented, versioned;, tested and backed up under End User Computing policy. But these solutions are now frowned upon...

I'm making a career out of getting these kinds of tools moved to better platforms.
Mol - I'd be interested to hear what kit you're flogging.

Large scale event catering is often calculated using complex excel spreadsheets for ease of use at the end of an event where EPOS tills haven't been available. The problem is they get very big very quickly and errors creep in which no one knows how to fix!

Calculating stock/cash/transfers etc to work out losses etc and you will find most places have been using the same system for years and don't know how to change it.

Happy to share a copy of one if you want it?

The alternative is of course to spend hundreds of thousands if not millions on proper customised software

The gap between a spreadsheet and expensive bespoke is what I am trying to bridge here, so in some ways I'm glad to hear this 🙂

It's because the project approval and budget process is too constraining teams have tried to create solutions locally.

And that ^^^

Mol - I'd be interested to hear what kit you're flogging.

You probably are already aware but I'll PM you 🙂

Happy to share a copy of one if you want it?

What I'm looking at is online so might not be directly relevant, but if you have a really simple one (I am doing a quick prototype) that would be cool, email is in profile.

We tended to use spreadsheets for prototyping a concept; working out what we wanted to know and how to get there from what we had, illustrating to the users, etc. Then we'd get the experts in to write the database, custom software or whatever it needed. For security reasons, we couldn't use anything in the cloud.

They are quite dangerous really in my line of work as people follow them blindly without verifying the maths on their copy

We just use it to grab data using SQL (using ODBC drivers to the DB2 database). Sometimes we get funky and run SQL over an Excel table, but that takes a specific type of masochism.

An incredible number of those in banking/finance and imo and ime a huge business risk. I have been in more than a few big rows at work refusing to accept people doing things that way.

We ran the treasury operation of a large high street bank on Excel for about a week when the expensive customised software broke. That was lots of fun...

The problem with NOT using Excel is that people then blindly trust the alternative solution without having the slightest idea what it does

That's why you don't design systems without stakeholder involvement... which is one of the principles of agile methodology.

The bigger problem still is that people don't know how to commission, design or develop systems - both customers and suppliers. But don't get me started. Other parts of my job are also concerned with empowering businesses to manage their processes without needing to deal with geeks and their paymasters.

We just use it to grab data using SQL (using ODBC drivers to the DB2 database). Sometimes we get funky and run SQL over an Excel table

Using Excel to grab the data as a database front-end but then you can also manipulate it and do sums on it in the tool. Nice idea actually.

I made a huge one years ago in my old company that would generate a complete IP addressing scheme for large VoIP networks. It just kept growing until I could barely remember how it worked.

Now I have one that I use for my company accounts, about 10 different worksheets mainly feeding into 1 summary sheet. Has worked ok for 12 years. It came about after trying to use Sage accounts which was horrendous.

I've known actuaries using hugely complex/expensive actuarial modelling software - output to Excel and then jig about with the data **finger in the air** add/subtract X% "because it looks right".

Same as trail rat. People just fill in the boxes they've been told to and take the answer spewed out as gospel, which it invariably isn't.

The biggest issue I have with Excel is that, at least where I work, it's been used simply as a replacement for paperwork, i.e. something in the 80s/early 90s was done using a paper checklist and when Excel came along that checklist was just copied, and the only change is that you now fill in the boxes with a keyboard rather than a pen. No concept of what Excel can do.
I've tried my best to drag the department into the 21st century by updating old spreadsheets/creating new ones, but there's always some numpty who insists on overwriting a formula, or inserting a row/column thus cocking up VBA cell references running in the background.

I've known actuaries using hugely complex/expensive actuarial modelling software - output to Excel and then jig about with the data **finger in the air** add/subtract X% "because it looks right".

My brother has a consultancy company that produces actuarial software, he is one. His attitude is very much to have a relatively simple system that does 90% of what you want (the Set menu) and then use excel for a la carte cases, rather than try and develop an all encompassing system which is much more complex and consequently expensive.

there's always some numpty who insists on overwriting a formula, or inserting a row/column thus cocking up VBA cell references running in the background.

Named ranges would handle this. But there would be many other ways the incompetent would find to break it.

Numpties gonna numpt.

It is very tricky to predict all the ways people will break stuff when you know how it should be used, and even harder to get the balance of applying controls & keeping things functional.

one company my employers acquired had oodles of stuff on spreadsheets written by steve in accounts and modified by louise in purchasing without any checks and the results were biblical commandments with outputs resulting in pestilence and plague - first stage metals processing (laser cutting etc) most were inventory planning related, what customer part could be cut from what inventory item, how many parts, what other customer parts came from same item and could be nested, what if demand from customer Y increased/decreased and lead time for start stock was X months etc. Multiple related stock items - some customs bonded but physically identical, some different dimensions but could be used to produce same customer part etc.

not a techie but think ended up with some sort of java enabled overlays to the accounting/stock control unix based system that enabled easy entry of related product data/parts data/sorting/filters and the ability to run on screen what ifs with the actual logic coded away from fat fingers

here's always some numpty who insists on overwriting a formula, or inserting a row/column thus cocking up VBA cell references running in the background.

You can stop this by locking cells, protecting worksheets etc. If you can edit stuff you shouldn't, whoever designed the tool did a crap job. All methodology should be locked down so users can't just randomly hack it - whether in Excel or any other tool.

NB A good book I can recommend on Excel VBA application design is..

[img] [/img]

You can stop this by locking cells, protecting worksheets etc. If you can edit stuff you shouldn't, whoever designed the tool did a crap job. All methodology should be locked down so users can't just randomly hack it - whether in Excel or any other tool.

I know this. However I work on a remote site and I'm only there for 3 weeks out of every 6. Work is soul-destroying enough that when I'm home I try to pretend it isn't a thing, and I simply don't answer any Aberdeen phone calls. If something did need changing in a spreadsheet I'd locked when I'm not there then they'd be screwed. It very rarely does, but there's always that one time and so spreadsheets have to be unlocked.

As a life long avoider of any form of process / QA, I can't believe I'm saying this, but the above isn't an excuse, it's an admission of a lack of any form of proper process and a risk to the business......

So I think my first demo will be a pocket money calculator for my kids. They get a basic £2pw and bonuses for chores. I'll invent some complex rules for the purposes of demo e.g. £25p per chore but a bonus 50p for all chores, and maybe -50p for disciplinary measures.

I reckon I can knock that up in an afternoon using public cloud services, have the same data accessible by multiple clients and stored in a persistent location. All done without code or having to find a host and maintain a server.

More complex project for the second demo I think.

I reckon I can knock that up in an afternoon using public cloud services, have the same data accessible by multiple clients and stored in a persistent location. All done without code or having to find a host and maintain a server.

You've just discovered Google Docs?

it's an admission of a lack of any form of proper process and a risk to the business......

You've just discovered Google Docs?

Not what I plan to do, no. But as always there are many ways to do things, each with pros and cons. And demos are by nature simple when they are dreamed out of thin air, and simple demos can always be implemented in different ways.

If you're nice I might show you what I've done 🙂

go on, show us. \this is a good thread

Quite probably, but that's not my problem. I am invested precisely 0% in the god-awful toxic environment that is where I work. I try to make my particular role a bit easier because of the whole "work smarter, not harder" thing and when some people see they get on board, that's all. Some of those people don't know what they're doing, but again that's not my problem

Indeed. Must be an industry thing.

We have shedloads of stuff written in Excel by various ex-members of various departments within our organisation, some quite complex. People get quite sniffy when we in IT don't support/troubleshoot/bugfix/update this stuff for them.

Calculation of solubility curves, crystallisation DOE, assay determination.

Oh and booking equipment.