Calculate AVE & CR in Excel

Transform AMOS output into transparent, audit-ready data. Use standardized loadings (λ) and error variances (θ) with precision.

Why Excel is ideal

Manual control See every step of the calculation clearly.
Customizable One template works for many constructs.
Visual feedback Conditional formatting for AVE < 0.50.
Reusable Perfect for thesis appendices.
1

Get Regression Weights (λ)

AMOS Regression Weights

Key Metrics

  • Estimate (λ) Standardized loading.
    Note: Variance explained is λ², not λ.
  • C.R. Critical Ratio (> |1.96| is significant).
  • P Value *** means p < .001
2

Get Variances (θ)

AMOS Variances

Critical Step

Locate rows e1, e2... These are your item error variances (θ). The estimate here represents the part of the item not explained by the factor.

3

Build & Calculate

Excel Template Structure Sheet1
Cell
A (Item)
B (Loading λ)
C (λ²)
D (Error θ)
1
Item Name
Loading
Squared
Error
2
EG1
0.85
=B2^2
0.23
3
EG2
0.91
=B3^2
0.15
...
TOTALS:
=SUM(B2:B7)
=SUM(C2:C7)
=SUM(D2:D7)

AVE (Average Variance Extracted)

Formula: Σλ² / (Σλ² + Σθ)

=SUMPRODUCT(B2:B7,B2:B7) / (SUMPRODUCT(B2:B7,B2:B7)+SUM(D2:D7))

CR (Composite Reliability)

Formula: (Σλ)² / ((Σλ)² + Σθ)

=(SUM(B2:B7)^2) / ((SUM(B2:B7)^2)+SUM(D2:D7))

Live Simulator

Interactive Demo

Edit the values below to see how AVE and CR change instantly.

Inputs

AVE Result Target: ≥ 0.50
0.00
CR Result Target: ≥ 0.70
0.00

Thresholds to Remember

  • AVE ≥ 0.50
    Convergent validity acceptable (explains >50% variance).
  • CR ≥ 0.70
    Internal consistency is acceptable.

Quality Checks

  • Map each item to the correct error term (e.g., EG1 ↔ e1).
  • Investigate loadings > 1 (potential Heywood cases).
  • Ensure you are using the standardized solution.