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