I have a task wherein I have to separate data in the Excel sheet and then calculate its median.

For example the data is recorded as follows.

Excellent Very Good Good Fair Poor Very Poor

3 3 3 2 0 0 0 0

This means that excellent has occurred 3 times, Very good 3 times and so on.

The convention followed is Excellent = 5, Very good = 4, Good = 3, Fair = 2, Poor = 1 and Very Poor = 0.

Hence The above data in the table indicates the following values

5,5,5,4,4,4,3,3,3,2,2

Hence the median of these values is 4. There are several rows in the worksheet and I need to calculate the median values for each row in the above manner.

Please let me know if there would be any Macro code to automate this process.

I am attaching the excel workbook that contains the data.

For example the data is recorded as follows.

Excellent Very Good Good Fair Poor Very Poor

3 3 3 2 0 0 0 0

This means that excellent has occurred 3 times, Very good 3 times and so on.

The convention followed is Excellent = 5, Very good = 4, Good = 3, Fair = 2, Poor = 1 and Very Poor = 0.

Hence The above data in the table indicates the following values

5,5,5,4,4,4,3,3,3,2,2

Hence the median of these values is 4. There are several rows in the worksheet and I need to calculate the median values for each row in the above manner.

Please let me know if there would be any Macro code to automate this process.

I am attaching the excel workbook that contains the data.

## Comment