Working with some accounting transaction data and an aging table.
I have to make some changes in how the age is calculated and how the buckets are determined.
Specifically, I need help with the following:
I have done this in the past using a pivot table to calculate the oldest transaction date by pro and then a helper column added to the table which looked up that date. I need a formula solution for this version though. Greatly appreciate any help.
I run 365 Enterprise with Apps for Windows.
Thanks so much
I have to make some changes in how the age is calculated and how the buckets are determined.
Specifically, I need help with the following:
| 1. A new aging formula that subtracts the AGEDATE from the oldest transaction date for that pro, where AGEDATE is a named range for current period's week-ending date and pros, column G, are the orders in my record data. Since the data is transaction-based there may be one or many duplicated pros in the data. - I think I got this on my last attempt. |
| 2. For the aging buckets, I need a formula for CURRENT in column N, that will return the PPD or prepaid value, column H, for that row if its age is 30 days or less OR the action status, column J, contains the text C&D. This phrase needs to be encased in asterisks acting as wildcards. |
| 3. I need to adjust the other age bucket formulas (columns O through T) so they will not pick up the PPD or prepaid amount for any pro that has an action status containing the text C&D regardless of its age. |
I run 365 Enterprise with Apps for Windows.
Thanks so much
| Book1 | |||||||||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | W | X | Y | |||
| 1 | Txn Date | Jrnl No. | Orig. Audit Trail | Distribution Reference | Orig. Master # | Orig. Master Name | Pro | PPD | Action Comments | Action Status | Last Reviewed | DIV | Aging | Current | 31-60 | 61-90 | 91-120 | 121-150 | 151-180 | Over 180 | Total | Diff | AGEDATE | 5/13/2023 | |||
| 2 | 3/24/2023 | 4124906 | PMTRX00021243 | NULL | NULL | NULL | 95-206317 | 450.00 | NULL | C&D-Requested 04/28 | 4/3/2023 | 95 | 50 | 450.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 450.00 | 0.00 | ||||||
| 3 | 3/24/2023 | 4124906 | PMTRX00021243 | NULL | NULL | NULL | 95-206317 | (250.00) | NULL | DEFER | 4/3/2023 | 95 | 50 | (250.00) | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | (250.00) | 0.00 | ||||||
| 4 | 3/24/2023 | 4124907 | PMTRX00021243 | NULL | NULL | NULL | 95-217819 | 3,250.00 | NULL | DEFER | 4/3/2023 | 95 | 50 | 3,250.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 3,250.00 | 0.00 | ||||||
| 5 | 3/24/2023 | 4124907 | PMTRX00021243 | NULL | NULL | NULL | 95-217819 | (3,050.00) | NULL | DEFER | 4/3/2023 | 95 | 50 | (3,050.00) | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | (3,050.00) | 0.00 | ||||||
| 6 | 4/20/2023 | 4249048 | CMTRX00001418 | NULL | NULL | NULL | 95-240951 | (3,732.00) | NULL | C&D-Requested 04/28 | 4/28/2023 | 95 | 23 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 3,732.00 | ||||||
| 7 | 4/20/2023 | 4249048 | CMTRX00001418 | NULL | NULL | NULL | 95-240952 | (3,732.00) | NULL | C&D-Requested 04/28 | 4/28/2023 | 95 | 23 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 3,732.00 | ||||||
| 8 | 4/20/2023 | 4249048 | CMTRX00001418 | NULL | NULL | NULL | 95-240953 | (3,732.00) | NULL | C&D-Requested 04/28 | 4/28/2023 | 95 | 23 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 3,732.00 | ||||||
| 9 | 4/20/2023 | 4249048 | CMTRX00001418 | NULL | NULL | NULL | 95-240954 | (3,732.00) | NULL | C&D-Requested 04/28 | 4/28/2023 | 95 | 23 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 3,732.00 | ||||||
| 10 | 4/20/2023 | 4249048 | CMTRX00001418 | NULL | NULL | NULL | 95-240955 | (3,732.00) | NULL | C&D-Requested 04/28 | 4/28/2023 | 95 | 23 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 3,732.00 | ||||||
| 11 | 4/20/2023 | 4249048 | CMTRX00001418 | NULL | NULL | NULL | 95-243439 | (559.00) | NULL | C&D-Requested 04/28 | 4/28/2023 | 95 | 23 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 559.00 | ||||||
| 12 | 4/20/2023 | 4249048 | CMTRX00001418 | NULL | NULL | NULL | 95-243439 | (559.00) | NULL | C&D-Requested 04/28 | 4/28/2023 | 95 | 23 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 559.00 | ||||||
| 13 | 4/20/2023 | 4249048 | CMTRX00001418 | NULL | NULL | NULL | 95-243439 | (559.00) | NULL | C&D-Requested 04/28 | 4/28/2023 | 95 | 23 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 559.00 | ||||||
| 14 | 4/20/2023 | 4249048 | CMTRX00001418 | NULL | NULL | NULL | 95-243439 | (559.00) | NULL | C&D-Requested 04/28 | 4/28/2023 | 95 | 23 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 559.00 | ||||||
| 15 | 4/20/2023 | 4249048 | CMTRX00001418 | NULL | NULL | NULL | 95-243439 | (559.00) | NULL | C&D-Requested 04/28 | 4/28/2023 | 95 | 23 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 559.00 | ||||||
| 16 | 4/20/2023 | 4249048 | CMTRX00001418 | NULL | NULL | NULL | 95-243439 | (1,118.00) | NULL | C&D-Requested 04/28 | 4/28/2023 | 95 | 23 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 1,118.00 | ||||||
| 17 | 4/20/2023 | 4249048 | CMTRX00001418 | NULL | NULL | NULL | 95-243439 | (1,118.00) | NULL | C&D-Requested 04/28 | 4/28/2023 | 95 | 23 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 1,118.00 | ||||||
| 18 | 4/20/2023 | 4249048 | CMTRX00001418 | NULL | NULL | NULL | 95-243439 | (1,677.00) | NULL | C&D-Requested 04/28 | 4/28/2023 | 95 | 23 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 1,677.00 | ||||||
| 19 | 4/20/2023 | 4249048 | CMTRX00001418 | NULL | NULL | NULL | 95-245072 | (559.00) | NULL | C&D-Requested 04/28 | 4/28/2023 | 95 | 23 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 559.00 | ||||||
| 20 | 4/20/2023 | 4249048 | CMTRX00001418 | NULL | NULL | NULL | 95-245072 | (559.00) | NULL | C&D-Requested 04/28 | 4/28/2023 | 95 | 23 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 559.00 | ||||||
| 21 | 4/20/2023 | 4249048 | CMTRX00001418 | NULL | NULL | NULL | 95-245072 | (559.00) | NULL | C&D-Requested 04/28 | 4/28/2023 | 95 | 23 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 559.00 | ||||||
| 22 | 4/20/2023 | 4249048 | CMTRX00001418 | NULL | NULL | NULL | 95-245072 | (559.00) | NULL | C&D-Requested 04/28 | 4/28/2023 | 95 | 23 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 559.00 | ||||||
| 23 | 4/20/2023 | 4249048 | CMTRX00001418 | NULL | NULL | NULL | 95-245072 | (559.00) | NULL | C&D-Requested 04/28 | 4/28/2023 | 95 | 23 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 559.00 | ||||||
| 24 | 4/20/2023 | 4249048 | CMTRX00001418 | NULL | NULL | NULL | 95-245072 | (1,118.00) | NULL | C&D-Requested 04/28 | 4/28/2023 | 95 | 23 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 1,118.00 | ||||||
| 25 | 4/20/2023 | 4249048 | CMTRX00001418 | NULL | NULL | NULL | 95-245072 | (1,118.00) | NULL | C&D-Requested 04/28 | 4/28/2023 | 95 | 23 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 1,118.00 | ||||||
| 26 | 4/20/2023 | 4249048 | CMTRX00001418 | NULL | NULL | NULL | 95-245072 | (1,677.00) | NULL | C&D-Requested 04/28 | 4/28/2023 | 95 | 23 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 1,677.00 | ||||||
| 27 | 4/20/2023 | 4249048 | CMTRX00001418 | NULL | NULL | NULL | 95-245072 | (1,677.00) | NULL | C&D-Requested 04/28 | 4/28/2023 | 95 | 23 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 1,677.00 | ||||||
| 28 | 2/22/2022 | 1874399 | PMTRX00015318 | NULL | NULL | NULL | 95-247706 | 150.00 | NULL | DEFER | 1/10/2023 | 95 | 445 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 150.00 | 150.00 | 0.00 | ||||||
| 29 | 4/20/2023 | 4249048 | CMTRX00001418 | NULL | NULL | NULL | 95-250576 | (559.00) | NULL | C&D-Requested 04/28 | 4/28/2023 | 95 | 23 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 559.00 | ||||||
| 30 | 4/20/2023 | 4249048 | CMTRX00001418 | NULL | NULL | NULL | 95-250576 | (1,118.00) | NULL | C&D-Requested 04/28 | 4/28/2023 | 95 | 23 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 1,118.00 | ||||||
| 31 | 4/20/2023 | 4249048 | CMTRX00001418 | NULL | NULL | NULL | 95-250577 | (297.00) | NULL | C&D-Requested 04/28 | 4/28/2023 | 95 | 23 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 297.00 | ||||||
| 32 | 1/21/2023 | 3656483 | GLTRX00060402 | NULL | NULL | NULL | 96-275310 | 100.00 | NULL | DEFER | 11/17/2022 | 96 | 112 | 0.00 | 0.00 | 100.00 | 0.00 | 0.00 | 0.00 | 100.00 | 0.00 | ||||||
| 33 | 10/30/2022 | 3309556 | PMTRX00019420 | NULL | NULL | NULL | 96-275335 | 735.00 | NULL | DEFER | 11/17/2022 | 96 | 195 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 735.00 | 735.00 | 0.00 | ||||||
| 34 | 10/30/2022 | 3309556 | PMTRX00019420 | NULL | NULL | NULL | 96-275335 | 245.00 | NULL | DEFER | 4/28/2023 | 96 | 195 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 245.00 | 245.00 | 0.00 | ||||||
| 35 | 10/30/2022 | 3309556 | PMTRX00019420 | NULL | NULL | NULL | 96-275335 | 200.00 | NULL | DEFER | 4/28/2023 | 96 | 195 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 200.00 | 200.00 | 0.00 | ||||||
| 36 | 10/29/2022 | 3266581 | PMTRX00019205 | NULL | NULL | NULL | 96-275409 | 165.00 | NULL | DEFER | 4/28/2023 | 96 | 196 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 165.00 | 165.00 | 0.00 | ||||||
| 37 | 10/26/2022 | 3262960 | PMTRX00019197 | NULL | NULL | NULL | 96-275415 | 637.42 | NULL | DEFER | 11/17/2022 | 96 | 199 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 637.42 | 637.42 | 0.00 | ||||||
| 38 | 10/29/2022 | 3266581 | PMTRX00019205 | NULL | NULL | NULL | 96-275415 | 375.31 | NULL | DEFER | 1/26/2023 | 96 | 199 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 375.31 | 375.31 | 0.00 | ||||||
| 39 | 10/27/2022 | 3266589 | PMTRX00019206 | NULL | NULL | NULL | 96-275415 | 267.48 | NULL | DEFER | 1/26/2023 | 96 | 199 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 267.48 | 267.48 | 0.00 | ||||||
| 40 | 1/28/2023 | 3842933 | GLTRX00061403 | NULL | NULL | NULL | 96-275533 | 100.00 | NULL | DEFER | 1/17/2023 | 96 | 105 | 0.00 | 0.00 | 100.00 | 0.00 | 0.00 | 0.00 | 100.00 | 0.00 | ||||||
| 41 | 11/23/2022 | 3392060 | PMTRX00019706 | NULL | NULL | NULL | 96-275640 | 990.00 | NULL | C&D | 3/21/2023 | 96 | 171 | 0.00 | 0.00 | 0.00 | 0.00 | 990.00 | 0.00 | 990.00 | 0.00 | ||||||
| 42 | 11/24/2022 | 3421806 | PMTRX00019776 | NULL | NULL | NULL | 96-275640 | 330.00 | NULL | C&D | 4/28/2023 | 96 | 171 | 0.00 | 0.00 | 0.00 | 0.00 | 330.00 | 0.00 | 330.00 | 0.00 | ||||||
| 43 | 4/25/2023 | 4261146 | GLTRX00066979 | NULL | NULL | NULL | 96-276241 | (150.00) | NULL | C&D-Not requested | 5/5/2023 | 96 | 18 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 150.00 | ||||||
| 44 | 12/1/2022 | 3438378 | PMTRX00019844 | NULL | NULL | NULL | 96-277407 | 400.00 | NULL | DEFER | 3/21/2023 | 96 | 163 | 0.00 | 0.00 | 0.00 | 0.00 | 400.00 | 0.00 | 400.00 | 0.00 | ||||||
| 45 | 2/9/2022 | 1841818 | PMTRX00015237 | NULL | NULL | NULL | 98-138231 | 500.00 | NULL | DEFER | 3/9/2023 | 98 | 458 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 500.00 | 500.00 | 0.00 | ||||||
| 46 | 12/21/2022 | 3537683 | GLTRX00057792 | NULL | NULL | NULL | 98-189766 | 600.00 | NULL | DEFER | 3/14/2023 | 98 | 143 | 0.00 | 0.00 | 0.00 | 600.00 | 0.00 | 0.00 | 600.00 | 0.00 | ||||||
| 47 | 12/21/2022 | 3537683 | GLTRX00057792 | NULL | NULL | NULL | 98-189767 | 600.00 | NULL | DEFER | 3/14/2023 | 98 | 143 | 0.00 | 0.00 | 0.00 | 600.00 | 0.00 | 0.00 | 600.00 | 0.00 | ||||||
| 48 | 12/21/2022 | 3537683 | GLTRX00057792 | NULL | NULL | NULL | 98-189769 | 600.00 | NULL | DEFER | 3/14/2023 | 98 | 143 | 0.00 | 0.00 | 0.00 | 600.00 | 0.00 | 0.00 | 600.00 | 0.00 | ||||||
| 49 | 12/21/2022 | 3537683 | GLTRX00057792 | NULL | NULL | NULL | 98-189771 | 600.00 | NULL | DEFER | 3/14/2023 | 98 | 143 | 0.00 | 0.00 | 0.00 | 600.00 | 0.00 | 0.00 | 600.00 | 0.00 | ||||||
| 50 | 3/27/2023 | 4138688 | PMTRX00021274 | NULL | NULL | NULL | 98-194672 | 600.00 | NULL | DEFER | 4/7/2023 | 98 | 47 | 600.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 600.00 | 0.00 | ||||||
| 51 | 3/27/2023 | 4138688 | PMTRX00021274 | NULL | NULL | NULL | 98-194675 | 600.00 | NULL | DEFER | 4/7/2023 | 98 | 47 | 600.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 600.00 | 0.00 | ||||||
| 52 | 3/30/2023 | 4173208 | PMTRX00021305 | NULL | NULL | NULL | 98-194676 | 200.00 | NULL | DEFER | 4/7/2023 | 98 | 44 | 200.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 200.00 | 0.00 | ||||||
| 53 | 3/27/2023 | 4138688 | PMTRX00021274 | NULL | NULL | NULL | 98-194677 | 600.00 | NULL | DEFER | 4/7/2023 | 98 | 47 | 600.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 600.00 | 0.00 | ||||||
| 54 | 3/29/2023 | 4168658 | PMTRX00021302 | NULL | NULL | NULL | 98-194681 | 1,200.00 | NULL | DEFER | 4/7/2023 | 98 | 45 | 1,200.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 1,200.00 | 0.00 | ||||||
| 55 | 3/30/2023 | 4173208 | PMTRX00021305 | NULL | NULL | NULL | 98-194681 | 200.00 | NULL | DEFER | 4/7/2023 | 98 | 45 | 200.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 200.00 | 0.00 | ||||||
Sheet1 | |||||||||||||||||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| L2:L55 | L2 | =LEFT(G2,2)*1 |
| M2:M55 | M2 | =AGEDATE-MINIFS($A$3:INDEX($A:$A,COUNTA($A:$A)+1),$G$3:INDEX($G:$G,COUNTA($G:$G)+1),G2) |
| O2:S55 | O2 | =IF(AND($M2>=TEXTBEFORE(O$1,"-",1)*1,$M2<=TEXTAFTER(O$1,"-",1)*1),$H2,0) |
| T2:T55 | T2 | =IF($M2>TEXTAFTER(T$1," ",1)*1,$H2,0) |
| U2:U55 | U2 | =SUM(N2:T2) |
| V2:V55 | V2 | =+U2-$H2 |
| Named Ranges | ||
|---|---|---|
| Name | Refers To | Cells |
| AGEDATE | =Sheet1!$Y$1 | M2:M55 |
