A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data
Hello Excel@excel,
Thank you for the clear example and expected results. Based on your screenshot (Member ID in D, Date in E, billed units in G, total units paid in H and RVU in K), you can calculate the retraction without sorting or highlighting rows manually.
In I2, enter this formula and copy it down:
=MAX(0,MIN(G2,H2-4-SUMIFS($G$2:$G$1000,$D$2:$D$1000,D2,$E$2:$E$1000,E2,$K$2:$K$1000,"<"&K2)-SUMPRODUCT(($D$2:$D$1000=D2)($E$2:$E$1000=E2)($K$2:$K$1000=K2)(ROW($G$2:$G$1000)<ROW())$G$2:$G$1000)))
Change 1000 to the last row of your report if needed.
For each member and date of service, the formula works out the excess units (total paid minus 4). It then subtracts units already assigned to lines with a lower RVU, or to earlier lines with the same RVU. What's left is retracted from the current line, up to that line's billed units. If the lowest RVU line doesn't have enough units, the remaining units move to the next lowest RVU automatically. Claim ID isn't used, so the same claim with multiple dates (John) and different claims for the same member (Tom) are both handled correctly.
In J2, enter this formula and copy it down:
=IF(I2=0,"keep","Retract "&I2&" unit"&IF(I2=1,"","s"))
When two lines have the same RVU, as in Adam's case, the formula retracts from the line that appears first in the sheet. Please make sure the dates, units and RVUs are stored as numbers, not text. Otherwise, the SUMIFS and comparisons won't match.
I hope this helps. Looking forward to your reply.
If the answer is helpful, please click "Yes" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in the forum documentation to enable e-mail notifications if you want to receive the related email notification for this thread.