Running Balances for employees

taylorberner

Hello all,

I am trying to make a spreadsheet that tracks employee fitness reimbursement balances based off of their entries into a form. Each employee has exactly $300 that they can use per year for fitness expenses. Every time they submit a reimbursement, it gets put into a form that includes their name, clock ID, date, expense type, and expense amount. I would like to start a new spreadsheet that tracks employee reimbursement balances based on the entries from the original form.

The trick part for me is that we do not have a list that includes all employees names or ID's, so it would need to pull this from the original spreadsheet and then calculate balances, even if they have multiple entries.

Genevieve P.

Hi @taylorberner

Instead of creating a separate sheet, what about using a Report to surface these calculations? You could have the Report Group by the Employee ID, then SUM the expense amount column.

See: Redesigned Reports with Grouping and Summary Functions

If you need to see the remaining balance, you could have an additional column in the source sheet which Sums together each ID's expense amount and subtracts it from $300, using the SUMIFS Function.

Ex:

=300 - SUMIFS([expense amount]:[expense amount], [employee name]:[employee name], [employee name]@row)

Then show this column in the Report and use Summarize to show the "MIN" or "MAX" amount in the top summary row (the amount will be the same in every row so it will be the same in the summary row).

Cheers,

Genevieve

taylorberner

The report is going to work great. Thank you for the help!

Genevieve P.

No problem! 🙂