j_erussell01 wrote:
I'm analyzing a large data set (80 columns, 800 rows) and some of my values
are 0, but I need to find out how to incoporate/exclude them, because I need
to do a Log with a base 2 on each value in all 80 categories. I'm trying to
calculate the Shannon-Wiener Index on each column (if that helps).

I know nothing about the Shannon-Wiener index beyond what I just read
on a wikipedia.org web page. But my understanding is that it is
computed using LN(), not LOG(...,2).

In either case, your problem is the same. That is, I think you want to
compute -SUM(p[i]*LN(p[i]), i=1,...,S), and you need to avoid the error
caused by evaluating LN(0)

One solution is the following array formula (enter using
ctrl-shift-Enter):

=-sumproduct(A1:CB800, if(A1:CB800<=0, 0, ln(A1:CB800)))

Someone else might be able to provide a SUMPRODUCT() formula that does
not require an array formula. But the IF() function seems necessary to
avoid evaluating LN(0).