A Microsoft Excel forum. ExcelBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

Shannon diversity index formula



 
 
Thread Tools Display Modes
  #1  
Old April 8th 07, 07:50 PM posted to microsoft.public.excel.worksheet.functions
peter
external usenet poster
 
Posts: 4
Default Shannon diversity index formula

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

Ads
  #2  
Old April 8th 07, 08:22 PM posted to microsoft.public.excel.worksheet.functions
Barb Reinhardt
external usenet poster
 
Posts: 3,355
Default Shannon diversity index formula

Try this

=-SUMPRODUCT(--(C2:C20>0),(C2:C20/SUM(C2:C20)),(LOG(C2:C20/SUM(C2:C20))))



"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 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
>
>

  #3  
Old April 8th 07, 09:18 PM posted to microsoft.public.excel.worksheet.functions
peter
external usenet poster
 
Posts: 4
Default Shannon diversity index formula

This function still evaluates to a #NUM error.

On Apr 8, 2:22 pm, Barb Reinhardt
> wrote:
> Try this
>
> =-SUMPRODUCT(--(C2:C20>0),(C2:C20/SUM(C2:C20)),(LOG(C2:C20/SUM(C2:C20))))
>


  #4  
Old April 8th 07, 09:36 PM posted to microsoft.public.excel.worksheet.functions
Gary''s Student
external usenet poster
 
Posts: 11,059
Default Shannon diversity index formula

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

  #5  
Old April 8th 07, 10:19 PM posted to microsoft.public.excel.worksheet.functions
peter
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



  #6  
Old April 8th 07, 10:40 PM posted to microsoft.public.excel.worksheet.functions
Gary''s Student
external usenet poster
 
Posts: 11,059
Default Shannon diversity index formula

Sorry about the whales thing... I have nothing against whales, some of my
best friend are cetaceans.


In any event in C2 thru C20 enter:

=-(B2/SUM($B$2:$B$20))*IF(B2=0,0,LOG(B2/SUM(B$2:B$20)))
and in another cell:

=sum(C2:C20)
--
Gary''s Student - gsnu200714


"peter" wrote:

> 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

>
>
>

  #7  
Old April 8th 07, 11:00 PM posted to microsoft.public.excel.worksheet.functions
peter
external usenet poster
 
Posts: 4
Default Shannon diversity index formula

Thanks, but I'm really trying to consolidate the whole thing into a
single formula in a single cell.

On Apr 8, 4:40 pm, Gary''s Student
> wrote:
> Sorry about the whales thing... I have nothing against whales, some of my
> best friend are cetaceans.
>
> In any event in C2 thru C20 enter:
>
> =-(B2/SUM($B$2:$B$20))*IF(B2=0,0,LOG(B2/SUM(B$2:B$20)))
> and in another cell:
>
> =sum(C2:C20)
> --
> Gary''s Student - gsnu200714
>
> "peter" wrote:
> > 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



  #8  
Old April 8th 07, 11:40 PM posted to microsoft.public.excel.worksheet.functions
Barb Reinhardt
external usenet poster
 
Posts: 3,355
Default Shannon diversity index formula

Try this then:

=-SUMPRODUCT((C$2:C$20/SUM(C$2:C$20)),(IF(C$2:C$20>0,(LOG(C$2:C$20/SUM(C$2:C$20))))))
Activate with CTRL SHIFT ENTER
You should see {} around the formula when you are done.

"peter" wrote:

> This function still evaluates to a #NUM error.
>
> On Apr 8, 2:22 pm, Barb Reinhardt
> > wrote:
> > Try this
> >
> > =-SUMPRODUCT(--(C2:C20>0),(C2:C20/SUM(C2:C20)),(LOG(C2:C20/SUM(C2:C20))))
> >

>
>

  #9  
Old April 8th 07, 11:40 PM posted to microsoft.public.excel.worksheet.functions
Barb Reinhardt
external usenet poster
 
Posts: 3,355
Default Shannon diversity index formula

Try this then:

=-SUMPRODUCT((C$2:C$20/SUM(C$2:C$20)),(IF(C$2:C$20>0,(LOG(C$2:C$20/SUM(C$2:C$20))))))
Activate with CTRL SHIFT ENTER
You should see {} around the formula when you are done.

"peter" wrote:

> This function still evaluates to a #NUM error.
>
> On Apr 8, 2:22 pm, Barb Reinhardt
> > wrote:
> > Try this
> >
> > =-SUMPRODUCT(--(C2:C20>0),(C2:C20/SUM(C2:C20)),(LOG(C2:C20/SUM(C2:C20))))
> >

>
>

  #10  
Old April 9th 07, 12:13 AM posted to microsoft.public.excel.worksheet.functions
Harlan Grove[_2_]
external usenet poster
 
Posts: 1,230
Default Shannon diversity index formula

"peter" > wrote...
>This function still evaluates to a #NUM error.
>
> wrote:
>>=-SUMPRODUCT(--(C2:C20>0),(C2:C20/SUM(C2:C20)),
>>(LOG(C2:C20/SUM(C2:C20))))


Barb's formula doesn't work because --(C2:C20>0) doesn't exclude
calculating LOG(C2:C20/SUM(C2:C20)) for zero values in C2:C20. There's
a fix for that. I've also rearranged terms to take advantage of the
distributive law.

=-SUMPRODUCT(C2:C20,LOG(C2:C20+(C2:C20=0)))/SUM(C2:C20)
+LOG(SUM(C2:C20))

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
index, match formula Todd Excel Worksheet Functions 1 June 27th 06 08:43 PM
Index/ Match Formula LJoe Excel Worksheet Functions 2 June 22nd 06 06:19 PM
Index formula help Luke Excel Discussion (Misc queries) 1 December 14th 05 09:34 AM
Sum and Index formula? Luke Excel Discussion (Misc queries) 2 April 6th 05 12:58 PM
Min formula not returning value from Index ExcelMonkey Excel Worksheet Functions 3 January 29th 05 01:47 AM


All times are GMT +1. The time now is 05:12 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
Copyright 2004-2014 ExcelBanter.
The comments are property of their posters.