First time poster, please let me know (gently) if I'm in the wrong place or otherwise doing something incorrectly.
I'm currently out of ideas on how to achieve something that might be easier to show than describe:
<tbody>
</tbody>
I've got a list of transactions, in processed date order, for customers identified by unique values in Column A above. Column B shows a "running total" of what we originally charged, while Column D shows a revised charge per transaction. This is why Column B always has a number, while Column D will contain zeros (no change in running total means no change during that transaction). I used column C to identify the first transaction for a particular customer number, while column E contains my formula:
<code>=IF(D2=0,0,IF(C2=TRUE,D2-B2,IF(SUM(OFFSET(D2,(COUNTIF($A$1:A2,A2)-1)*-1,0,COUNTIF($A$1:A2,A2)-1))=0,D2-B2,"Do it")))</code>
I've gotten as far as the "Do it" bit (because I don't know how to make it do it!). Column F shows what I'm trying to return for the "Do it" values in Column E.
My main problem is comparing a "running total" to a per-transaction amount. Also the first transaction for a customer could be a zero value, so I've tried my best to deal with that in the above formula.
I'm not sure my approach is appropriate, but I've used a combination of COUNTIF and OFFSET to dynamically define a range for the formula, a range consisting of the all records for a particular Customer Number which precede the current record. If the sum of preceding records is zero, and the current record is non-zero, i know it's my first non-zero record and proceed with Column D - Column B.
When I come to a record for a customer that is a non-zero value in Column D, and it is preceded by at least one non-zero value for that same customers records above it, I want to subtract the first non-zero value counting up from the current record.
I've seen some examples of array formulas that will subtract the first non-zero value it finds in a column, but I don't have a lot of experience with SCE formulas and wasn't sure how to adapt them to my purposes.
Any help is appreciated! Even just a suggestion on a combination of formulas or a different approach to my table might put me on the right track.
Thanks all.
I'm currently out of ideas on how to achieve something that might be easier to show than describe:
| Customer_Number | Original_charge | First_Transaction | Revised_charge | Additional/Return | Should_be |
| 00001950000000 | $480.00 | TRUE | $507.00 | $27.00 | $27.00 |
| 00001950000000 | $480.00 | FALSE | $0.00 | $0.00 | $0.00 |
| 00001950000000 | $480.00 | FALSE | $0.00 | $0.00 | $0.00 |
| 00001950000000 | $480.00 | FALSE | $507.00 | Do it | $0.00 |
| 00001950000000 | $480.00 | FALSE | $507.00 | Do it | $0.00 |
| 00001950000000 | $480.00 | FALSE | $0.00 | $0.00 | $0.00 |
| 00001950000000 | $384.00 | FALSE | $407.00 | Do it | -$100.00 |
| 00001950000000 | $383.00 | FALSE | $405.00 | Do it | -$2.00 |
| 00001950000000 | $383.00 | FALSE | $405.00 | Do it | $0.00 |
| 00001950000000 | $383.00 | FALSE | $405.00 | Do it | $0.00 |
| 00001950000000 | $383.00 | FALSE | $0.00 | $0.00 | $0.00 |
| 00001950000000 | $383.00 | FALSE | $405.00 | Do it | $0.00 |
| 00001950000000 | $383.00 | FALSE | $0.00 | $0.00 | $0.00 |
| 00001950000000 | $383.00 | FALSE | $0.00 | $0.00 | $0.00 |
| 00001950000000 | $383.00 | FALSE | $405.00 | Do it | $0.00 |
| 00001950000000 | $383.00 | FALSE | $398.00 | Do it | -$7.00 |
| 00001950000000 | $383.00 | FALSE | $0.00 | $0.00 | $0.00 |
| 00001950000000 | $380.00 | FALSE | $398.00 | Do it | $0.00 |
| 00016020000000 | $88.00 | TRUE | $63.00 | -$25.00 | -$25.00 |
| 00016020000000 | $88.00 | FALSE | $0.00 | $0.00 | $0.00 |
| 00016020000000 | $88.00 | FALSE | $0.00 | $0.00 | $0.00 |
| 00016020000000 | $88.00 | FALSE | $0.00 | $0.00 | $0.00 |
| 00016020000000 | $88.00 | FALSE | $0.00 | $0.00 | $0.00 |
| 00016020000000 | $88.00 | FALSE | $62.00 | Do it | -$1.00 |
| 00016020000000 | $88.00 | FALSE | $62.00 | Do it | $0.00 |
| 00016020000000 | $138.00 | FALSE | $97.00 | Do it | $34.00 |
<tbody>
</tbody>
I've got a list of transactions, in processed date order, for customers identified by unique values in Column A above. Column B shows a "running total" of what we originally charged, while Column D shows a revised charge per transaction. This is why Column B always has a number, while Column D will contain zeros (no change in running total means no change during that transaction). I used column C to identify the first transaction for a particular customer number, while column E contains my formula:
<code>=IF(D2=0,0,IF(C2=TRUE,D2-B2,IF(SUM(OFFSET(D2,(COUNTIF($A$1:A2,A2)-1)*-1,0,COUNTIF($A$1:A2,A2)-1))=0,D2-B2,"Do it")))</code>
I've gotten as far as the "Do it" bit (because I don't know how to make it do it!). Column F shows what I'm trying to return for the "Do it" values in Column E.
My main problem is comparing a "running total" to a per-transaction amount. Also the first transaction for a customer could be a zero value, so I've tried my best to deal with that in the above formula.
I'm not sure my approach is appropriate, but I've used a combination of COUNTIF and OFFSET to dynamically define a range for the formula, a range consisting of the all records for a particular Customer Number which precede the current record. If the sum of preceding records is zero, and the current record is non-zero, i know it's my first non-zero record and proceed with Column D - Column B.
When I come to a record for a customer that is a non-zero value in Column D, and it is preceded by at least one non-zero value for that same customers records above it, I want to subtract the first non-zero value counting up from the current record.
I've seen some examples of array formulas that will subtract the first non-zero value it finds in a column, but I don't have a lot of experience with SCE formulas and wasn't sure how to adapt them to my purposes.
Any help is appreciated! Even just a suggestion on a combination of formulas or a different approach to my table might put me on the right track.
Thanks all.
