AHP and the Magical Number Seven Plus or Minus Two

In the analytic hierarchy process you define a set of criteria and sub-criteria arranged in a hierarchy, to do pairwise comparisons and find the weights of criteria or decision alternatives. In my AHP excel template the number of criteria is limited to ten, in my AHP online software to 15. Still sometimes I am asked to extend and allow for more criteria.

Why the number of criteria should not exceed the magical number seven plus or minus two?

There are three reasons not to exceed the number of 9 criteria in any AHP project. Two of them are quite clear and published in the literature:

  • The first has to do with the human limits on our capacity for processing information, and was published by George A Miller, as well as in the context of AHP by Saaty and Ozdemir.
  • The second is related to the first one. The number of pairwise comparisons increases with the number of criteria, it is (n2n)/2. For example, 9 criteria require 36 comparisons. For a  high number of comparisons easily logical inconsistencies occur, and the consistency ratio CR exceeds values of 10% to 20%, making the basic assumption of near consistent matrices invalid and the AHP results questionable.

See also my post here.

The third reason is not so obvious and not so well known. It is based on the limited 1 to 9 AHP ratio scale for the judgment. The maximum preference you can give to one criterion is 9, i.e. this criterion is 9 times more important than all other criteria. Assume, you have only two criteria, then – if you fully prefer one over the other – the preferred one will result in a weight of 90%, the other gets a weight of 10%. The weights depend on the number of criteria, the maximum weight or maximum priority wmax is always

wmax = M/(n + M – 1)

with M = 9, the maximum of the AHP scale and n the number of criteria. The below diagram shows wmax as a function of the number of criteria.


Clearly you can see that for 10 criteria the maximum possible weight reduces to 50%, or in other words, although you give full preference to one criterion, it only gets a weight of 50%! For more than ten criteria the weight will be below 50%. This is the reason, why the number of criteria should never exceed the magical number seven plus or minus two.

Incoming search terms:

  • ahp calculation example
  • a number of criteria or a number of criterion
  • ahp weight
Share on Facebook

BPMSG Diversity Calculator – Excel

The diversity calculator is an excel template that allows you to calculate alpha-, beta- and gamma diversity for a set samples (input data), and to analyze similarities between the samples based on partitioning diversity in alpha and beta diversity.

The template works under Windows OS and Excel 2010 (xlsx extension). No macros or links to external workbooks are necessary. The workbook consists of an input worksheet for a set of data samples, a calculation worksheet, where all necessary calculations are done, and a result worksheet “beta” displaying the results.


The template may be used to partiton data distributions into alpha and beta diversity, it can be applied in many areas, for example

  • Bio diversity – local (alpha) and regional (beta) diversity
  • AHP group consensus – identify sub-goups of decision makers with similar priorities
  • Marketing – cluster analysis of similarities in markets
  • Business diversification over time periods
  • and many more.

Let me know your application! If you just need to calculate a set of diversity indices, you can use my online diversity calculator here.

Calculations and results

Following data will be calculated and displayed:


  • Shannon Entropy H (natural logarithm) alpha-, beta- and gamma, and corresponding Hill numbers (true diversity of order one) for all samples
  • Homogeneity measure
    1. Mac Arthur homogeneity indicator M
    2. Relative homogeneity S
    3. AHP group consensus S* (for AHP priority distributions)


  • Table 1: Shannon alpha-entropy, Equitability, Simpson Dominance, Gini-Simpson index and Hill numbers for each data sample


  • Table 2: Top 24 pairs of most similar samples
  • Page 2: Matrix of pairs of data samples
  • Diagram 1: Gini-Simpson index and Shannon Equitability
  • Diagram 2: Average proportional distribution for all classes/categories
  • Diagram 3: Proportional distribution sorted from largest to smallest proportion (relative abundance)


  • Maximum number of classes/categories: 20
  • Maximum number of samples: 24

Description of the template:  BPMSG-Diversity-Calc-v14-09-08.pdf

Other posts explaining the concept of diversity


The template is free. So I would really appreciate, if you could – at least – feedback or write me about your application of AHP, and the use of the template. Thank you!

