[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