Scribd Logo
Upload

Welcome to Scribd!

  • 1K views

    How To Do AHP Analysis in Excel

    Uploaded by

    Jose D Salinas
    The document describes how to perform an Analytic Hierarchy Process (AHP) analysis in Excel to aid in multi-criteria decision making. It explains how to structure the decision making problem into a hierarchy, make pairwise comparisons between criteria, normalize the results to generate weights, and check for consistency in the judgments. The key steps involve developing pairwise comparison matrices for criteria and alternatives, normalizing the matrices to obtain weights, and calculating a consistency ratio to evaluate the judgments.

    Copyright:

    Attribution Non-Commercial (BY-NC)
    Download as PDF, TXT or read online from Scribd

    How To Do AHP Analysis in Excel

    Uploaded by

    Jose D Salinas
    1K views21 pages
    The document describes how to perform an Analytic Hierarchy Process (AHP) analysis in Excel to aid in multi-criteria decision making. It explains how to structure the decision making problem into a hierarchy, make pairwise comparisons between criteria, normalize the results to generate weights, and check for consistency in the judgments. The key steps involve developing pairwise comparison matrices for criteria and alternatives, normalizing the matrices to obtain weights, and calculating a consistency ratio to evaluate the judgments.

    Original Description:

    Presentation on how to do Hierarchic Analitic Process to Priorization

    Original Title

    How to Do AHP Analysis in Excel

    Copyright

    © Attribution Non-Commercial (BY-NC)

    Available Formats

    PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    The document describes how to perform an Analytic Hierarchy Process (AHP) analysis in Excel to aid in multi-criteria decision making. It explains how to structure the decision making problem into a hierarchy, make pairwise comparisons between criteria, normalize the results to generate weights, and check for consistency in the judgments. The key steps involve developing pairwise comparison matrices for criteria and alternatives, normalizing the matrices to obtain weights, and calculating a consistency ratio to evaluate the judgments.

    Copyright:

    Attribution Non-Commercial (BY-NC)
    Download as PDF, TXT or read online from Scribd
    Download as pdf or txt
    1K views21 pages

    How To Do AHP Analysis in Excel

    Uploaded by

    Jose D Salinas
    The document describes how to perform an Analytic Hierarchy Process (AHP) analysis in Excel to aid in multi-criteria decision making. It explains how to structure the decision making problem into a hierarchy, make pairwise comparisons between criteria, normalize the results to generate weights, and check for consistency in the judgments. The key steps involve developing pairwise comparison matrices for criteria and alternatives, normalizing the matrices to obtain weights, and calculating a consistency ratio to evaluate the judgments.

    Copyright:

    Attribution Non-Commercial (BY-NC)
    Download as PDF, TXT or read online from Scribd
    Download as pdf or txt
    of 21

    How to do AHP analysis in Excel

    Khwanruthai BUNRUAMKAEW (D3)


    Division of Spatial Information Science Graduate School of Life and Environmental Sciences University of Tsukuba ( March 1st, 2012)

    The Analytical Hierarchy Process - AHP


    AHP is one of the multiple criteria decision-making method that was originally developed by Prof. Thomas L. Saaty
    (1977).

    provides measures of judgement consistency derives priorities among criteria and alternatives simplifies preference ratings among decision criteria using pair wise comparisons

    Using AHP
    1. Decompose the decision-making problem into a hierarchy 2. Make pair wise comparisons and establish priorities among the elements in the hierarchy 3. Synthesise judgments (to obtain the set of overall or weights for achieving your goal) 4. Evaluate and check the consistency of judgements

    The basic procedure is as follows:


    1. Develop the ratings for each decision alternative for each criterion by developing a pair wise comparison matrix for each criterion normalizing the resulting matrix averaging the values in each row to get the corresponding rating calculating and checking the consistency ratio

    2. Develop the weights for the criteria by developing a pairwise comparison matrix for each criterion normalizing the resulting matrix averaging the values in each row to get the corresponding rating calculating and checking the consistency ratio

    3. Calculate the weighted average rating for each decision alternative. Choose the one with the highest score.
    5

    Structure the Hierarchy


    Decompose the decision-making problem into a hierarchy of criteria and alternatives.
    GOAL Goal
    Criteria Factor 1
    Subfactor 11

    1
    Subfactor 13

    Criteria Factor 2 2
    Criteria 13
    Subfactor 21

    Criteria Factor 3 3
    Subfactor 31

    Criteria 11

    Subfactor 12

    Criteria 12

    Criteria 21

    Subfactor 22

    Criteria 22

    Criteria 31

    Subfactor 32

    Criteria 32

    Subfactor 33

    Criteria 33

    Alt 1

    Alt 2

    Alt 3

    Level 1 is the goal of the analysis. Level 2 is multi-criteria that consist of several criterions, You can also add several other levels of sub-criteria. The last level is the alternative choices
    6

    The first step in the AHP procedure is to make pair wise comparisons between each criterion. The example scale for comparison (Saaty & Vargas, 1991).
    Scale 1 3 5 7 9 2,4,6,8 Degree of preference Equal importance Moderate importance of one factor over another Strong or essential importance Very strong importance Extreme importance Values for inverse comparison

    Results of the comparison (for each factors pair) were described in term of integer values from 1 (equal value) to 9 (extreme different) where higher number means the chosen factor is considered more important in greater degree than other factor being compared with.
    7

    Example
    Table: Primary questionnaire design: effective criteria and pair wise comparison
    Factor C1 C2 C3 9 9 9 8 8 8 7 7 7 6 6 6 5 5 5 4 4 4 Factor weighting score More importance than 3 3 3 2 2 2 Equal 1 1 1 2 2 2 Less importance than 3 3 3 4 4 4 5 5 5 6 6 6 7 7 7 8 8 8 9 9 9 C2 C3 C1 Factor

    Table: Pair wise comparison matrix which holds the preference values
    Criteria C1 C2 C3 C1 1 0.25 0.2 C2 4 1 2 C3 5 0.5 1
    =1/2 If the criteria in the column is preferred to the criteria in the row, then the inverse of the rating is given.

    This table shows a simple comparison matrix of order 3 where 3 criteria C1, C2 and C3 are compared against each other.
    8

    Consider the following example:


    Factor C1 C2 C3 C4 C5 9 9 9 9 8 8 8 8 7 7 7 7 6 6 6 6 5 5 5 5 4 4 4 4 Factor weighting score More importance than 3 3 3 3 2 2 2 2 Equal 1 1 1 1 2 2 2 2 Less importance than 3 3 3 3 4 4 4 4 5 5 5 5 6 6 6 6 7 7 7 7 8 8 8 8 9 9 9 9 C2 C3 C4 C5 Factor


    A B C D E F

    Start with the total cost criterion and generate the following data in a spreadsheet:
    1 2 3 4 5 6

    Factor C1 C2 C3 C4 C5

    C1 1.00

    C2 7.00 1.00

    C3 3.00 0.14 1.00

    C4 1.00 0.20 1.00 1.00

    C5 1.00 0.20 1.00 1.00 1.00

    How to fill up the upper triangular matrix is using the following rules: 1.If the judgment value is on the left side of 1, we put the actual judgment value. 2.If the judgment value is on the right side of 1, we put the reciprocal value.

    Making Comparison Matrix (How to make reciprocal matrix?) To fill the lower triangular matrix, we use the reciprocal values of the upper diagonal. If aij is the element of row i column j of the matrix, then the lower diagonal is filled using this formula =
    A B C D E F

    =1/C /C2 2 =1/D /D2 2 =1/E /E2 2 =1/F /F2 2

    1 2 3 4 5 6

    Factor C1 C2 C3 C4 C5

    C1 1.00 =1/7 =1/3 =1/1 =1/1

    C2 7.00 1.00 =1/0.14 =1/0.20 =1/0.20

    C3 3.00 0.14 1.00 =1/1 =1/1

    C4 1.00 0.20 1.00 1.00 =1/1

    C5 1.00 0.20 1.00 1.00 1.00

    =1/E /E3 3 =1/F /F3 3

    This slide shows how to analyze this paired comparisons

    10

    Step 1: Pair wise comparison


    The criteria in the row is being compared to the criteria in the column.
    A B
    C1 1.00 0.14 0.33 1.00 1.00 3.48

    C
    C2 7.00 1.00 7.00 5.00 5.00 25.00

    D
    C3 3.00 0.14 1.00 1.00 1.00 6.14

    E
    C4 1.00 0.20 1.00 1.00 1.00 4.20

    F
    C5 1.00 0.20 1.00 1.00 1.00 4.20

    Pair wise inputs

    1 2 3 4 5 6 7

    Factor C1 C2 C3 C4 C5 Total

    =Sum (B2 (B2:B6 :B6)


    Thus now we have complete comparison matrix The next step is to normalize the matrix. This is done by totaling the numbers in each column.

    11

    Step 2: Normalization
    This step is to normalize the matrix by totaling the numbers in each column. Each entry in the column is then divided by the column sum to yield its normalized score. The sum of each column is 1.
    A 6 7 8 9 10 11 12 13 14
    Factor C1 C2 C1 0.29 0.04 0.10 0.29 0.29 C2 0.28 0.04 0.28 0.20 0.20 C3 0.49 0.02 0.16 0.16 0.16 C4 0.24 0.05 0.24 0.24 0.24 C5 0.24 0.05 0.24 0.24 0.24 Total C5 Total

    B
    1.00 3.48

    C
    5.00 25.00

    D
    1.00 6.14

    E
    1.00 4.20

    F
    1.00 4.20

    =Sum (B10 (B10:F :F10 10) )


    =AVERAGE(G10/5)
    Average

    1.53 0.20 1.01 1.13 1.13

    0.31 0.04 0.20 0.23 0.23

    1=

    C3 C4 C5

    =1

    =(B6 =(B 6/B /B7 7)

    =(C6 =(C 6/C /C7 7)

    Highest average score

    Normalized inputs (priority vector)


    12

    Step 3: Consistency analysis


    Now, calculate the consistency ratio and check its value.

    The purpose for doing this is to make sure that the original preference ratings were consistent. There are 3 steps to arrive at the consistency ratio: 1.Calculate the consistency measure. 2.Calculate the consistency index (CI).
    CI = lmax - n . n-1

    3.Calculate the consistency ratio (CI/RI where RI is a random index).


    CR = CI / RI

    To calculate the consistency measure, we can take advantage of Excels matrix multiplication function =MMULT().
    13

    Consistency Ratio (CR)


    A 9 10 11 12 13 14 15 16
    Factor C1 C2 C3 C4 C5 Total

    B
    C1 0.29 0.04 0.10 0.29 0.29 1.00

    C
    C2 0.28 0.04 0.28 0.20 0.20 1.00

    D
    C3 0.49 0.02 0.16 0.16 0.16 1.00

    E
    C4 0.24 0.05 0.24 0.24 0.24 1.00

    F
    C5 0.24 0.05 0.24 0.24 0.24 1.00

    G
    Total

    Consistancy Average Measure

    1.53 0.20 1.01 1.13 1.13

    0.31 0.04 0.20 0.23 0.23 CI= RI= C.Ratio

    5.37 5.08 5.10 5.15 5.15 0.04 1.12 0.04

    =MMULT(B2:F2,H10:H14)/H10

    RI is provided by AHP (see slide 16)

    =MMULT(B3:F3,H10:H14)/H11

    =(AVERAGE(H10 =(AVERAGE(H 10:H :H14 14) )-5)/ )/4 4

    =I15 =I15/I /I16 16) )


    CR = CI / RI
    14

    Approximation of the Consistency Index


    1. Multiply each column of the pair wise comparison matrix by the corresponding weight. 2. Divide of sum of the row entries by the corresponding weight. 3. Compute the average of the values from step 2, denote it by lmax . 4. The approximate CI = lmax - n . n - 1
    =(AVERAGE(H10:H14)-5)/4
    15

    Consistency Ratio (CR)


    CR = CI / RI
    In practice, a CR of 0.1 or below is considered acceptable. Any higher value at any level indicate that the judgements warrant re-examination. Consistency Index (CI) reflects the consistency of ones judgement CI = Random Index (RI)
    the CI of a randomly-generated pair wise comparison matrix n RI 1 2 3 4 0.9 5 6 7 8 9 10

    lmax - n . n - 1

    0.00 0.00 0.58

    1.12 1.24 1.32 1.41 1.46 1.49

    Notes: n = order of matrix Random inconsistency indices for n = 10 (Saaty, 1980)

    16

    Summary
    With AHP, we can measure the degree of consistency; and if unacceptable, we can revise pair wise comparisons. If we are perfectly consistent, then the consistency measures will equal n and therefore, the CIs will be equal to zero and so will the consistency ratio. If this ratio is very large (Saaty suggests > 0.10), then we are not consistent enough and the best thing to do is go back and revise the comparisons. All of this work concludes the first step in the procedure. The next step is to use similar pair wise comparisons to determine the appropriate weights for each of the criteria. Now, continue for the other sub-criteria. You can easily do this by copying this sheet into other sheets and then simply changing the pair wise comparisons.
    17

    Remark
    By now you have learned several introductory methods on Multi-Criteria Decision Making (MCDM) from the advantage of Excels simple cross tabulation, using rank, and weighted score until AHP.

    Widely Used AHP


    Cost/Benefit Analysis Strategic planning R&D priority setting and selection Technology choice Investment priority Priority for developing tourism Evaluation of for new telecommunications services Other evaluation of alternatives
    18

    The mathematics of AHP


    (1) Normalization: Behind the scene

    19

    (2) Consistency analysis : Behind the scene

    Source: Haas, R. and Meixner, N.

    20

    References + Knowledges
    Saaty, T.L. (1980). The analytic hierarchy process. McGraw-Hill, New York. Saaty, T.L.,Vargas, L.G. (1991). Prediction, Projection and Forecasting. Kluwer Academic Publishers, Dordrecht, 251 pp. Haas, R. and Meixner, N. (n.d.). An Illustrated Guide to the Anlytic Hierarchy Process. Institute of Marketing & Innovation, University of Natural Resources and Applied Life Sciences, Vienna [Available online] http://www.boku.ac.at/mi/ Multi-Attribute Decision Analysis Approach: Qualitative Approach Analytic Hierarchy Process (AHP) Expert Choice Exercise www.satecs.com Some words on the Analytic Hierarchy Process (AHP) and the provided ArcGIS extension ext_ahp DECISION MODELING WITH MICROSOFT EXCEL: Multi-Objective Decision Making

    21

    You might also like