|
Jan 2020
8:11pm, 30 Jan 2020
113 posts
|
Neal
I think saying "if you aren't using formulas, you shouldn't be using a spreadsheet" is overly simplistic.
For me, it is more of a case of knowing what computers are good at and what people are good at. If a person is doing something that people are rubbish at but computers are good at, such as adding up a big columns of figures or copying stuff from one place to another, then it is a sign that they are either using the wrong tool or using the right tool poorly.
If you are going to spend a significant amount of time on a fairly mindless task, spend at least some time trying to find a different way of accomplishing the task in a more efficient way.
Ok, that's a bit woolly and general - but that is the sort of thing that people are much better at dealing with than computers.
|
|
Feb 2020
9:03pm, 2 Feb 2020
18,804 posts
|
ChrisHB
Who can see a human error here?
=SUM(H3:H369) - this is unsurprisingly in column H
It gives 27 but if I count using my fingers the answer is 43.
Or if I put the same formula in column G, it gives 670
(my mistake is cutting and pasting without changing H to G)
|
|
Feb 2020
5:21pm, 3 Feb 2020
18,931 posts
|
flanker
some crud in the text along with figures in some of those numbers, so it's not actually treating them as numbers?
|
|
Feb 2020
6:03pm, 3 Feb 2020
18,808 posts
|
ChrisHB
that would explain the first. Can I make the cells display in hex or something to show what's really in them?
|
|
Feb 2020
6:38pm, 3 Feb 2020
10,252 posts
|
larkim
Find a way to test each cell, e.g. =h3*2, review the outputs when copied down? Error should be visually evident.
|
|
Feb 2020
6:38pm, 3 Feb 2020
1,688 posts
|
um
Chris - To identify any crud ... maybe just insert a column after H and set each cell to =Hnn+0
Anything not a number will show as #VALUE! or 0 ?
If you select the column, does the count and sum in the lowest grey border show anything that matches the sum() or your manual sum?
No hidden or filtered rows that you are missing in the manual sum?
|
|
Feb 2020
7:58pm, 3 Feb 2020
18,809 posts
|
ChrisHB
thank you.
That revealed that one '1' had a hidden quote in front of it. I still can't make the sum right.
The other matter has gone away.
|
|
Feb 2020
8:02pm, 3 Feb 2020
18,810 posts
|
ChrisHB
the numbers are correct: if I put in =h3+h4+h5....H9 it give 8 which is correct. But =sum(h3:h9) produces 2.
|
|
Feb 2020
1:03am, 4 Feb 2020
18,937 posts
|
flanker
That's weird!
The only thing I can think of that could cause that is that you have some weird formatting on those cells so that it displays negative numbers without the minus sign.
|
|
Feb 2020
8:44am, 4 Feb 2020
11,900 posts
|
Garfield
Make sure that it's in formula mode (fx) Chris... I've tried pasting formulae into other cells but the formula didn't always *take*, resulting in odd results. Also when I've tried typing it in, it didn't always work if I wasn't in formula mode (that's what I refer to it as, not sure what the real name for it!)
|
Excel skills test
Mar 2019