Author Topic: =SUM(A23/B21)  (Read 11462 times)

Pingu

  • Put away those fiery biscuits!
  • Mrs Pingu's domestique
    • the Igloo
Re: =SUM(A23/B21)
« Reply #25 on: 27 January, 2016, 11:21:34 am »
Ah, yes, of course  :facepalm: I guess I haven't thought of summing one item before!

ElyDave

  • Royal and Ancient Polar Bear Society member 263583
Re: =SUM(A23/B21)
« Reply #26 on: 27 January, 2016, 11:22:07 am »
my worst bugbear when I have to audit other peoples spreadsheets (a very regular thing) is the use of fixed factors, but not as references.

i.e. x = y*44.3046/12

where in this case 44.3046 = Mol wt CO2 and 12 = mol wt C (Not exact, just illustrative purposes)

rather than setting up a page of reference factors with a nice little table of Mol Wts, physical constants etc so you can have

x = y * $A$23/$A$24

they only need to change in one place and I only need to verify in one place, and sample check their correct usage

Or use named references so you could have x = y * MolWtCO2 / MolWtC to make it easier to work out what the formula is doing.

exactly.  Bloody clients
“Procrastination is the thief of time, collar him.” –Charles Dickens

Re: =SUM(A23/B21)
« Reply #27 on: 27 January, 2016, 11:31:40 am »
Ah, yes, of course  :facepalm: I guess I haven't thought of summing one item before!

Looked at that way, there'll be an impact on performance. Probably not significant with most spreadsheets, but definitely wrong and not just a different way of doing things.
We have two ears and one mouth for a reason. We should do twice as much listening as talking.

Jaded

  • The Codfather
  • Formerly known as Jaded
Re: =SUM(A23/B21)
« Reply #28 on: 27 January, 2016, 03:15:48 pm »
Performance and also on the transparency of the calculating processes in a sheet. And possibly sloppiness in future coding, if these users ever get to do future coding.

