View Single Post
  #10   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Harlan Grove[_2_] Harlan Grove[_2_] is offline
external usenet poster
 
Posts: 1,231
Default 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))