Claim reserve in Excel: which open file still has reserve left
A claim reserve list is one row per claim. You type the claim, the holder, the type word, an opened date, the reserve, and the amount already paid. Left is reserve minus paid while the row is open. The window says open-amt when that difference is above zero, settled when the amounts match, and over when paid is above the reserve. The first screen is a sheet named Files. Row 1 is the header. Row 2 is one claim you can check by hand: reserve 1200, paid 400, status open. Left is 800. The window is open-amt. The opened date is on the row and is not in that result. This sheet is not a policy renewal calendar. That calendar uses a contract date and notice days. This list uses the reserve and the amount paid that you typed. It is not a supplier invoice register, and it is not a customer collection list. The type word is a filter. You type the reserve. The sheet does not raise it when paid goes past it. Closed rows drop out of the window and stay on the sheet. Elyx can write the amount left and the window. You still type the reserve and the amount paid. The trial lasts 7 days, with no card.
The Problem
The file fails when the reserve lives in one note and the amount paid lives in another. Nobody can see which open claim is still above zero, because the two numbers are not on the same row. A sentence without those two numbers is not a reserve list. A second failure is mixing this sheet with a renewal calendar. A renewal row answers which contract date is inside the notice. An invoice row answers which supplier bill is unmatched. A collection row answers which customer invoice is late. If you paste renewal dates, supplier bills, or customer invoices here, the morning sum no longer means reserve left on open claims. Keep this sheet to claims, type words, and the two amounts. A third failure is deleting a file when it is closed. Set the status to closed and leave the row. Left goes blank. Deleting the row removes the only record of the 1200 and the 400 you already typed. If paid is above the reserve, the window says over. You retype the reserve yourself.
Benefits
Each claim is one row with a reserve and an amount paid you typed.
Left is reserve minus paid while the row is open.
Open-amt means left is above zero. Settled means the amounts match. Over means paid is above the reserve.
Closed rows stay on the sheet and drop out of the open-amt sum.
The opened date is stored. It is not in the money formula.
Step-by-Step Tutorial
Create the claim list
Name the sheet Files. In row 1 type: Claim, Holder, Type, Opened, Reserve, Paid, Status, Left, Window, Note. Claim is the file number you use. Holder is the name. Type is a word such as auto, home, or health. Opened is a real date you type, and it is not used by the money formulas. Reserve and Paid are amounts you typed. Left and Window are formulas. Status is a word from a list. Freeze row 1. Do not merge cells.
One row per claim. A second payment on the same file updates Paid. Do not add a second row for the same claim unless it is a different file.
Lock the status words and the type words
Select Status from row 2 down. Data, Data Validation, List. The source on a sheet named Lists is open and closed. Open means the reserve is still in play. Closed means you are done with that file. Only open stays in the window. Select Type. The source is auto, home, and health. Add a word on the Lists sheet if you need one. People must pick a word. A synonym such as paid or car will not match the formulas. The type is a filter. It is not a coverage decision.
Reserve and Paid are numbers. Do not type the word open-amt in those columns.
Enter five files you can check by hand
On row 2 type claim CL-19, type auto, any opened date, reserve 1200, paid 400, status open. You will predict left 800 and window open-amt. On row 3 type claim CL-20, type home, reserve 300, paid 100, status open. Left should be 200 and window open-amt. On row 4 type claim CL-21, type auto, reserve 500, paid 500, status open. Left should be 0 and window settled. On row 5 type claim CL-22, type health, reserve 1200, paid 1500, status open. Left should be -300 and window over. On row 6 type claim CL-23, type auto, reserve 1200, paid 400, status closed. Left and window should go blank. Change the opened date on row 2 and left should stay 800.
If you do not know the amount paid yet, leave Paid blank. Left stays blank. Do not type zero unless nothing has been paid.
Write the amount left
Left is blank when status is closed, or when Reserve or Paid is blank. Otherwise it is Reserve minus Paid. With Reserve in E2, Paid in F2, and Status in G2, use =IF(OR(G2="closed",E2="",F2=""),"",E2-F2). Format as a number. A positive number is reserve still above the amount paid. Zero means the amounts match. A negative number means paid is above the reserve you typed. The opened date in column D is not in this formula.
=IF(OR(G2="closed",E2="",F2=""),"",E2-F2)1200 minus 400 is 800. 300 minus 100 is 200. 1200 minus 1500 is -300. If you see another number, the cells are not the amounts you typed.
Write the window
The window is blank when status is closed, or when either amount is blank. Otherwise it is open-amt when Reserve minus Paid is above zero, over when that difference is below zero, and settled when it is zero. Use =IF(OR(G2="closed",E2="",F2=""),"",IF(E2-F2>0,"open-amt",IF(E2-F2<0,"over","settled"))). Open-amt means reserve is still above paid. Settled means the two amounts match. Over means you paid more than the reserve you typed, so the reserve cell needs a look. The formula does not change the reserve.
=IF(OR(G2="closed",E2="",F2=""),"",IF(E2-F2>0,"open-amt",IF(E2-F2<0,"over","settled")))Do not wrap Left in ABS. Hiding the minus sign hides CL-22, where 1500 was typed against a reserve of 1200.
Add only the open remainders
On a sheet named Morning, the open-amt rows are =COUNTIFS(Files!I:I,"open-amt"). With the rows above, that count is 2: left of 800 and left of 200. The open sum is =SUMIFS(Files!H:H,Files!I:I,"open-amt"), which is 1000. The over count is =COUNTIFS(Files!I:I,"over"), which is 1, the left of -300. Closed CL-23 is out because its window is blank. A blank window is not open-amt. Auto files that are open-amt are =COUNTIFS(Files!C:C,"auto",Files!I:I,"open-amt"), which is 1, only CL-19. Write the rule next to the sum.
=SUMIFS(Files!H:H,Files!I:I,"open-amt")Do not add the -300 into the open sum. Over is a different word, so SUMIFS leaves it out.
Read a negative left as a reserve to retype
If left is negative and status is still open, paid is above the reserve and the row was not closed. That is a row to open, not a row to delete, and not a row whose reserve the formula should raise. Either the payment was typed on the wrong file, or the reserve of 1200 is no longer the number you mean next to a paid amount of 1500. Fix the cell that is wrong. The -300 does not say the claim is denied. A left amount on a closed row should not happen, because closed blanks the cell. If you see a number on a closed row, the formula was overwritten.
Filter Window to open-amt for the morning list. Filter Type to home when you only want that word. Filter Status to closed when you need history.
Check the five files before you share the sum
Reserve 1200 and paid 400, status open: left 800, window open-amt. Reserve 300 and paid 100, status open: left 200, window open-amt. Reserve 500 and paid 500, status open: left 0, window settled. Reserve 1200 and paid 1500, status open: left -300, window over. The same 1200 and 400 with status closed: left blank, window blank. The open-amt sum is 1000, and the open-amt count is 2. Changing the opened date does not move left. If a check fails, fix the amounts or the status word before you send the sum. The sum is only this sheet. It is not a renewal date, and it is not a coverage opinion.
Write the five checks on Morning if more than one person updates the files.
Template Features
Left while the claim is open
Reserve minus paid. Blank when status is closed or an amount is empty.
=IF(OR(G2="closed",E2="",F2=""),"",E2-F2)Window from that difference
Open-amt above zero, over below zero, settled at zero.
=IF(OR(G2="closed",E2="",F2=""),"",IF(E2-F2>0,"open-amt",IF(E2-F2<0,"over","settled")))Open-amt count
COUNTIFS reads the window. With the sample rows the count is 2.
=COUNTIFS(Files!I:I,"open-amt")Open-amt sum
SUMIFS adds only left amounts whose window is open-amt. The sample sum is 1000.
=SUMIFS(Files!H:H,Files!I:I,"open-amt")History stays on the sheet
Closed rows remain. They leave the open sum because the window is blank.
Concrete Examples
800 still above the amount paid
CL-19 has a reserve of 1200 and 400 already paid. You want it on the morning list.
Reserve: 1200. Paid: 400. Status: open. Type: auto.
Result: Left is 800. Window is open-amt. The open count includes the row. The 1200 and the 400 are the numbers you typed.
A second open file makes 1000
CL-20 has a reserve of 300 and 100 already paid. CL-19 is still open-amt at 800.
CL-20 reserve 300, paid 100, status open. CL-19 still 1200 and 400.
Result: Left is 200, window open-amt. The open sum is 1000, which is 800 plus 200. The open count is 2.
Paid above the reserve
CL-22 has a reserve of 1200 and 1500 typed as paid. You do not want that row in the open sum, and you do not want the formula to raise the reserve.
Reserve: 1200. Paid: 1500. Status: open.
Result: Left is -300. Window is over. The open sum stays 1000. Closing the row blanks left and leaves the amounts on the sheet.
Pro Tips
Zero paid is a number
Type 0 only when nothing has been paid. A blank cell means you do not know yet, and left stays blank. 1200 minus 0 is a left of 1200 and a window of open-amt.
The opened date is not the window
Type any real date in Opened. Change it and left does not move. The renewal calendar is the sheet that reads dates. This one reads the two amounts.
Text amounts do not subtract
If Reserve was pasted as text, E2-F2 will not be 800. Re-enter 1200 and 400 as numbers. Then read the window again.
Distinct from a renewal calendar
Policy dates belong on the insurance agent's renewal list. Supplier invoices belong on the accounts payable register. Customer invoices belong on the collection list. Do not paste those rows here and expect this sum to mean reserve left.