Home 
Search 
Today's Posts 
#1




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 addin, neither of which are acceptable. What say you all? Peter 
#2




Shannon diversity index formula
Try this
=SUMPRODUCT((C2:C200),(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 addin, neither of which are acceptable. What say you all? Peter 
#3




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:C200),(C2:C20/SUM(C2:C20)),(LOG(C2:C20/SUM(C2:C20)))) 
#4




Shannon diversity index formula
p should never be zero. Because pi is the fraction of individuals belonging
to the ith 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 addin, neither of which are acceptable. What say you all? Peter 
#5




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 ith 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 addin, neither of which are acceptable. What say you all? Peter 
#6




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 ith 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 addin, neither of which are acceptable. What say you all? Peter 
#7




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 ith 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 addin, neither of which are acceptable. What say you all? Peter 
#8




Shannon diversity index formula
Try this then:
=SUMPRODUCT((C$2:C$20/SUM(C$2:C$20)),(IF(C$2:C$200,(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:C200),(C2:C20/SUM(C2:C20)),(LOG(C2:C20/SUM(C2:C20)))) 
#9




Shannon diversity index formula
Try this then:
=SUMPRODUCT((C$2:C$20/SUM(C$2:C$20)),(IF(C$2:C$200,(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:C200),(C2:C20/SUM(C2:C20)),(LOG(C2:C20/SUM(C2:C20)))) 
#10




Shannon diversity index formula
"peter" wrote...
This function still evaluates to a #NUM error. wrote: =SUMPRODUCT((C2:C200),(C2:C20/SUM(C2:C20)), (LOG(C2:C20/SUM(C2:C20)))) Barb's formula doesn't work because (C2:C200) 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)) 
Reply 

Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
index, match formula  Excel Worksheet Functions  
Index/ Match Formula  Excel Worksheet Functions  
Index formula help  Excel Discussion (Misc queries)  
Sum and Index formula?  Excel Discussion (Misc queries)  
Min formula not returning value from Index  Excel Worksheet Functions 