"Why the hell are we summing cells here? Oh, we aren't"  >:( >:(
It is simpler than it looks.

Bluebottle

  • Everybody's gotta be somewhere
Re: =SUM(A23/B21)
« Reply #29 on: 27 January, 2016, 08:16:57 pm »
Quote from: OP
=SUM(A23/B21)


Why does Excel allow that to work  ???

It is, as I am fond of telling my students, cos excel is thick as pigshit.
Dieu, je vous soupçonne d'être un intellectuel de gauche.

FGG #5465

Re: =SUM(A23/B21)
« Reply #30 on: 28 January, 2016, 07:36:51 am »
Bad workman.

If you use a hammer incorrectly and end up with a bent nail, or bruised thumb, do you blame the hammer?
We have two ears and one mouth for a reason. We should do twice as much listening as talking.

Jaded

  • The Codfather
  • Formerly known as Jaded
Re: =SUM(A23/B21)
« Reply #31 on: 28 January, 2016, 07:55:35 am »
Hammers don't have the ability to tell if the user is using wrongly.
It is simpler than it looks.

Re: =SUM(A23/B21)
« Reply #32 on: 28 January, 2016, 08:17:26 am »
If I had a hammer, I'd hammer in the morning, I'd hammer in the evening, all over this land.
They don't let me have a hammer.

Re: =SUM(A23/B21)
« Reply #33 on: 28 January, 2016, 08:19:47 am »
my worst bugbear when I have to audit other peoples spreadsheets (a very regular thing) is the use of fixed factors, but not as references.

i.e. x = y*44.3046/12

where in this case 44.3046 = Mol wt CO2 and 12 = mol wt C (Not exact, just illustrative purposes)

rather than setting up a page of reference factors with a nice little table of Mol Wts, physical constants etc so you can have

x = y * $A$23/$A$24

they only need to change in one place and I only need to verify in one place, and sample check their correct usage

This drags me back to the dark ages where code was properly documented, each section had comments describing it's function and data items were appropriately named.   
 

Re: =SUM(A23/B21)
« Reply #34 on: 28 January, 2016, 08:44:33 am »
Hammers don't have the ability to tell if the user is using wrongly.

Adding functionality to detect harmless misuse of =SUM, which would be executed everywhere =SUM is used (even correctly), would degrade the performance of all spreadsheets. Some spreadsheets are very, very large and could have thousands (even millions) of =SUMs to validate.

Letting the harmless misuse through degrades the performance of only those spreadsheets where =SUM has been used unnecessarily.

As for the origins of the incorrect/poor usage of =SUM, here's an interesting discussion: http://www.excelforum.com/excel-general/540503-why-do-people-use-sum-unnecessarily.html

------------------------

I can assure you that communication is involved in the misuse of a hammer which results in a bruised thumb. The hammer tells you - non-verbally - that you're an idiot. You "thank" the hammer, verbally and (often) very abusively for imparting the gifts of knowledge and experience. The Hammer God is ruthless and unforgiving. Treat it well and it will do good work. Treat it unwisely and it will screw up your work and/or teach you an unpleasant, painful lesson.
We have two ears and one mouth for a reason. We should do twice as much listening as talking.

Re: =SUM(A23/B21)
« Reply #35 on: 28 January, 2016, 09:01:37 am »
I have a zen approach to using hammers and axes.  In essence you have to 'be' the hammer or axe head and forget that the shaft exists.  You then fly towards the target with unerring accuracy.

I haven't figured out the zen approach to excel.
Move Faster and Bake Things

Pancho

  • لَا أَعْبُدُ مَا تَعْبُدُونَ
Re: =SUM(A23/B21)
« Reply #36 on: 28 January, 2016, 09:41:27 am »
As someone who sees a lot of financial spreadsheets, I'm often pretty alarmed at the bodges, hacks and general un-auditable-ness that seems commonplace.

These are multi-megabyte, massively-linked and inter-linked tomes that spit out an answer in the £hundreds-of-millions or £billions range. The implications of an incorrect decimal point in a key, but deeply buried, cell are massive.

It makes me come out in a cold sweat when I think too much about how they'll have evolved over the course of months as the conceptual model or the personnel involved change. And by "evolved", I mean sheets added, hardcoded numbers stuck in "just for now", etc. And, naturally, the final changes will have been made to an immovable deadline - probably at 0300 by some poor sod who's not slept for two days.

I've seen this in several industries and both public and private sector - if anyone is practising Excel discipline, I've yet to encounter it.

And, another thing: version control! Don't even get me started!

ian

Re: =SUM(A23/B21)
« Reply #37 on: 28 January, 2016, 09:48:35 am »
I use Excel a lot.

This is how much training and instruction I've received in my career.

><

I've created formulae so complex, so labyrinthine that they've come to wonder for themselves what it that they're supposed to do.

ElyDave

  • Royal and Ancient Polar Bear Society member 263583
Re: =SUM(A23/B21)
« Reply #38 on: 28 January, 2016, 09:49:18 am »
my worst bugbear when I have to audit other peoples spreadsheets (a very regular thing) is the use of fixed factors, but not as references.

i.e. x = y*44.3046/12

where in this case 44.3046 = Mol wt CO2 and 12 = mol wt C (Not exact, just illustrative purposes)

rather than setting up a page of reference factors with a nice little table of Mol Wts, physical constants etc so you can have

x = y * $A$23/$A$24

they only need to change in one place and I only need to verify in one place, and sample check their correct usage

This drags me back to the dark ages where code was properly documented, each section had comments describing it's function and data items were appropriately named.

I once had a manager who insisted quite rightly on that kind of discipline which I still follow for non-personal spreadsheets.  He however was the worst culprit for throwing you a half-baked, undocumented, scrappy kind of bag of crap typically with the phrase "you can sort that out for me, needs a bit of tidying"  :demon:
“Procrastination is the thief of time, collar him.” –Charles Dickens

ElyDave

  • Royal and Ancient Polar Bear Society member 263583
Re: =SUM(A23/B21)
« Reply #39 on: 28 January, 2016, 09:53:32 am »
As someone who sees a lot of financial spreadsheets, I'm often pretty alarmed at the bodges, hacks and general un-auditable-ness that seems commonplace.

These are multi-megabyte, massively-linked and inter-linked tomes that spit out an answer in the £hundreds-of-millions or £billions range. The implications of an incorrect decimal point in a key, but deeply buried, cell are massive.

It makes me come out in a cold sweat when I think too much about how they'll have evolved over the course of months as the conceptual model or the personnel involved change. And by "evolved", I mean sheets added, hardcoded numbers stuck in "just for now", etc. And, naturally, the final changes will have been made to an immovable deadline - probably at 0300 by some poor sod who's not slept for two days.

I've seen this in several industries and both public and private sector - if anyone is practising Excel discipline, I've yet to encounter it.

And, another thing: version control! Don't even get me started!

I share your pain.   The spreadsheets I verify are for emissions trading purposes so have both financial and compliance implications.  The cost of a tonne of CO2 is pretty low at the moment, but the fines at 100 euro per tonne and adverse publicity are the reasons not to get it wrong.  Still doesn't seem to be much of a driver to get it any more right then "you can take a look at it for us and spot any problems" which is not really my job in any case.

“Procrastination is the thief of time, collar him.” –Charles Dickens

David Martin

  • Thats Dr Oi You thankyouverymuch
Re: =SUM(A23/B21)
« Reply #40 on: 28 January, 2016, 10:01:53 am »
Quote from: OP
=SUM(A23/B21)


Why does Excel allow that to work  ???
because it evaluates A23/B21, then sums it.
Try =SUM(A23/B21, A24/B22) (given appropriate values in the cells of course.)
"By creating we think. By living we learn" - Patrick Geddes

David Martin

  • Thats Dr Oi You thankyouverymuch
Re: =SUM(A23/B21)
« Reply #41 on: 28 January, 2016, 10:03:23 am »
I have a zen approach to using hammers and axes.  In essence you have to 'be' the hammer or axe head and forget that the shaft exists.  You then fly towards the target with unerring accuracy.

I haven't figured out the zen approach to excel.

Use R instead. Then Excel zen is fine. You just use Excel for manual data entry.
"By creating we think. By living we learn" - Patrick Geddes

Re: =SUM(A23/B21)
« Reply #42 on: 28 January, 2016, 10:09:00 am »
@Pancho

Sounds like the sort of stuff that, really, should have a properly specified, designed, documented, written and tested system.

Spreadsheets are for small stuff, personal stuff, unimportant but useful stuff, and sometimes modelling for more complex stuff.

I've often written stuff then - 6 months or so later - wondered what the hell I was thinking (both Excel and programmatic stuff). If I'm developing anything that's not 'just for me' it's always properly documented.  :thumbsup:

If it's just for me and I suspect I'll be using it more or less permanently it'll be documented.  :thumbsup:

I tend to get caught out by the just-for-me temporary stuff that becomes permanent or semi-permanent. ::-) :facepalm:
We have two ears and one mouth for a reason. We should do twice as much listening as talking.

David Martin

  • Thats Dr Oi You thankyouverymuch
Re: =SUM(A23/B21)
« Reply #43 on: 28 January, 2016, 10:34:56 am »
That's why I do my quant stuff in R or Python - you can separate code from data, test and keep versions.
And even drag stuff out of spreadsheets if necessary.
"By creating we think. By living we learn" - Patrick Geddes

ian

Re: =SUM(A23/B21)
« Reply #44 on: 28 January, 2016, 10:53:11 am »
If you work in any big business you'll have Excel. You won't have R, Python, etc. nor the time or resources to learn them. I manage some big datasets (>5 million rows) that would certainly benefit from something with a bit more whumpf then Excel. That ain't going to happen because it's 'not my job' and they pay me too much to mess around with data. That's the kind of work they ship off to India. That it's not practical (or ultimately cost effective) to explain to a shifting set of offshore resources what I need and get updates etc. (which would turn into a project, which I'd have to spec, write a business case, present to the relevant investment committees, and do so every bloody year while risking it being cut in monthly cost-cutting exercise) is a bit of a lost cause. So in Excel I dibble.

I like to hide an array formula is every spreadsheet. I'm pretty sure there's a method involving array formulae that would open a hell portal and pour demons on the Earth. That's my ultimate plan.

Re: =SUM(A23/B21)
« Reply #45 on: 28 January, 2016, 10:58:26 am »
Careful - not all Easter Eggs are pleasant surprises.

Go Easter-Egg hunting at your own peril.

http://chandoo.org/wp/2013/04/01/unlock-angy-formulas-excel-game/
We have two ears and one mouth for a reason. We should do twice as much listening as talking.

Pancho

  • لَا أَعْبُدُ مَا تَعْبُدُونَ
Re: =SUM(A23/B21)
« Reply #46 on: 28 January, 2016, 11:07:31 am »
I use Excel a lot.

This is how much training and instruction I've received in my career.

><

I've created formulae so complex, so labyrinthine that they've come to wonder for themselves what it that they're supposed to do.

Which is precisely why I face[1] the problems I do. Everyone involved are brainy manager-y types. Who've all picked it up as they go along.

Being an ex-software engineer, I'm a rare beast - someone who understands documentation, system testing, maintainability, etc, etc. But I'm the only one who worries about the integrity of the nuts-and-bolts - to everyone else, being on top of  "the numbers" is just another high level facet of the management task; criticism of the tools is regarded as criticism as their ability as managers or their departmental competence.

Trouble is, I'm also the bloke who's ultimately responsible for the number being right - hence the cold sweats.

And while we're griping. Anyone want to talk about risk? The identification, costing, and pricing thereof? Sometimes I think I'm the only person with a clue.

[1] Actually, "faced" rather than "face" - I'm an unemployed scuzzball. A role I'm still having trouble acclimatising to. Excel integrity is now an SEP.

Re: =SUM(A23/B21)
« Reply #47 on: 28 January, 2016, 11:13:31 am »
my worst bugbear when I have to audit other peoples spreadsheets (a very regular thing) is the use of fixed factors, but not as references.

i.e. x = y*44.3046/12

where in this case 44.3046 = Mol wt CO2 and 12 = mol wt C (Not exact, just illustrative purposes)

rather than setting up a page of reference factors with a nice little table of Mol Wts, physical constants etc so you can have

x = y * $A$23/$A$24

they only need to change in one place and I only need to verify in one place, and sample check their correct usage

This drags me back to the dark ages where code was properly documented, each section had comments describing it's function and data items were appropriately named.
Burn the heretic!

Don't you know that all of that is now anathema? Those of us who remember those days must keep quiet, for fear of ostracism.
"A woman on a bicycle has all the world before her where to choose; she can go where she will, no man hindering." The Type-Writer Girl, 1897

Re: =SUM(A23/B21)
« Reply #48 on: 28 January, 2016, 11:25:52 am »
I got out of commercial software development when one of my customers (an IT manager) saw the box for the development language/tool I used. It was clearly labelled "Rapid Application Development" and "Self-Documenting."

From then on he expected complex and virtually impossible (sometimes actually impossible) changes to be made to a $multimillion system overnight and without the benefits of analysis, design, documentation (code or user) and testing. When the overnight target wasn't achieved, or didn't work, he'd blame the programmer and expect them to put it right FOC. Nothing could be done to bring him back to reality.

I became a plumber having decided I'd prefer to earn a living by putting my hand down somebody else's toilet than continue cleaning up typical IT manager/director shite.

We have two ears and one mouth for a reason. We should do twice as much listening as talking.

Pingu

  • Put away those fiery biscuits!
  • Mrs Pingu's domestique
    • the Igloo
Re: =SUM(A23/B21)
« Reply #49 on: 28 January, 2016, 11:32:22 am »
I use Excel a lot.

This is how much training and instruction I've received in my career.

><

I've created formulae so complex, so labyrinthine that they've come to wonder for themselves what it that they're supposed to do.

The universe we think we live in is being run in a tab in a Excel 3000 workbook. That's why it seems to be not quite right.