May 24 2023 11:04 AM
Hello Experts,
I have a data set, in which unique items have been sold at different discount percentages.
I want to know in which bracket of discount how many items are there for offering. So when I group by discount and count items, I am facing an error where the grouping is being counted again.
For example, If am grouping at the intervals of 10 then my data is reflecting like this
0-10% - 50 items
10- 20% - 80 items
20-20% - 92 items
whereas it should be
0-9% - X items
10-19% - Y items
20-19% - Z items
Decimals are not in percentages in my worksheet.
PLease help me out here.
Thanks
May 24 2023 12:51 PM
Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar? Alternatively, you can attach it to a private message to me. Thanks in advance.
May 24 2023 01:11 PM
I guess we speak about grouping in PivotTable. Such grouping is by design and it's not overlapping of ranges. Just
0 - 10% means ( >= 0 and <= 10% )
10% - 20% means ( > 10% and <= 20% )
20% - 30% means ( > 20% and <= 30% )
etc
First bin includes first number of the range, next bins take all what is greater of first number (and not more than the second one)
May 24 2023 10:33 PM
@Hans Vogelaar PFA
May 24 2023 10:39 PM
May 25 2023 01:12 AM
As @Sergei Baklan remarked, there is no overlap in the counts: the total 9297 is correct.
Excel has to write 0.1-0.2 and 0.2-0.3 because these are numbers with decimals, not whole numbers.
I don't think it is possible to make Excel display the groups as percentages 10%-20% etc.
You'd have to create your own list with COUNTIFS formulas for that.