0

OK Excel gurus! For the life in me I cannot figure this out. I am going to try to paste the cells from a grid in below. Across the top under each month are the results of the formula applied to each month. On the right, I ad the results of each month and get $28,396.73. However, when I do the same formula on the totals of each row (i.e. the whole year) I get $28,428.36. That seems like a lot of variance for a rounding error! I would expect these two values to be exactly the same. Any ideas why not?

If you paste the below into Excel the formula in cell B2 (which is $829.34) is =((B4+B5)*((B6+B7)/B8))+B6+B7+B9+B10+B11 I would expect that the same formula applied to the yearly total would be equal to the sum of each monthly total but it is not.

Screenshot for clarity:

enter image description here

| Jan | Feb        | Mar        | April      | May        | June       | July       | August     | September  | October    | November   | December   |            |              |           |
|-----|------------|------------|------------|------------|------------|------------|------------|------------|------------|------------|------------|------------|--------------|-----------|
|     | 829.34     | 735.75     | 1,521.44   | 1,556.90   | 2,690.49   | 2,767.04   | 2,905.11   | 3,157.43   | 3,132.17   | 3,147.30   | 3,116.02   | 2,837.75   | 28,396.73    | 28,428.36 |
|     |            |            |            |            |            |            |            |            |            |            |            |            |              |           |
| A   | 9,587.89   | -2,711.10  | 18,637.86  | 26,467.32  | -6,264.93  | 4,433.74   | 26,573.66  | 18,278.19  | 19,856.50  | 20,837.80  | 19,929.65  | 19,929.65  | 175,556.23   |           |
| B   | 14,548.92  | 12,176.03  | 11,772.03  | 11,069.96  | 14,242.51  | 15,850.23  | 15,520.98  | 18,429.26  | 15,799.47  | 16,148.24  | 15,708.04  | 11,707.63  | 172,973.30   |           |
| C   | 570.1757   | 570.1757   | 570.1757   | 570.1757   | 643.1117   | 643.1117   | 643.1117   | 643.1117   | 643.1117   | 643.1117   | 643.1117   | 643.1117   | 7,425.60     |           |
| D   | 122.1278   | 122.1278   | 122.1278   | 122.1278   | 673.0078   | 673.0078   | 673.0078   | 673.0078   | 673.0078   | 673.0078   | 673.0078   | 673.0078   | 5,872.57     |           |
| E   | 150,833.34 | 150,833.34 | 153,858.56 | 150,833.33 | 180,259.90 | 198,041.65 | 203,028.58 | 233,580.13 | 258,458.31 | 247,471.73 | 283,542.33 | 283,113.12 | 2,493,854.32 |           |
| F   |            | 26.25      | 570.1757   | 570.1757   | 643.1117   | 643.1117   | 643.1117   | 673.5      | 673.5      | 673.5      | 673.5017   | 643.1117   | 6,433.05     |           |
| G   |            |            | 122.1278   | 122.1278   | 673.0078   | 673.0078   | 673.0078   | 902.54     | 902.54     | 902.54     | 902.5411   | 673.0078   | 6,546.45     |           |
| H   |            |            |            |            |            |            |            | 58.44      | 58.44      | 58.44      | 58.44      | 58.44      | 292.2        |           |  
1
  • 1
    For future reference, note that I formatted your textual data by using this Markdown Tables Generator and then applying the code-sample to the pasted data. Makes it much easier to copy/paste into a worksheet for testing purposes. Feb 3, 2020 at 13:24

1 Answer 1

0

First, change the value in cell C8 to 1, and you will see the two results now vary by two orders of magnitude. This illustrates that you are getting different results because you're implementing mathematically different formulas. It's just coincidence that with the numbers you're using, the two results happen to be close.

The formula in cell B2 (which equals $829.34) computes

B2 = (a+b) × (c+d)/e

The formula in cell N4 computes

∑an = a1+a2+a3+...+a12

Since multiplication and addition are not commutative, then your summation calculations are not equivalent:

∑Bn ≠ (∑an+∑bn) × (∑cn+∑dn) / ∑en

For future reference, you can use three backticks (```) to put your data in a space-separated code block which is much easier to look at. Even better, a link to the workbook in OneDrive or Google Sheets would have made your summation formulas immediately obvious. Anything you can do to make it easier for someone answering your question is likely to get your more responses.

 Jan Feb Mar April May June July August September October November December  
 829.34 735.75 1521.44 1556.90 2690.49 2767.04 2905.11 3157.43 3132.17 3147.30 3116.02 2837.75 28396.73258295 28428.36

A 9587.89 -2711.10 18637.86 26467.32 -6264.93 4433.74 26573.66 18278.19 19856.50 20837.80 19929.65 19929.65 175556.23000000 
B 14548.92 12176.03 11772.03 11069.96 14242.51 15850.23 15520.98 18429.26 15799.47 16148.24 15708.04 11707.63 172973.30000000 
C 570.18 570.18 570.18 570.18 643.11 643.11 643.11 643.11 643.11 643.11 643.11 643.11 7425.59640000 
D 122.13 122.13 122.13 122.13 673.01 673.01 673.01 673.01 673.01 673.01 673.01 673.01 5872.57360000 
E 150833.34 150833.34 153858.56 150833.33 180259.90 198041.65 203028.58 233580.13 258458.31 247471.73 283542.33 283113.12 2493854.32000000 
F 26.25  570.18 570.18 643.11 643.11 643.11 673.50 673.50 673.50 673.50 643.11 6433.04990000 
G   122.13 122.13 673.01 673.01 673.01 902.54 902.54 902.54 902.54 673.01 6546.44790000 
H        58.44 58.44 58.44 58.44 58.44 292.20000000 

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .