P
peter
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 on http://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 on http://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
diversity index (SDI) on a single array. An explanation of SDI can be
found on http://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 on http://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