June 25, 2016
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$)
VIP
April 21, 2015
VIP
Trusted Members
June 25, 2016
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.
1 Guest(s)