I really need some help
I have attached the Excel sheet I am working with as you can see its got the all the working days of Jan and so far for Feb, well this will grow over the 12 months all the way to end of the year.
The problem is I need to get the average of each person figures for each month i.e.
Person A, Jan Average, Feb Average.
The average will be the total for the month divided by the days of that month worked (anything blank is seen as a day off).
One of the issues is the data comes from an old AS400 system and through filtering out all the nonsense data we seem to be stuck with a blank line in each month. i.e. data is the work blank and the whole line is a blank which is easy tog et around by just -1 from the total of days calculated.
Thing is I've already written a load of code and its all IF statements within IF statements.
So far it can read Just January for one person and give a total and I am concerned that this is going to turn into a beast for some simple figures.
The biggest issues is that this is updated and sent to managers and directors daily so its needs to be less of a daily chore to update the figures.
Does anyone one have any simpler ideas as to achieve this without what currently appears to be 100s of lines of VBA.
There is one thing, it maybe that more staff could come and work in a year so they would need adding to the sheet of data if needed so be another column header and figures to work with.
I hope you guru's have some good advice.
KR
Kevin
Bookmarks