Generate a report where there are yearly cap on reimbursement for employees

[just4usam]

Hi All,

Need help with this.

I was asked to create this report and am not able.
Would somebody be able to help me with is last bit I am looking to perfect the report.

If you look at the tab named "Table of Reimbursement Summary",: (I have highlighted the cells in red)

a) worker ID - 205110 (Cell nos B7 to B10) - this id had only used 120 in the month of March and 540 in the month of April towards technology and should show accordingly in the table but the formula i have written is showing 660 for both months.
b) worker ID - 734972 (cell nos B32 to B35) - this id had used 700 for month of March and 500 for month of April towards technology and should show 700 for month of March and zero for every other month after that since he ash already reached the cap of 700 for the year but it is showing 700 for both March and April.

The attached excel file has Instructions in 1st tab.

Thanks for all help in advance.

Apologies if my formula looks shabby, but please do suggest other better formulas if you think this may look much more refined.

[#2]

@just4usam I built a sample solution that simplifies your formula and breaks it apart. The revised column uses a reference table to determine when a max value condition is met. The running column applies a running sum to see if the previous data entry has met the max value condition.

There are two situations that are still unclear.

1. The Months are not ordered chronologically, which will be problematic for the running total.

2. How should the running deal with the situation when wellness was 170 the month prior and 81 in the current month? Should it subtract be 0 or sum to 200? (example highlighted in green)

[#3]

@adversi 

Thank you for taking the time out on this

on the situations you have mentioned:

a) the month column needs to be chronologically ordered

b) Since the wellness category has a monthly cap, the formula needs to give an output either the value itself if its less than 200 or display as 200 if the value is more than 200.

[#4]

@adversi 

Again, below are the conditions that the report has to be based on:

a) Wellness reimbursement cap of $200 per month

b) Technology reimbursement cap of $700 per year