How to convert the data following into range on excel?-CodePudding

I have data like following:

If I want it to convert it into specific ranges like following:

How should I do it on excel?

CodePudding user response：

I suggest you to create the Dummy column (C column) which will contain the limits of your ranges. And then you will fill values into the D column thanks to this formula :

=C2 & "-" & C3

So the first value Total Count column can be filled with (in column E) :

=SUMIF(A$2:A$16;"<"& C3;B$2:B$16)-SUMIF(A$2:A$16;"<"&C2;B$2:B$16)

and then you can extend the formula through the other cells of the column.

This SUMIF() formula calculates values for range <50 minus values for range <0 (for Range 0-50). For Range 50-100, this formula will calculate the sum for range <100 minus sum for range <50, and so on.. The

Remark : Here the limits of the range are not considered for example if Valeur =50 the formula in Range column doen't count it. If you want to consider them in account, you can modify the values of the Dummy column to 0; 49.999999; or 50.000001 (left for you as an exercice :) ).

CodePudding user response：

I would advise you to create a helper column, using a formula like =ROUNDDOWN(A1,0).

Once you have this (let's say in column "C"), you can sum your values using the basic =SUMIF() function, something like:

=SUMIF(C$1:C$8,3,B$1:B$8)

(This will sum the values of the cells from B1 to B8, in case the corresponding values in column "C" are equal to 3, which means that the values in column "A" are between 150 and 199.99....