A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data
Example-MicrosoftCommunity.png
This has the column names
This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
Rules and scenarios
The new example spreadsheet I copied has the helper columns H and I that was suggested in previpus psot. I added column K(RVU). The data for this is populated from a lookup formula which referencesExample-MicrosoftCommunity.png a lookup sheet
I also highlighted the rows that need to be retracted based on lowest RVU. This is the end result I am looking for.
For member Rose-A20 1 unit of 97032 should be retracted since 97032 has the lowest RVU at 0.25.
For member Adam-B20 3 units of 97761 should be retracted although it does not matter which line since both codes 97761 and 97760 have same rvu which is 0.50.
Note: All the codes in Column F are valid codes. No need to exclude any codes.
Also this report has only claims that exceed 4 unit limit.
A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data
Example-MicrosoftCommunity.png
This has the column names
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.