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

Jaded

  • The Codfather
  • Formerly known as Jaded
=SUM(A23/B21)
« on: January 26, 2016, 09:26:43 pm »
Was helping someone with a spreadsheet this week and this is how the formulae were all constructed.

I'd forgotten that I've seen this so many times before. Is there a batch of rogue Excel trainers out there telling people this is how formulae are done?
If you don't like your democracy, vote against it.

red marley

Re: =SUM(A23/B21)
« Reply #1 on: January 26, 2016, 09:28:45 pm »
Oh sir! Not sums again! Can we do colouring in instead?

Jaded

  • The Codfather
  • Formerly known as Jaded
Re: =SUM(A23/B21)
« Reply #2 on: January 26, 2016, 09:46:36 pm »
Conditional?
If you don't like your democracy, vote against it.

Pingu

  • Put away those fiery biscuits!
  • Mrs Pingu's domestique
    • the Igloo
Re: =SUM(A23/B21)
« Reply #3 on: January 26, 2016, 09:57:00 pm »
Oh sir! Not sums again! Can we do colouring in instead?

Four colour theorem  :thumbsup:

David Martin

  • Thats Dr Oi You thankyouverymuch
Re: =SUM(A23/B21)
« Reply #4 on: January 26, 2016, 09:57:37 pm »
Is that integer or floating point division?
"By creating we think. By living we learn" - Patrick Geddes

Jaded

  • The Codfather
  • Formerly known as Jaded
Re: =SUM(A23/B21)
« Reply #5 on: January 26, 2016, 10:05:37 pm »
Who cares if it adds up...
If you don't like your democracy, vote against it.

Re: =SUM(A23/B21)
« Reply #6 on: January 26, 2016, 10:17:27 pm »
=SUM(A23/B21)

after some testing (not exhaustive) appears to do exactly the same as:

=A23/B21

Maybe a hangover from an early version of Excel, or an inheritance from some other spreadsheet system?
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 #7 on: January 26, 2016, 10:22:53 pm »
No. It was a new spreadsheet.

If you don't like your democracy, vote against it.

Bluebottle

  • Everybody's gotta be somewhere
Re: =SUM(A23/B21)
« Reply #8 on: January 26, 2016, 10:45:56 pm »
I have noticed that over the last couple of years, a large portion of our undergrads all use formulae in the form of

=SUM(something+/*-somethingelse)

When challenged why, the answer is almost certainly, "We were told to do that at school. 'SUM' tells excel that a sum/formula will be used, innit."

Mutters darkly about formulaic teaching....
Dieu, je vous soupçonne d'être un intellectuel de gauche.

FGG #5465

Jaded

  • The Codfather
  • Formerly known as Jaded
Re: =SUM(A23/B21)
« Reply #9 on: January 26, 2016, 11:02:32 pm »
That is what I thought was happening...

Thanks for a reasonably sensible answer hided in the deluge of clever posts  ;)
If you don't like your democracy, vote against it.

Kim

  • Timelord
Re: =SUM(A23/B21)
« Reply #10 on: January 26, 2016, 11:13:01 pm »
Wait what?

Cargo cult programming has spread to spreadsheets?  *weeps for the fate of mankind*
To ride the Windcheetah, first, you must embrace the cantilever...

David Martin

  • Thats Dr Oi You thankyouverymuch
Re: =SUM(A23/B21)
« Reply #11 on: January 26, 2016, 11:36:12 pm »
So what does the = do then if not indicate a formula will be used?
"By creating we think. By living we learn" - Patrick Geddes

Jaded

  • The Codfather
  • Formerly known as Jaded
Re: =SUM(A23/B21)
« Reply #12 on: January 26, 2016, 11:50:52 pm »
It prepares you to type SUM

Please, please don't tell us that you put SUM in all your formulae  ;D
If you don't like your democracy, vote against it.

Andrew

Re: =SUM(A23/B21)
« Reply #13 on: January 27, 2016, 05:52:35 am »
I like ROUND. It's a good word is round.

ElyDave

  • Royal and Ancient Polar Bear Society member 263583
Re: =SUM(A23/B21)
« Reply #14 on: January 27, 2016, 07:18:57 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
“Procrastination is the thief of time, collar him.” –Charles Dickens

Re: =SUM(A23/B21)
« Reply #15 on: January 27, 2016, 08:20:57 am »
I like ROUND. It's a good word is round.

It sounds like what it is. It's a well rounded word. Rather like Mr Bean's "wibble". Except that's not rounded. It's wibbly.
We have two ears and one mouth for a reason. We should do twice as much listening as talking.

Re: =SUM(A23/B21)
« Reply #16 on: January 27, 2016, 08:28:37 am »
I have noticed that over the last couple of years, a large portion of our undergrads all use formulae in the form of

=SUM(something+/*-somethingelse)

When challenged why, the answer is almost certainly, "We were told to do that at school. 'SUM' tells excel that a sum/formula will be used, innit."

Mutters darkly about formulaic teaching....

Oh gawd. When I'm into my 'dinosaur' years, rather than just my current 'old git' years* younger people who've been brainwashed into thinking they've somehow acquired an education will be trying to 'correct' my use of = without the unnecessary SUM(

*: Some young whipper-snappers already regard me as 'dinosaur' or even 'fossil'.
We have two ears and one mouth for a reason. We should do twice as much listening as talking.

T42

  • Tea tank
Re: =SUM(A23/B21)
« Reply #17 on: January 27, 2016, 08:32:00 am »
Tyrannosoreass?
I've dusted all those old bottles and set them up straight.

Vince

  • Can't climb; won't climb
Re: =SUM(A23/B21)
« Reply #18 on: January 27, 2016, 08:36:00 am »
I have had occasions where entering =A23/B23 just displays the formula into the cell. Next time I'll try enclosing it in a sum() function.
I did check and there were no quotes in the cell.
216km from Marsh Gibbon

Jaded

  • The Codfather
  • Formerly known as Jaded
Re: =SUM(A23/B21)
« Reply #19 on: January 27, 2016, 08:37:10 am »
I think it is because of the Sigma button in the tool bar.

Proper Excel didn't have that.
If you don't like your democracy, vote against it.

Re: =SUM(A23/B21)
« Reply #20 on: January 27, 2016, 08:48:00 am »
Tyrannosoreass?

Only the morning after a vindaloo.
We have two ears and one mouth for a reason. We should do twice as much listening as talking.

Re: =SUM(A23/B21)
« Reply #21 on: January 27, 2016, 09:02:59 am »
I have had occasions where entering =A23/B23 just displays the formula into the cell. Next time I'll try enclosing it in a sum() function.
I did check and there were no quotes in the cell.
Because it was formatted as a 'text' cell ?
Rust never sleeps

Phil W

Re: =SUM(A23/B21)
« Reply #22 on: January 27, 2016, 10:49:38 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.

Pingu

  • Put away those fiery biscuits!
  • Mrs Pingu's domestique
    • the Igloo
Re: =SUM(A23/B21)
« Reply #23 on: January 27, 2016, 11:14:45 am »
Quote from: OP
=SUM(A23/B21)


Why does Excel allow that to work  ???

Re: =SUM(A23/B21)
« Reply #24 on: January 27, 2016, 11:18:46 am »
Nested formulae.

It's working out the division first then working out the sum of everything contained in the brackets.

=SUM(A1) works as well.
We have two ears and one mouth for a reason. We should do twice as much listening as talking.