Month: February 2013

Diversity Calculator Excel – BPMSG

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

Calculations and results

Following data will be calculated and displayed:

div-templ-02

  • 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)

div-templ-03

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

div-templ-04

  • 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

Downloads

PLEASE READ before DOWNLOAD
The template is free, but I appreciate any donation helping me to maintain the website. Thank you!

BPMSG Diversity Calculator Excel Template Version 2020-07-05 (zip)

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.

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
  • Different scales added:
  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.

I appreciate any feedback! Please download the latest version from my AHP template download page.

;
Ob kRSAuZ X EpT