December 18, 2018
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
December 18, 2018
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
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
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
December 18, 2018
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)