Compare variables and calculate

[ssraghunathan]

Would like to compare the values of 2 different data (s)


3 variables are ;


1. Salary of an employee

2. Grade Salary median

3. Department salary median


Based on the below crieteria, we would like to correct the employees salary.


a) Wherever employee salary is lower than above median (either on both or on any one ).

b) The average of higher median value (Both in case of both median value are high compared to employee salary, in other case the median which is higher (either Org Grade or Depart Grade Median))

c) The above correction will be considered only for those who is drawing less than 1.5 L salary earned


Illustration :


(All Values in Lakhs)

Salary Grade Median Functional Median Diff value 2 b considered for correction

3.50 2.50 3.00 -

3.50 4.00 3.50 0.50

3.50 3.50 3.75 0.25

3.50 4.00 4.50 0.75


I need to get those diff. value


Hope this forum will help me to crack the above problem.

 

[srinidhi]

Assuming A1 has the heading Salary, B1 Grade Median, C1 Functional Median, Difference in D1, & the New Salary in E1.

Data starts from A2.

In D2 enter this formula to get the difference b/w Grade Median & Functional Median IF(Grade Median<Functional Median, Functional Median- Grade Median, Grade Median-Functional Median), =IF(B2<C2,C2-B2,B2-C2). In E2 enter this formula to get the new salary. IF (Salary<1.5, Salary+Difference, Salary) =IF(A2<1.5,A2+D2,A2). Hope this is what you are looking for...

 

[ssraghunathan]

Srinidhi,


Thanks for your quick response.


To understand beter i mention the difference value in the colum D1. But i want the D1 column should be calculated automatically with the given set of rules.


I tried your given formula.. But it is not the one i'm looking for.


Thanks


Raghunathan

 

[srinidhi]

Ok, in D I have just given the difference b/w Grade Median & Functional Median,

if you want the new salary in D, then do a nested If in D, with the D & E formula given above.

If this does not solve the problem. Post your workbook.

 

[NARAYANK991]

Hi Raghunathan ,


I am slightly confused.


1. You would like to correct an employee's salary , if there is a discrepancy between the salary , and two medians viz. the organizational grade median , and the departmental median.


2. You would like this correction to be applied only for those employees whose salary is less than 1.5 lakhs ; is this per month or per year ? If it is per year , then the example you have given is confusing because all the salaries mentioned are above this limit.


3. What is the meaning of average of higher median value ? There are two median values mentioned ; either you can have the average of these two , or you can have the higher of these two ; how can you have the average of the higher one ?


A precise formulation of any problem , makes it easier to get a solution faster.


Narayan

 

[ssraghunathan]

Narayan,


sorry to respond you late... Let me brief you...


Point 1 : Salary Median at Grade / Level & Functional Level and would like compare an employees salary with the above median. If the current salary of an employee is less than the above median, i would like to correct.


Point No2. : The correction i would like to carry out for those who are drawing Rs.1.5 Lakhs per annum. Salary mentioned in the illustration is per annum.


Point no. 3: Since I'm comparing both Grade & Functional median, there could be a possibility of ;


a) Current Salary is less than both medians (Grade & Functional) : In such case, the average of the medians

b) Current salary is less than Grade or Functional Median : In such case, Median which is higher than those two.


I hope this clarifies.


Thanks


Raghunathan S

 

[NARAYANK991]

Hi Raghunathan ,


Can you not use an IF statement to calculate the correction ?


=IF(Salary > 1.5 , 0 , IF(AND(Salary < Grade Median, Salary < Functional Median) , AVERAGE(Grade Median , Functional Median) , MAX(Grade Median , Functional Median)))


Narayan

 

[ssraghunathan]

Thanks it is working fine...