Would greatly appreciate it if someone could provide a prorating formula. What I want is to prorate the amounts in D2 over the values in C2.
There are multiple transactions in column A and for each transaction, there can be anywhere from 1 to several lines (column B), with each
line having value a in C. The prorate amts are always on the first line of each transaction. Lines (B) are not necessarily unique within a given transaction
and I'm not even sure if they play a part in the equation. I'm looking for results as per column E.
<colgroup><col style="mso-width-source:userset;mso-width-alt:2450;width:50pt" width="67"> <col style="mso-width-source:userset;mso-width-alt:4388;width:90pt" width="120"> <col style="mso-width-source:userset;mso-width-alt:4352;width:89pt" width="119"> <col style="mso-width-source:userset;mso-width-alt:2852;width:59pt" width="78"> <col style="mso-width-source:userset;mso-width-alt:3657;width:75pt" width="100"> <col style="mso-width-source:userset;mso-width-alt:4278;width:88pt" width="117"> </colgroup><tbody>
[TD="class: xl81, width: 67, align: center"]
[/TD]
[TD="class: xl82, width: 120, align: center"]A[/TD]
[TD="class: xl82, width: 119, align: center"]B[/TD]
[TD="class: xl82, width: 78, align: center"]C[/TD]
[TD="class: xl82, width: 100, align: center"]D[/TD]
[TD="class: xl82, width: 117, align: center"]E
[/TD]
[TD="class: xl81, align: right"]1[/TD]
[TD="class: xl83, width: 120, align: center"]Transaction #
[/TD]
[TD="class: xl84, width: 119, align: center"]Transaction Line[/TD]
[TD="class: xl85, width: 78, align: center"]Value[/TD]
[TD="class: xl85, width: 100, align: center"]Prorate Amt
[/TD]
[TD="class: xl76, align: center"] Expected Results
[/TD]
[TD="class: xl81, align: right"]2[/TD]
[TD="class: xl79, align: right"]13284634688276
[/TD]
[TD="class: xl78, align: right"]1[/TD]
[TD="class: xl78, align: right"]16891.69[/TD]
[TD="class: xl80, align: right"]422.09[/TD]
[TD="class: xl77, align: right"] 422.09
[/TD]
[TD="class: xl81, align: right"]3[/TD]
[TD="class: xl79, align: right"]13284634832909[/TD]
[TD="class: xl78, align: right"]1[/TD]
[TD="class: xl78, align: right"]1241.26[/TD]
[TD="class: xl80, align: right"]233.72[/TD]
[TD="class: xl77, align: right"] 29.59 [/TD]
[TD="class: xl81, align: right"]4[/TD]
[TD="class: xl79, align: right"]13284634832909[/TD]
[TD="class: xl78, align: right"]2[/TD]
[TD="class: xl78, align: right"]7191.41[/TD]
[TD="class: xl80"][/TD]
[TD="class: xl77, align: right"] 171.44
[/TD]
[TD="class: xl81, align: right"]5[/TD]
[TD="class: xl79, align: right"]13284634832909[/TD]
[TD="class: xl78, align: right"]3[/TD]
[TD="class: xl78, align: right"]1371.29[/TD]
[TD="class: xl80"][/TD]
[TD="class: xl77, align: right"] 32.69
[/TD]
[TD="class: xl81, align: right"]6[/TD]
[TD="class: xl79, align: right"]13284634838028[/TD]
[TD="class: xl78, align: right"]1[/TD]
[TD="class: xl78, align: right"]6477.89[/TD]
[TD="class: xl80, align: right"]313.34[/TD]
[TD="class: xl77, align: right"] 313.34
[/TD]
[TD="class: xl81, align: right"]7[/TD]
[TD="class: xl79, align: right"]13284634838108[/TD]
[TD="class: xl78, align: right"]1[/TD]
[TD="class: xl78, align: right"]4332.22[/TD]
[TD="class: xl80, align: right"]522.52[/TD]
[TD="class: xl77, align: right"] 176.18
[/TD]
[TD="class: xl81, align: right"]8[/TD]
[TD="class: xl79, align: right"]13284634838108[/TD]
[TD="class: xl78, align: right"]1[/TD]
[TD="class: xl78, align: right"]6755.89[/TD]
[TD="class: xl80"][/TD]
[TD="class: xl77, align: right"] 274.74
[/TD]
[TD="class: xl81, align: right"]9[/TD]
[TD="class: xl79, align: right"]13284634838108[/TD]
[TD="class: xl78, align: right"]1[/TD]
[TD="class: xl78, align: right"]1760.63[/TD]
[TD="class: xl80"][/TD]
[TD="class: xl77, align: right"] 71.60
[/TD]
[TD="class: xl81, align: right"]10[/TD]
[TD="class: xl79, align: right"]13284634838255[/TD]
[TD="class: xl78, align: right"]1[/TD]
[TD="class: xl78, align: right"]1888.8[/TD]
[TD="class: xl80, align: right"]1070.88[/TD]
[TD="class: xl77, align: right"] 26.85 [/TD]
[TD="class: xl81, align: right"]11[/TD]
[TD="class: xl79, align: right"]13284634838255[/TD]
[TD="class: xl78, align: right"]2[/TD]
[TD="class: xl78, align: right"]2102.23[/TD]
[TD="class: xl78"][/TD]
[TD="class: xl77, align: right"] 29.89
[/TD]
[TD="class: xl81, align: right"]12[/TD]
[TD="class: xl79, align: right"]13284634838255[/TD]
[TD="class: xl78, align: right"]2[/TD]
[TD="class: xl78, align: right"]5618.39[/TD]
[TD="class: xl78"][/TD]
[TD="class: xl77, align: right"] 79.88
[/TD]
[TD="class: xl81, align: right"]13[/TD]
[TD="class: xl79, align: right"]13284634838255[/TD]
[TD="class: xl78, align: right"]3[/TD]
[TD="class: xl78, align: right"]8999.02[/TD]
[TD="class: xl78"][/TD]
[TD="class: xl77, align: right"] 127.94
[/TD]
[TD="class: xl81, align: right"]14[/TD]
[TD="class: xl79, align: right"]13284634838255[/TD]
[TD="class: xl78, align: right"]4[/TD]
[TD="class: xl78, align: right"]2272.33[/TD]
[TD="class: xl78"][/TD]
[TD="class: xl77, align: right"] 32.31
[/TD]
[TD="class: xl81, align: right"]15[/TD]
[TD="class: xl79, align: right"]13284634838255[/TD]
[TD="class: xl78, align: right"]5[/TD]
[TD="class: xl78, align: right"]14910.33[/TD]
[TD="class: xl78"][/TD]
[TD="class: xl77, align: right"] 211.98 [/TD]
[TD="class: xl81, align: right"]16[/TD]
[TD="class: xl79, align: right"]13284634838255[/TD]
[TD="class: xl78, align: right"]6[/TD]
[TD="class: xl78, align: right"]39533.46[/TD]
[TD="class: xl78"][/TD]
[TD="class: xl77, align: right"] 562.04
[/TD]
</tbody>
There are multiple transactions in column A and for each transaction, there can be anywhere from 1 to several lines (column B), with each
line having value a in C. The prorate amts are always on the first line of each transaction. Lines (B) are not necessarily unique within a given transaction
and I'm not even sure if they play a part in the equation. I'm looking for results as per column E.
<colgroup><col style="mso-width-source:userset;mso-width-alt:2450;width:50pt" width="67"> <col style="mso-width-source:userset;mso-width-alt:4388;width:90pt" width="120"> <col style="mso-width-source:userset;mso-width-alt:4352;width:89pt" width="119"> <col style="mso-width-source:userset;mso-width-alt:2852;width:59pt" width="78"> <col style="mso-width-source:userset;mso-width-alt:3657;width:75pt" width="100"> <col style="mso-width-source:userset;mso-width-alt:4278;width:88pt" width="117"> </colgroup><tbody>
[TD="class: xl81, width: 67, align: center"]
[/TD]
[TD="class: xl82, width: 120, align: center"]A[/TD]
[TD="class: xl82, width: 119, align: center"]B[/TD]
[TD="class: xl82, width: 78, align: center"]C[/TD]
[TD="class: xl82, width: 100, align: center"]D[/TD]
[TD="class: xl82, width: 117, align: center"]E
[/TD]
[TD="class: xl81, align: right"]1[/TD]
[TD="class: xl83, width: 120, align: center"]Transaction #
[/TD]
[TD="class: xl84, width: 119, align: center"]Transaction Line[/TD]
[TD="class: xl85, width: 78, align: center"]Value[/TD]
[TD="class: xl85, width: 100, align: center"]Prorate Amt
[/TD]
[TD="class: xl76, align: center"] Expected Results
[/TD]
[TD="class: xl81, align: right"]2[/TD]
[TD="class: xl79, align: right"]13284634688276
[/TD]
[TD="class: xl78, align: right"]1[/TD]
[TD="class: xl78, align: right"]16891.69[/TD]
[TD="class: xl80, align: right"]422.09[/TD]
[TD="class: xl77, align: right"] 422.09
[/TD]
[TD="class: xl81, align: right"]3[/TD]
[TD="class: xl79, align: right"]13284634832909[/TD]
[TD="class: xl78, align: right"]1[/TD]
[TD="class: xl78, align: right"]1241.26[/TD]
[TD="class: xl80, align: right"]233.72[/TD]
[TD="class: xl77, align: right"] 29.59 [/TD]
[TD="class: xl81, align: right"]4[/TD]
[TD="class: xl79, align: right"]13284634832909[/TD]
[TD="class: xl78, align: right"]2[/TD]
[TD="class: xl78, align: right"]7191.41[/TD]
[TD="class: xl80"][/TD]
[TD="class: xl77, align: right"] 171.44
[/TD]
[TD="class: xl81, align: right"]5[/TD]
[TD="class: xl79, align: right"]13284634832909[/TD]
[TD="class: xl78, align: right"]3[/TD]
[TD="class: xl78, align: right"]1371.29[/TD]
[TD="class: xl80"][/TD]
[TD="class: xl77, align: right"] 32.69
[/TD]
[TD="class: xl81, align: right"]6[/TD]
[TD="class: xl79, align: right"]13284634838028[/TD]
[TD="class: xl78, align: right"]1[/TD]
[TD="class: xl78, align: right"]6477.89[/TD]
[TD="class: xl80, align: right"]313.34[/TD]
[TD="class: xl77, align: right"] 313.34
[/TD]
[TD="class: xl81, align: right"]7[/TD]
[TD="class: xl79, align: right"]13284634838108[/TD]
[TD="class: xl78, align: right"]1[/TD]
[TD="class: xl78, align: right"]4332.22[/TD]
[TD="class: xl80, align: right"]522.52[/TD]
[TD="class: xl77, align: right"] 176.18
[/TD]
[TD="class: xl81, align: right"]8[/TD]
[TD="class: xl79, align: right"]13284634838108[/TD]
[TD="class: xl78, align: right"]1[/TD]
[TD="class: xl78, align: right"]6755.89[/TD]
[TD="class: xl80"][/TD]
[TD="class: xl77, align: right"] 274.74
[/TD]
[TD="class: xl81, align: right"]9[/TD]
[TD="class: xl79, align: right"]13284634838108[/TD]
[TD="class: xl78, align: right"]1[/TD]
[TD="class: xl78, align: right"]1760.63[/TD]
[TD="class: xl80"][/TD]
[TD="class: xl77, align: right"] 71.60
[/TD]
[TD="class: xl81, align: right"]10[/TD]
[TD="class: xl79, align: right"]13284634838255[/TD]
[TD="class: xl78, align: right"]1[/TD]
[TD="class: xl78, align: right"]1888.8[/TD]
[TD="class: xl80, align: right"]1070.88[/TD]
[TD="class: xl77, align: right"] 26.85 [/TD]
[TD="class: xl81, align: right"]11[/TD]
[TD="class: xl79, align: right"]13284634838255[/TD]
[TD="class: xl78, align: right"]2[/TD]
[TD="class: xl78, align: right"]2102.23[/TD]
[TD="class: xl78"][/TD]
[TD="class: xl77, align: right"] 29.89
[/TD]
[TD="class: xl81, align: right"]12[/TD]
[TD="class: xl79, align: right"]13284634838255[/TD]
[TD="class: xl78, align: right"]2[/TD]
[TD="class: xl78, align: right"]5618.39[/TD]
[TD="class: xl78"][/TD]
[TD="class: xl77, align: right"] 79.88
[/TD]
[TD="class: xl81, align: right"]13[/TD]
[TD="class: xl79, align: right"]13284634838255[/TD]
[TD="class: xl78, align: right"]3[/TD]
[TD="class: xl78, align: right"]8999.02[/TD]
[TD="class: xl78"][/TD]
[TD="class: xl77, align: right"] 127.94
[/TD]
[TD="class: xl81, align: right"]14[/TD]
[TD="class: xl79, align: right"]13284634838255[/TD]
[TD="class: xl78, align: right"]4[/TD]
[TD="class: xl78, align: right"]2272.33[/TD]
[TD="class: xl78"][/TD]
[TD="class: xl77, align: right"] 32.31
[/TD]
[TD="class: xl81, align: right"]15[/TD]
[TD="class: xl79, align: right"]13284634838255[/TD]
[TD="class: xl78, align: right"]5[/TD]
[TD="class: xl78, align: right"]14910.33[/TD]
[TD="class: xl78"][/TD]
[TD="class: xl77, align: right"] 211.98 [/TD]
[TD="class: xl81, align: right"]16[/TD]
[TD="class: xl79, align: right"]13284634838255[/TD]
[TD="class: xl78, align: right"]6[/TD]
[TD="class: xl78, align: right"]39533.46[/TD]
[TD="class: xl78"][/TD]
[TD="class: xl77, align: right"] 562.04
[/TD]
</tbody>