View Single Post
  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
peter peter is offline
external usenet poster
 
Posts: 4
Default Shannon diversity index formula

Well, when you've got a datasheet with a list of 400 species on it,
and the polar bear doesn't happen to show up for the survey that day,
then the polar bear gets a zero. So what you're saying is what I'm
trying to do... exclude them without having to do lots of
manipulations.

And I'm quite happy to say that there are, in fact, whales in the
Amazon River basin. The Amazon River dolphin: http://www.acsonline.org/factpack/Boto.htm.
You're correct that the polar bears and kangaroos are elsewhere.

However, I'm not asking a theoretical question here, I'm asking for a
formula. So, back on track... anyone with anything helpful?

On Apr 8, 3:36 pm, Gary''s Student
wrote:
p should never be zero. Because pi is the fraction of individuals belonging
to the i-th species, by definition it can't be zero. Including a zero is
like including the number of whales in the Amazon river basin - there aren't
any. Just exclude zeros, polar bears, kangaroos, etc.
--
Gary''s Student - gsnu200714

"peter" wrote:
I'm trying to create a single formula that evaluates to the Shannon
diversity index (SDI) on a single array. An explanation of SDI can be
found onhttp://en.wikipedia.org/wiki/Diversity_index.


SDI = -E { p(i)*log(p(i)) }


In words, that's the negative sum over the (proportion times the log
of the proportion)


It's a simple thing to do if all the values in the array are 0, but
if one is zero, that screws up the LOG function, because LOG(0) is
undefined and so it returns an error.


This is my formula at present: =-SUMPRODUCT((B2:B20/SUM(B$2:B
$20)),LOG(B2:B20/SUM(B$2:B$20)))
And below is the sample data...


A B
1 Species TOTAL
2 Species 1 2
3 Species 2 2
4 Species 3 2
5 Species 4 2
6 Species 5 2
7 Species 6 2
8 Species 7 2
9 Species 8 2
10 Species 9 2
11 Species 10 2
12 Species 11 2
13 Species 12 2
14 Species 13 2
15 Species 14 2
16 Species 15 2
17 Species 16 2
18 Species 17 2
19 Species 18 2
20 Species 19 2


The TOTAL column is the sum of all the following columns. However,
there will always be a number of zeros in the TOTAL column, so the
function returns an error. I've tried using conditional tests as
explained onhttp://www.xldynamic.com/source/xld.SUMPRODUCT.html, but
I can't figure out how to get those to work inside the LOG function
which is the source of the error.


The SDI is also easy to calculate if I have a couple of columns in
which to put the proportions and the LOG(proportions), but that's not
what I'm looking for.


I have seen a few functions made by various folks around on the
internet, but 1) some have this same problem, and 2) some require an
add-in, neither of which are acceptable.


What say you all?
Peter