## 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.

## Applications

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)

Limitations:

• 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

 PLEASE READ before DOWNLOAD 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!

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
• beta diversity calculation

## Updated AHP Excel Template Version 08.02.13

An updated version of my AHP Excel template for multiple inputs is now available as version 08.02.13. Beside the extension from 8 to 10 criteria and from 7 to 20 participants some new features have been added. In the past it was sometimes difficult for participants to achieve a low consistency ratio. Now inconsistent comparisons in the input sheet will be highlighted, if the required consistency level is exceeded.  The level of consistency needed (“alpha” in the summary sheet) can also be changed from 0.1 (standard rule of thumb from Saaty) to higher values, for example 0.15 or 0.2. In addition another scale for the judgment can be chosen. For my projects I made good experience with the balanced scale.

A new feature is the consensus index. If you have more than 1 participant and do the group aggregation (select participant “0”), the consensus index is an indicator, how homogenous the judgment within the group was done. Zero percent means no consensus, all participants put their preference on different criteria;  100% means full consensus. Here the changes in detail:

## Summary sheet

• Number of criteria increased from 8 to 10
• Number of participants increased from 7 to 20
1. Linear standard scale
2. Log
3. Sqrt
4. InvLin
5. Balanced
6. Power
7. Geom.
• Alpha – allows to adjust consistency threshold (0.1 default)
• Consensus indicator for group aggregation added
• Geometric Consistency Index CGI added

## Input sheets

• Consistency ratio is calculated on each input sheet.
• Priorities are calculated and shown based on RGMM (row geometric mean method)
• Top three inconsistent pairwise comparisons highlighted (if CR>alpha)

## Known Issues

Thanks to feedback from Rick, sometimes there seems to be a problem with the correct display of weights beside the criteria in the summary sheet. If you face this problem, unprotect sheet summary. Select weigths (O18:O27). Click “conditional formating”, “clear rules”,”clear rules from selected cells”. Then the values will be displayed correctly, and you can format them in the way you want. It is a strange effect; it only appears on one of my PCs, on the other it works fine. I uploaded a modified version, but not sure whether it works for everyone.