View original (French)

Excel: formula for point allocation

ornormmen Posted messages 5 Status Member -  
Denis.L Posted messages 438 Status Member -
Bonjour,

I am at work and I need to assign points to all employees in the company based on several criteria: age, number of children, seniority, etc.

All employee information from the company that employs me is recorded in an Excel file. I would like to use this file to create a formula to directly associate each employee with their number of points.

To illustrate, here is the result of the point attribution based on age:

16-20 years: 1 pt
20-25 years: 2 pts
25-30 years: 3 pts
30-35 years: 4 pts
35-40 years: 5 pts
40-45 years: 6 pts
45-50 years: 7 pts
50-55 years: 9 pts
55-57 years: 10 pts
57 and +: 6 pts

Thank you for guiding me through the creation of the formula, it would be nice of you, especially since I have plans this weekend and I don't want to be stuck here! :D
hugs friends 
Configuration: Windows 2000 Firefox 2.0.0.16

3 answers

  1.
    ishak74 Posted messages 37 Status Member 1
     
    Windows too old, you would need Microsoft Office 2008.
    0
  2. CyrilR
     
    Hello,

    You can use the VLOOKUP() function

    For example, in column A you have the age of your employees
    in column B you want to have the number of points

    You need to set up two other columns with the scale, not forgetting the value 0, that is:
    in column D:
    D1: 0
    D2: 16
    D3: 20
    D4: 25
    ...
    D11: 57

    in column E you should put the corresponding number of points:
    E1: 0
    E2: 1
    E3: 2
    E4: 3
    ...
    E11: 6

    Finally, in column B you put the following formula:
    B1: =VLOOKUP(A1;$D$1:$E$11;2;1)
    you copy this formula down for all your values in column A

    Is that clear?

    CyrilR
    0
    1. ornormmen Posted messages 5 Status Member
       
      clear as crystal.
      A big thank you from the whole team.
      0
      1. Denis.L Posted messages 438 Status Member 232 > ornormmen Posted messages 5 Status Member
         
        Hello,

        After 57 years, we are worth less and less, we lose points, it's not good to get old, what comforts me is that I don't have them yet, for now I have 9 points ;-))

        Best regards

        Denis
        0
    2. ornormmen Posted messages 5 Status Member
       
      How is it that the results are accurate up to D11, and then all the results are in the form N#A?
      0
      1. CyrilR > ornormmen Posted messages 5 Status Member
         
        Hello,

        There must be a typo in the formula, don't forget the $

        Also, in D11 we don't put formulas but the beginning of the ranges.

        If it doesn't work, reply by putting your email without the @ and I will write to you

        Thank you

        CyrilR
        0
      2. ornormmen Posted messages 5 Status Member > CyrilR
         
        ornormmen hotmail.com
        0
  3. ornormmen Posted messages 5 Status Member
     
    Clear as crystal.
    A big thank you.
    0