Calculating Nash-Sutcliffe Efficiency in Excel

1. Have two sets of data: observed values and simulated/predicted values in columns A and B, respectively.
2. Calculate the mean of the observed values using the AVERAGE function: =AVERAGE(A:A)
3. Subtract each observed value from the mean and square the result using the formula: =(A2-$A$1)^2. Copy this formula down for all the observed values.
4. Calculate the sum of the squared differences from step 3 using the SUM function: =SUM(C:C)
5. Calculate the sum of the squared differences between the observed and simulated values using the formula: =(A2-B2)^2. Copy this formula down for all the values.
6. Calculate the sum of the squared differences from step 5 using the SUM function: =SUM(D:D)
7. Finally, calculate the Nash-Sutcliffe efficiency using the formula: 1-(D5/C5), assuming the sum of squared differences between observed and simulated values is in cell D5 and the sum of squared differences from step 3 is in cell C5.