Ratio between Sum of Columns is not logical

Hi.
I have an excel table with three Columns. Each cell in column 3 is a multiplier of constant between 3.57 and 3.78 and the first column. The constants are in the second column.
i.e. col1 * col2= col 3
this is the table:

Col1 ____ Col2 ____ Col3
-2027.47 ____ 3.75 ____ -7594.77
-3095.82 ____ 3.74 ____ -11582.71
-528.97 ____ 3.78 ____ -2001.17
-559.33 ____ 3.78 ____ -2114.5
-579.34 ____ 3.70 ____ -2142.5
-1017.48 ____ 3.70 ____ -3762
-1025.66 ____ 3.70 ____ -3790
-2023.24 ____ 3.71 ____ -7498.37
641.74 ____ 3.69 ____ 2371
919.03 ____ 3.60 ____ 3305.79
-11042 ____ 3.73 ____ -41160.98
-5890.66 ____ 3.73 ____ -21999.76
-2275.86 ____ 3.74 ____ -8509.4
-8537.12 ____ 3.73 ____ -31883.45
-6639.5 ____ 3.73 ____ -24796.45
-10248.6 ____ 3.73 ____ -38221.73
-5000.81 ____ 3.71 ____ -18576.77
-5020.84 ____ 3.71 ____ -18651.18
-1617.82 ____ 3.73 ____ -6039.96
5796.17 ____ 3.59 ____ 20788.78
10483.96 ____ 3.59 ____ 37602.16
917.89 ____ 3.57 ____ 3272.66
7420.43 ____ 3.59 ____ 26614.4
11283.23 ____ 3.59 ____ 40468.85
-748.56 ____ 3.70 ____ -2767.6
-40.05 ____ 3.70 ____ -148
-10029.42 ____ 3.70____ -37107
4954.66 ____ 3.58 ____ 17735.51
2894.89 ____ 3.70 ____ 10707
2011.03 ____ 3.57 ____ 7187.1
4117.92 ____ 3.57 ____ 14716.46
8387.64 ____ 3.57 ____ 29978.81

-18119.96 ____ 4.17 ____ -75599.78

The last row contains the sum of Col1 and Col3. But when I calculate the ratio between the 2 sums it is 4.17!!!!
How can it be when the max ratio between each column cell is a max of 3.78??
Please help
Thanks in advance.

Will you please attach a SMALL sample Excel workbook (10-20 rows of data is usually enough)? Please don't attach a picture of one (no-one will want to re-type all your stuff before starting).

1. Make sure that your sample data are truly REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired solution is also shown (mock up the results manually).

3. Make sure that all confidential information is removed first!!

4. Try to avoid using merged cells. They cause lots of problems!

Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Originally Posted by sanram

This is because you have combination of positive and negative numbers here. To get rid of this you can use below formula for sum :
Col1 :

Formula:

Please Login or Register  to view this content.

Col3 :

Formula:

Please Login or Register  to view this content.

Then you will get the correct ratio.

Thank you for your answer.
These numbers are real and I need the negative sign to sum the numbers.
Why the negative value actually ruin the ratio?
I also attach the excel file as I was instructed...

Aha! Now it makes sense. calculate the sum/ratio of the positive and negative numbers separately...

Originally Posted by Glenn Kennedy

Aha! Now it makes sense. calculate the sum/ratio of the positive and negative numbers separately...

OK! you are right. Now it make sense.
we have 3.59 ratio for the positive values and 3.72 ratio for the negative ones. This make sense.

So we will expect a ratio between 3.59 and 3.72 to multiply the sum of column A with, but we still get 4.17...
and when it comes to money, it is more significant...

Yes. It's an artefact caused by your mix of +ve and -ve numbers.

i do not understand what you are saying/asking here:

"So we will expect a ratio between 3.59 and 3.72 to multiply column A with, but we still get 4.17..."

Are you happy enough with the overall result of the problem, that you can get an answer that makes sense?

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

I think I get it now after separating the numbers to pos and neg.
It now makes sense.
The sum is -18119.96 but the negative part of it (77948.55) which is bigger then the positive part is multiplied by a higher ratio (3.72) and the positive part
which is smaller (59828.59) is multiplied by a lower ratio (3.59).
We can not multiply -18119.96 as is with a ratio. We must separate and calculate the negative and positive parts with their ratio.

Thank you very much Glenn to help me understand this.
Thank you all.