More PDF Informaion is here:
This page provides information on the Screen Tips and Super Tips for various functions in the Data-Tool. Click the links below to navigate to the SuperTip information.
Reshape 2-Dim Data. Select 2-Dim data
📐 R2D: Reshape 2D to 1D
Use R2D to flatten a two-dimensional data table into a single column.
✔ Select a 2D range of data.
✔ The data is unrolled column-by-column into a one-dimensional list.
📽️ Refer to the help video for a walkthrough.
Reshape 1-Dim(a column) Data. Select 1-Dim data
📐 R1D: Reshape 1D to 2D
Use R1D to reshape a one-dimensional data column into a two-dimensional matrix.
✔ Select a single column of data.
✔ Enter the desired number of columns.
✔ The data will fill the matrix row by row.
⚠️ Ensure total number of data points is divisible by the number of columns you'll select in the opened window.
📽️ See the help video for a full example.
Reshape Based on Column Number. Select two columns data. Column numbers and data.
📐 BCN: Based on Column Number
Transform 1D data into 2D using column index values.
✔ Select two columns: [column number] and [data].
✔ Data will be arranged into rows based on the column index.
🎯 Example: [2, 3.5],[2,2.5],[1,1.8] → places row1:[1,1.8] and row2:[2,3.5,2.5].
📽️ Watch the help video for a full example.
Convert column daily data to monthly.
CDDM (Column Daily Data to Month):
Use this feature to convert daily data arranged in columns into a monthly format.
✔ Requires at least 365 rows per year.
✔ Each column is treated as a variable/station.
🚫 Avoid including headers or missing (NaN) values.
Convert row daily data to monthly.
RDDM (Row Daily Data to Month):
Transforms daily data organized in rows into monthly values.
✔ Requires at least 365 columns per year.
✔ Each row is treated as a year.
🚫 Exclude NaN values before conversion and don't select headers.
Convert row monthly data to seasonal.
RMS (Row Monthly to Seasonal):
Converts monthly data arranged in rows into seasonal data.
The data should have 12 columns.
🚫 Exclude NaN values before conversion and don't select headers.
✔ Offers three options:
• Normal seasons(Spring, Summer, Fall, and Winter)
• Custom seasons (e.g., user-defined months).
• 12 predefined overlapping 3-month seasons (e.g., DJF, JFM, etc.).
Convert column monthly data to seasonal.
CMS (Column Monthly to Seasonal):
Converts monthly data in columns into seasonal values.
🚫 Exclude NaN values before conversion and don't select headers.
✔ Supports multiple columns (stations/variables).
✔ Customize or apply 12 predefined seasons or Normal like RMS.
Compute monthly mean or SD across years.
RMAM (Row Monthly to Average Monthly):
For n×12 row-based monthly data (n years), this tool calculates:
🚫 Exclude NaN values before conversion and don't select headers.
✔ Monthly means or standard deviations across n years.
✔ Outputs a 12×1 vector (Jan–Dec) for each variable.
Convert column monthly data to yearly.
CMY (Column Monthly to Yearly):
Converts monthly data in columns to annual statistics.
🚫 Exclude NaN values before conversion and don't select headers.
✔ Computes yearly average or sum per column (station).
✔ Ideal for comparing long-term trends across multiple stations.
Generate a correlation matrix with visual plot.
Generate a correlation matrix between multiple variables (columns):
✔ You may include headers in your selection.
✔ A new sheet will display a visual correlation plot:
• Circle color and size represent correlation strength and direction.
• A colorbar is included for reference.
✔ A separate sheet will contain the numeric correlation table for detailed review.
Perfect for identifying relationships between variables at a glance.
Calculate the Root Mean Square Error (RMSE)
Compute the Root Mean Square Error (RMSE), a widely used metric to evaluate prediction accuracy:
✔ RMSE measures the average magnitude of the residuals (prediction errors).
✔ It is the square root of the average of squared differences between observed and predicted values.
✔ RMSE = sqrt(mean((X_obs - X_model)²))
Applications:
• Commonly used in climatology, forecasting, and regression analysis.
• Helps identify how concentrated data is around the line of best fit.
Key Notes:
• RMSE is always non-negative; 0 indicates a perfect fit.
• RMSE is sensitive to large errors due to squaring.
• Units of RMSE are the same as the variable being analyzed.
Use RMSE in conjunction with MAE to detect large, infrequent errors in your model.
Calculate the Nash–Sutcliffe Efficiency (NSE)
Evaluate model performance using the Nash–Sutcliffe Efficiency (NSE):
✔ NSE measures how well predicted values match observed values.
✔ It compares the variance of prediction errors to the variance of the observed data.
✔ NSE = 1 – [Σ(OBS - SIM)² / Σ(OBS - mean(OBS))²]
Interpretation:
• NSE = 1 → perfect prediction
• NSE = 0 → model is as good as the mean of observed values
• NSE < 0 → mean of observed values is a better predictor than the model
Applications:
• Widely used in hydrology for discharge, sediment, and water quality simulations.
• Also applicable to any variable where observed and simulated data are available.
Limitations:
• Squaring emphasizes large errors—may overrate model performance during high values.
• Less sensitive to bias in low flows or systematic under/over prediction.
Use this metric in the Data Tool to assess the consistency between observed and simulated data.
Calculate the Pearson correlation coefficient
Assess the linear relationship between two continuous variables using the Pearson correlation coefficient:
✔ Pearson's r measures strength and direction of linear association.
✔ It ranges from -1 (perfect negative correlation) to +1 (perfect positive correlation).
✔ A value of 0 indicates no linear relationship, though nonlinear relations may exist.
Formula:
r = Σ[(Oi - mean(O)) * (Pi - mean(P))] / sqrt(Σ(Oi - mean(O))² * Σ(Pi - mean(P))²)
Where O = observed values, P = predicted/model values.
Interpretation:
• ±0.90 to ±1.00 → Very strong correlation
• ±0.70 to ±0.89 → Strong correlation
• ±0.50 to ±0.69 → Moderate correlation
• ±0.30 to ±0.49 → Weak correlation
• < ±0.29 → Very weak to no correlation
Advantages:
• Independent of units—can compare values with different measurement scales.
• Symmetric: r(X,Y) = r(Y,X)
Note: Pearson’s r assumes a linear relationship. For non-linear trends, consider using Spearman or Kendall rank correlation instead.
Calculate Mean Bias Error (MBE)
Estimate the average bias in predictions using Mean Bias Error (MBE):
✔ MBE measures whether predictions systematically overestimate or underestimate observed values.
✔ It is calculated as:
MBE = (1/n) * Σ(Pi - Oi)
Where:
• Pi = predicted/model values
• Oi = observed values
• n = number of samples
Interpretation:
• MBE = 0 → No overall bias (ideal case)
• MBE > 0 → Model overestimates values on average
• MBE < 0 → Model underestimates values on average
Notes:
• MBE does not reflect the magnitude of individual errors.
• High positive and negative errors can cancel each other.
• Best used to assess directional bias, not total error size.
Tips:
⚠️ The observation values should be the first column.
• Complement MBE with RMSE or MAE for a more complete model evaluation.
Calculate Mean Absolute Error (MAE)
Evaluate the average size of prediction errors using Mean Absolute Error (MAE):
✔ MAE is the average of the absolute differences between observed and predicted values:
MAE = (1/n) * Σ|Oi - Pi|
Where:
• Oi = observed values
• Pi = predicted/model values
• n = number of samples
Interpretation:
• MAE = 0 → perfect prediction
• Lower values of MAE indicate better model accuracy
Characteristics:
• MAE gives equal weight to all individual errors
• Unlike RMSE, MAE is less sensitive to outliers
• MAE and RMSE are both in the same units as the data
• MAE does not indicate direction of bias — it treats over- and under-predictions equally
Use MAE alongside RMSE and MBE for a comprehensive model error analysis.
Calculate Index of Agreement (d)
Quantify model prediction accuracy with Willmott’s Index of Agreement (d):
✔ The Index of Agreement measures the degree of model prediction error:
• d ranges from 0 (no agreement) to 1 (perfect agreement)
• Formula: d = 1 - [Σ(Pi - Oi)² / Σ(|Pi - Ō| + |Oi - Ō|)²]
Where:
• Oi = observed values
• Pi = predicted/model values
• Ō = mean of observed values
Interpretation:
• d = 1 → perfect match between observed and predicted values
• d = 0 → model performs no better than a naïve mean-based estimate
Features:
• Captures both additive and proportional differences
• Sensitive to extreme values (due to squared errors)
• Commonly used to validate models in climate and hydrological studies
Use this metric along with RMSE, MAE, and NSE for robust model evaluation.
Calculate Deviation of Runoff Volume (Dv)
Measure the model's bias in total runoff prediction with Deviation of Runoff Volume (Dv):
✔ Dv (also called Percentage Bias) quantifies the deviation of total simulated runoff from observed:
• Formula: Dv = [Σ(Si - Oi) / ΣOi] × 100
Where:
• Si = simulated discharge values
• Oi = observed discharge values
• N = number of observations
Interpretation:
• Dv = 0 → perfect model (no deviation in total runoff)
• Positive Dv → model overestimates total runoff
• Negative Dv → model underestimates total runoff
Features:
• Simple and effective goodness-of-fit measure
• Recommended by WMO (1986) and used in hydrology
• Applicable for single years or multi-year averages without formula changes
⚠️ The observation values should be the first column.
Smaller Dv values indicate better model performance.
Use it alongside NSE, RMSE, MAE, and d for full model validation.
Calculate Deviation of Gain (DG) from daily means
Evaluate model performance with Deviation of Gain (DG) from daily means:
✔ DG assesses how well the model captures daily discharge variability across the year:
• Formula: DG = Σ[(Pi - Oi)²] / Σ[(Oi - Ōi)²]
Where:
• Pi = predicted daily discharge
• Oi = observed daily discharge
• Ōi = average of observed discharge on that day-of-year
Interpretation:
• DG = 0 → perfect model fit to observed daily means
• DG → 1 → poor agreement with observed daily patterns
Highlights:
• Robust for multi-year evaluations (unlike R²)
• Captures deviations from expected seasonal flow patterns
• Recommended by WMO (1986) for daily streamflow comparison
Use DG along with NSE, RMSE, and Dv for a complete hydrologic model evaluation.
Calculate Discharge Volume Percentage (DVP) deviation
Evaluate how the modeled discharge deviates from observed discharge volumes over time:
✔ Discharge Volume Percentage (DVP) expresses the % deviation of modeled runoff from observed values:
• DVP = 100 × Σ(Observed - Predicted) / Average Cumulative Observed Volume
Interpretation:
• Each year’s DVP shows whether the model over- or underestimates runoff volume
• Final DVP value summarizes total model bias across the entire period
Use Cases:
• Evaluate volume conservation in hydrological models
• Detect long-term overestimation or underestimation trends
Notes:
• DVP > 0 → model underestimates total runoff
• DVP < 0 → model overestimates runoff
• DVP ≈ 0 → balanced prediction over time
⚠️ The observation values should be the first column.
This tool processes discharge data in yearly (365-day) blocks. Make sure your input series aligns with full-year intervals.
Check 12 Statistical Distributions
Test how well your data fits 12 distributions: Bernoulli, Beta, Binomial, Cauchy, Exponential, Gamma, Gumbel, Lognormal, Normal, Poisson, Uniform, and Von Mises.
Three tests are applied:
• Chi-Square – Compares observed vs. expected frequencies.
• Kolmogorov-Smirnov – For continuous distributions, tests empirical vs. theoretical CDFs.
• Anderson-Darling – Focuses more on distribution tails.
Interpretation:
• p < 0.05 → Reject H₀: Data doesn’t match distribution.
• p ≥ 0.05 → Fail to reject H₀: No strong evidence against fit.
Note:
High p-values don’t prove the distribution is correct.
Small samples may reduce test sensitivity.
Fit Gumbel Distribution
Analyze your data using the Gumbel distribution (Extreme Value Type-I) via Accord.NET:
Usage:
• Select a single column of continuous data.
• Click 'Gumbel' to fit the distribution and view results.
Features:
• Automatic distribution fitting (location & scale parameters).
• Summary view of fitting quality and estimated parameters.
• Probability Calculator:
- P(X ≤ value)
- P(X ≥ value)
- P(value1 ≤ X ≤ value2)
- P(X = value)
• Inverse CDF:
- Enter a probability (e.g., 0.95) to get the corresponding lower and upper bounds.
Note: Suitable for modeling maxima (e.g., floods, extreme rainfall).
Fit Gamma Distribution
Fit and analyze your data using the Gamma distribution via Accord.NET:
Usage:
• Select a single column of continuous, non-negative data.
• Click 'Gamma' to fit the distribution and view results.
Features:
• Automatic parameter estimation (shape and scale).
• Summary of fitting results with distribution parameters.
• Probability Calculator:
- P(X ≤ value)
- P(X ≥ value)
- P(value1 ≤ X ≤ value2)
- P(X = value)
• Inverse CDF:
- Enter a probability (e.g., 0.95) to obtain the corresponding value range.
Note: Gamma distribution is useful for modeling skewed, continuous data such as rainfall, waiting times, etc.
Generate CDF Chart
Create CDF (Cumulative Distribution Function) charts using Accord.NET:
Steps:
• Select your target distribution.
• Highlight one or more columns of numeric data (headers welcome).
• Click the 'CDF' button.
Each column will be processed as a data series. The tool fits the selected distribution
and creates a new sheet with CDF chart(s), showing cumulative probabilities.
Note: CDF helps analyze the probability that a variable falls below a certain threshold.
Generate PDF Chart
Create PDF (Probability Density Function) charts using Accord.NET:
Steps:
• Select your target distribution.
• Highlight one or more columns of numeric data (headers welcome).
• Click the 'PDF' button.
Each column is treated as a separate data series. The selected distribution is fitted,
and a new sheet is generated with PDF chart(s) representing density values.
Note: PDF is useful for visualizing the distribution shape and likelihood of values.
Create Box Plot Chart
Quickly create a Box Plot chart using Excel 2016's built-in chart type.
Steps:
• Select one or more columns of numeric data (minimum 4 rows, headers wlcome).
• Click the 'BoxPlot' button.
The tool will:
• Insert a new worksheet with a Box Plot chart.
• Calculate and display 5-number summaries: Min, Q1, Median, Q3, Max.
Use Box Plots to visualize the distribution, spread, and potential outliers of your data.
Matrix Plot
Create a matrix of scatter plots and histograms to explore relationships between variables.
Steps:
• Select at least two columns and three rows of numeric data(headers welcome).
• Click the 'MatrixPlot' button.
The tool will:
• Generate scatter plots for each pair of variables with Pearson correlation coefficients.
• Display histograms on the diagonal for each variable.
• Add variable names as axis titles (if headers are detected).
Use this plot to quickly assess correlations and data distribution patterns across multiple variables.
Each column is a variable or series
📊 What is a Violin Plot?
A violin plot combines a box plot and kernel density estimation (KDE) to show both summary statistics and distribution shape.
✅ Input: Select a range (min 3 rows × 1 column); columns = variables.
✅ KDE: Gaussian KDE is applied using Scott’s Rule for bandwidth.
✅ Shape: Wider areas = higher data density; symmetry/skew reveals distribution.
✅ Stats: Mean and median are shown for each variable.\n
📈 Use violin plots to detect multimodality, skew, or compare distributions.
Box plots give summary stats, but violin plots reveal more detail.
Multivariate Linear Regression
Perform multivariate linear regression when you have multiple dependent variables and shared predictors.
Steps:
• Select a range with at least one dependent variable and one or more independent variables.
• Click the 'MLR' button.
The tool will:
• Fit a multivariate linear regression model: Y = X·B + ε.
• Display coefficient estimates and model diagnostics.
• Evaluate R² and p-values for each dependent variable.
Use this method when analyzing multiple outcomes influenced by common predictors.
Detect Anomalies
Identify values that deviate significantly from the mean using a visual anomaly chart.
Steps:
• Select a column of numeric data(headers welcome) or two columns (X and Y values).
• Click the 'Anomaly' button.
The tool will:
• Compute the mean and split values into 'above' or 'below' the mean.
• Plot these anomalies with smooth segments, highlighting crossings at zero.
• Use green for values above the mean and red for values below.
Note: If there are more than 256 segments, only the anomaly table will be generated (chart omitted).
Use this tool to visually assess variation or detect sudden shifts in data trends.
Mann-Kendall Trend Test
A non-parametric test to detect monotonic trends in a single variable over time.
Usage:
• Select one column (your variable of interest).
Features:
• Ideal for hydrometeorological data.
• Does not assume a specific distribution.
• Less reliable if the data has serial correlation.
Note: Tied groups are excluded from the calculation per [doi:10.1016/j.atmosres.2013.10.012].
Hypothesis Test for Regression Slope
Tests whether the slope of a linear regression line differs significantly from zero.
Usage:
• Select two columns: time (X) and your variable (Y).
Features:
• Assumes linearity and normally distributed residuals.
• Computes p-value using the Student’s t-distribution.
• Indicates presence of increasing or decreasing trends.
White’s Heteroskedasticity Test
Tests for changing variance (heteroskedasticity) in a linear regression model over time.
Usage:
• Select two columns: time (X) and variable (Y).
Features:
• Detects non-constant variance in residuals.
• Uses a Chi-square test on squared residuals from a quadratic auxiliary regression.
• Helps validate regression assumptions in trend analysis.
Register License
Enter your license key to activate the full version of the tool.
• Paste your license key and click Register.
• Registration enables full access to all features.
Note: Please close and reopen the Excel file after registration to apply changes.
Show Efficiency Process
If checked, the tool will create a new sheet showing detailed steps and formulas used to calculate efficiency metrics like RMSE, MAE, and R².
If unchecked, only the final efficiency values will be shown in a message box.
Use this option when you want to inspect or export the full calculation process.