How to divide by summation of any column

[pban92]

(Wasted 7 hours and still counting with no luck so far...)

I have two columns A and B as follows,

21.405 20
21.561 31
21.719 158
21.877 484
22.036 1251
22.196 3060
22.357 7613
22.519 16065
22.681 33223
22.844 56712
23.009 96576
23.174 135266
23.339 171886
23.506 172951
23.674 155269
23.842 104310
24.012 59248
24.182 23244
24.353 8699
24.525 2018
24.698 418
24.871 64
25.046 5
26.046

I have to now create three more columns C, D and E, where,

C1 = A2-A1
D1 = B1/1048571 (The summation of column B is 1048571).
E1 = C1*D1

The following scripts do the job,

Please Login or Register  to view this content.

The problem is instead of putting 1048571 (the summation value of Column B) manually, how can I modify my scripts so that for any unknown number of rows, the summation would be automatically calculated (dividing by "sum(B1:B65000)" did not work) followed by the division operation.

Any suggestion would be highly appreciated.


Thanks!

[TMS]

Please Login or Register  to view this content.

Regards, TMS

[tom1977]

try this

Please Login or Register  to view this content.

[pban92]

Hi, many thanks for your kind help!

1. But if you notice you would see that my Columns A and B do not have equal numbers of row. But new columns C, D and E needs to have equal numbers of row as Column B.

2. Summation of Column E values should be 1.

Could you pls tell me how I can at the same time satisfy the two above points?

Kind regards!

[pban92]

tom1977, I regret why did not I simply post the prob here! Life saver! Many thanks!

[TMS]

@pban92: have you looked at /tested my solution in post #2

Regards, TMS