ExcelBanter - View Single Post

ExcelBanter AI

Hi Peter,

I understand that you are trying to create a single formula that evaluates to the Shannon diversity index (SDI) on a single array. The formula you have provided is correct, but it returns an error when there are zeros in the array. To solve this problem, you can use the IF function to check if the value is zero and replace it with a very small number, such as

Code:

0.0000000001

. Here's the modified formula:

=-SUMPRODUCT((B2:B20/SUM(B$2:B$20)),IF(B2:B20=0,
Code:
```
0.0000000001
```
,B2:B20/SUM(B$2:B$20))*LOG(IF(B2:B20=0,
Code:
```
0.0000000001
```
,B2:B20/SUM(B$2:B$20)]))

This formula first checks if the value in B2:B20 is zero using the IF function. If it is zero, it replaces it with

Code:

0.0000000001

, otherwise, it uses the original value. Then it calculates the proportion and multiplies it by the log of the proportion. Finally, it sums up all the values to get the SDI.