What function to use

Asked

Viewed 54 times

-1

As a beginner in Excel, I’m having difficulties to use a function that I believe is the solution to what I want, the function (SE).

The thing is, I have a sum of values that start at 54 and go to 317. I want to divide these values into 8 (eight) classes that remained like this: from 54 to 86 from 87 to 119 from 120 to 152 from 153 to 185 from 186 to 218 from 219 to 251 from 252 to 284 and from 285 to 317.

To these classes I gave "weights", type weight 1 up to weight 8. I would like Excel to identify which class belongs to classify in the weight I determine, example 54 to 86 equal to weight 1.

excel function

2 answers

0

If your value column is in A:A, starting with A2 fill in column B with:

B1 = 1 and other cells, starting from B2 =SE(MOD((A2-53); 33)=0;B1+1;B1)

0

You can create a table with the values and use a PROCV to search for the value within the range and its corresponding

   A     B     C     D     E     F     G     H     I
 -----|-----|-----|-----|-----|-----|-----|-----|-----|
 1    |     |     |     | MIN | MAX | VAL |     |     |
 -----|-----|-----|-----|-----|-----|-----|-----|-----|
 2    |  70 |     |     |  54 |  86 |  1  |     |     |
 -----|-----|-----|-----|-----|-----|-----|-----|-----|
 3    | FRM |     |     |  87 | 119 |  2  |     |     |
 -----|-----|-----|-----|-----|-----|-----|-----|-----|
 4    |     |     |     | 120 | 152 |  3  |     |     |
 -----|-----|-----|-----|-----|-----|-----|-----|-----|

The formula that goes in "FRM" is:

FRM =PROCV(A2;E2:G3;4;TRUE)

A2 = It is the cell where the value is that Voce wants to locate the weight

E2:G4 = Is the range where the values are

3 = It is the Indice where is the weight you are looking for

TRUE = Means that Voce wants to search for exact and non-approximate values

  • These values ranging from 54 to 317 come from the results of the mega sena, that is, the sum of each ten drawn. The smallest sum (54) is the sum of a contest where the sum of the ten gave this value, as well as the value 317.

  • These sums are next to each drawn contest. I’ve classified them that way, into classes, to see which ones come out the most and the frequency at which they occur. I could not understand how to apply the function you spoke. (procv)

  • Right, but which part is your doubt?

Browser other questions tagged excel function

You are not signed in. Login or sign up in order to post.