Download BPMSG Diversity Calculator Version 14.02.13 (zip) download

The work is licensed under the Creative Commons Attribution-Noncommercial 3.0 Singapore License. For terms of use please see our user agreement and privacy policy.

As this version is the first release, please feedback any bugs or problems you might encounter.

Incoming search terms:

  • shannon diversity index calculator
  • Alpha Diversity Calculator
  • Calculation of Alpha beta and gamma diversity
  • how to input data to be used in r to calculate diversity indices
  • algal diversity calculation soft ware free download
  • beta diversity calculation
Share on Facebook

AHP template – numerical accuracy

Thanks to feedback from Mihail, here a few words about the numerical accuracy when using the AHP excel template.

AHP requires the calculation of the principal Eigenvalue, the weights are derived from the Eigenvector.  In my calculations I use the power method.  It is an iterative method, and  only one of several techniques that can be used to approximate the eigenvalues of a matrix.

Update 11.12.12

The whole calculation is shown in work sheet ’10×10′. I use 12 iterations; at the end of the sheet I do a check (the reverse calculation), using the Eigenvalue equation: (Aλ IX = 0,  with A the AHP matrix; λ the principal Eigenvalue, and X the estimated Eigenvector. The resulting check value in cell B33 shows the sum of all matrix element of the Eigenvalue equation using the iterated Eigenvector and Eigenvalue. Ideally it should be zero.

Update 9.5.14

From version 2014-05-09 onward the template shows the convergence of the power method, when calculating the eigenvalue. In the summary sheet a threshold (squared Euclidean distance d2) can be set, to show how many iterations it takes, until the change of the approximated eigenvector is below the given threshold. By default the value is set to Thresh: 1E-07. As the number of iterations in the template is fixed to 12, care should be taken if the value reaches 12.


Here a practical example comparing the results from the power method, as now implemented in my template, with  an example (7 criteria) given by Saaty in Int. J. Services Sciences, Vol. 1, No. 1, 2008 (p 86, table 2). The AHP matrix is:

1 9 5 2 1 1  1/2
 1/9 1  1/3  1/9  1/9  1/9  1/9
 1/5 3 1  1/3  1/4  1/3  1/9
 1/2 9 3 1  1/2 1  1/3
1 9 4 2 1 2  1/2
1 9 3 1  1/2 1  1/3
2 9 9 3 2 3 1

The result according Saaty is
(0.177,  0.019, 0.042, 0.116, 0.190, 0.129, 0.327) with consistency ratio of 0,022

The result from my AHP Excel template is
(0.1775, 0.0191, 0.0418, 0.1164, 0.1896, 0.1288, 0.3268) with CR 0f 0.022
exactly the same. The check value in sheet ‘8×8 is 4E-12.

You can find more examples here.

Please download the latest version with the improved accuracy from here.




Share on Facebook

New AHP Excel template with multiple inputs

The AHP template works under Windows OS and Excel version MS Excel 2013. The workbook consists of 20 input worksheets for pair-wise comparisons, a sheet for the consolidation of all judgments, a summary sheet to display the result, a sheet with reference tables (random index, limits for geometric consistency index GCI, judgment scales) and a sheet for solving the eigenvalue problem when using the eigenvector method (EVM). Information about the latest version 2016.05.04.

Alternative for complex AHP projects: AHP free online software .

Excel Template

  • Within the input worksheets (questionnaires) priorities are calculated using the row geometric mean method (RGMM).
  • Two consistency indices (the consistency ratio CR and the geometric consistency index GCI) are calculated. The level of consistency needed (α) is implemented as a variable input field in the summary sheet, and can be set between zero and one.
  • If CR exceeds α, the top 3 inconsistent pair-wise comparisons on the input sheets are highlighted, to allow the participants an adjustment of their judgments. The judgment resulting in lower inconsistency is proposed.
  • Final priorities are shown in a summary sheet; their calculation is based on the eigen vector method (EVM). For the solution of the eigenvalue problem the power method algorithm is applied with a fixed number of 12 iterations.
  • Different judgment scales are implemented.
  • Either individual participants, or an aggregation of individual judgments (AIJ) based on the weighted geometric mean of all participants’ judgments can be selected.


  • The template does not include the hierarchy of the decision problem and the final aggregation of weights, i.e. it is only suitable for finding the weights in each category or sub-category. For the definition of a hierarchy and evaluation of alternatives see here.
  • Sensitivity analysis of the final result is not included.

How to use the template?

A detailed description is given in the pdf document here: AHPcalc-v2017-03-29.pdf


Please always make a reference to the author and website.


Download AHP Excel Template Version 2016.05.04 (zip) download

For terms of use please see our user agreement and privacy policy.

Please consider a donation, it will help me to maintain the website and program:

Your Donation (1 Singapore Dollar is approx. 0.7 USD):

An explanation of AHP (Analytic Hierarchy Process) is given in my video here.

Your comments are always welcome!

Incoming search terms:

  • ahp model excel
  • ahp calculator excel
  • ahp questionnaire example
  • ahp questionnaire template
  • ahp questionnaire format
  • ahp questionnaire sample
Share on Facebook

Combined Performance Index – Example in Excel

Here you find a simple example, how to realize the calculation of a combined performance index in an Excel sheet.

Download the excel sheet:  Comb_Index.xlsx

Incoming search terms:

  • how to calculate composite index in excel
Share on Facebook

Making your wordpress blog more secure

You find the individual steps easily in a web search. Most critical is the change of SQL table prefixes. I did it manually using phpMyAdmin. When finished, no link was working any longer. Reason: permalinks were set to %postname%. After switching first to default and then back to %postname% everything was fine.

I also struggled with the password protection. Inportant: add the line ErrorDocument 401 default, otherwise you might always get the “page not found” message.

Steps to do:

  • Move config.php file one directory above wordpress installation directory
  • Remove admin user
    (create new user with full rights and delete old user)
  • Protect wp-admin and wp-includes directories
    (read for owner and group only = 751)
  • Hide wordpress version and login error messages
    Add in functions.php (in your ChildTheme directory):
    <?php remove_action(‘wp_head’, ‘wp_generator’);
    add_filter(‘login_errors’,create_function(‘$a’,”return null;”)); ?>
  • Delete readme.html file from web root directory
  • Password protect wp-admin directory (I used CPanel to create .htaccess).
    Modify .htaccess file in wp-admin – add:
    ErrorDocument 401 default
  • Modify .htaccess file in web root:
    <IfModule mod_rewrite.c>
    RewriteEngine On
    RewriteBase /
    RewriteRule ^index\.php$ – [L]
    RewriteCond %{REQUEST_FILENAME} !-f
    RewriteCond %{REQUEST_FILENAME} !-d
    RewriteRule . /index.php [L]
  • SQL: change prefixes of database tables from
    “wp_”  to “new_prefix_”
Share on Facebook

AHP – Does Mobile Internet Enhance Driving Experience?

Thesis of Taiwan Car Users’ Attitudes

Daniel Werner, National Cheng Kung University Taiwan, Institute of International Management, Master Thesis Sep 2011

This thesis uses the AHP Excel template … from K Goepel, a German Ph.D in Singapore and specialist of MCDM methods. Training videos and this excel template are freely available on his website

Nice to see that my work is useful and applied in practice. I would appreciate to hear about similar cases from my visitors. Please leave a comment here or contact me.

Share on Facebook

Value-driven Leadership – Introduction

This short video gives an introduction to value-driven leadership.

How do you think about this guy?

More… about value-driven leadership

Share on Facebook

Analytic Hierarchy Process – new podcast episode

AHP stands for Analytic Hierarchy Process. It is multi-criteria decision making method, originally developed by Prof. Thomas L. Saaty.

The AHP video, explaining the method, is now available as a new episode in my podcast.

The latest version of my AHP Excel template is available for download from here.

Share on Facebook

Setting up a small Business

When you plan to setup your own business, many questions will come up.

  • How to do the planning?
  • How much can I charge to the customer?
  • How many orders do I need?
  • What are the risks involved?

In this presentation we will answer these questions using the key ratios we have introduced before. With a simple flow diagram the planning process is explained step by step.

You might subscribe to the podcast with this latest video episode here

Share on Facebook