Log In
Register
Home
December 18, 2018
Offline
Hello there, I'm encountering an error that doesn't make any sense to me. I have three columns. I'm trying to Group by the first two and SUM the third. The third column is decimal numbers, however when I SUM them, all the zeros are returned as errors.
I've tried some things like changing the number column to different types prior to grouping, but nothing seems to be working. Is this correct behaviour? Should I just replace the errors with zeros?
Thanks for your help
July 16, 2010
Offline
Hi Adam,
It should simply return a group with zero total. Do the zeros show as errors before you applied the grouping? I'd try replacing zeros with null before grouping.
Mynda
December 18, 2018
Offline
Mynda, whatnks for your reply. I'm afraid that didn't work. I'm not sure how I can possibly send you an example book as it's entirely made up of workers' names, companies and workforce hours.
I suppose the best I can do is describe better.
Column A contains Month (June or July on every row)
Column B contains company names (Company 1 (three rows), Company 2 (8 rows), etc.)
Column C contains workforce hours (decimal numbers)
I've tried replacing all the zeros with nulls before grouping them, as you suggested. It returns the following error:
DataFormat.Error: We couldn't convert to Number.
Details:
-
Is there a chance that the instances in July where there are no hours yet at all return hours?
Thanks again.
July 16, 2010
Offline
Hi Adam,
I can't reproduce the issue. When you have confidential data you can create another file with dummy data or remove/replace the confidential information to anonymise it.
I have had to create a dummy file in order to try and follow the issue and reproduce it. You can see in the attached that the zero values are not causing a problem.
Mynda
November 8, 2013
Offline
Hi Adam,
Basically, the answer is already in the error message:
DataFormat.Error: We couldn't convert to Number. Details: -
That means you don't have zero's in some cells, you have text "-", so if you simply replace "-" with null, before replacing 0 with null, will work.
December 18, 2018
Offline
Thank you both for the help. Can't believe I missed what was staring right at me - Catalin, great suggestion. I can't convert "-" to null or zero, as it's a non-numeric value. From looking around on the internet, it seems I just have to let it change to Error and then replace Error with 0. This will still accomplish what I'm after.
Thanks again to both of you.
1 Guest(s)
