Different results from Weighted Calculation

[randolphoralph7]

In Excel I have a East (Region) on Row 1. On Row 2 column B-G) I have AL, SC, and FL (state). Row 3 I have 2025 and 2026. Then row 4 I have Sales, row 5 2026 Net Revenue ∆, Row 6 I have Total Revenue PMPM. To calculate the Total Revenue PMPM for 2026 for AL, SC, and FL I am using the following calculation 2026 Total Revenue PMPM = 2025 Total Revenue PMPM +(2025 Total Revenue PMPM+2026 Net Revenue ∆).

Then in column H-I I have East total. To calculate Transactions I am summing Transactions for 2025 and 2026 for AL, SC, and FL.
For the East Total 2026 Net Revenue ∆ I am calculating using a weighted average =((AL 2026 Net Revenue ∆*AL 2026 Transactions)+(SC 2026 Net Revenue ∆*SC 2026 Transactions)+(FL 2026 Net Revenue ∆*FL 2026 Transactions))/(sum of 2026 AL, SC, and FL Transactions). For the East Total 2026 Net Revenue ∆ I get -5.3688%.
Then for East Total Revenue PMPM for 2025 and 2026 I am using a weighted average =((AL 2026 Total Revenue PMPM*AL 2026 Transactions)+(SC 2026 Total Revenue PMPM * SC 2026 Transactions)+(FL Total Revenue PMPM *FL 2026 Member Months))/(sum of 2026 AL, SC, and FL Transactions). For East total for 2025 Total Revenue PMPM I get $1774.43 and 2026 Total Revenue PMPM I get $1676.88.

If I take the East 2025 Total Revenue PMPM ($1774.43) which is a weighted average and use the 2026 Net Revenue ∆ (-5.3688%) which is a weighted average to calculate the East Total 2026 Total Revenue PMPM using the following formula =(East Total 2025 Total Revenue PMPM)+(East Total 2025 Total Revenue PMPM*East Total 2026 Net Revenue ∆) I get $1679.17 instead of $1676.88. I have added the workbook for review.

I am not sure why I am getting differing numbers and not sure which is correct.

[zbor]

Looks to me that weighted percentage per country and then SUM is not same as sum of weighted percentage per total.

Therefore approach in I6 seems correct.

You can test it for example putting first two % as 0, and last 100 and then see what result do you except