Hi excel genius,
I want to track medicare in excel,how can i?
my company has grant medical allowance only $500 per year.
For such case In June a sale man has claim his medica 46.25$, how can i track his claim to follow up he does not over claim in the year from Jan to December?
Thanks in advance
(Note i want to track from Jan to Dec because medica can claim full year but not exceed amount 500$)
So you want to be able for each person to keep up all claims and then automatically see if the 500 border is not crossed?
How many claims can a person make in a year?
Hi,Frans yes,a person can claim 500$ in a year
My suggestion.
(1) Analysis of claims for the year
Use a Pivot Table. It is the fastest and easiest way to analyze data
(2) Make sure total claims for each person does not exceed 500 in a year.
A filter of your data should be able to tell you the claims to-date for a particular person. In the column where the claim is, do not use SUM() to total.
SUM() includes all amount (including filtered data). Use SUBTOTAL() instead. eg=SUBTOTAL(9,B2:B100) as SUBTOTAL will only total the visible data.
You will then be able to see only the total for the particular person.
Hope this helps.
Hi Sunny,could you upload some sample file?
Refer sample attached
Thanks,Sunny
