formula to help with percentage for month to date

[dougers1]

Hi All
im looking for help with my scorecard i have set it up so that i can measure staff - i have everything i want except my total effectiveness in column

AN Highlighted in green

at the moment it gives me a total percentage of the 14 staff hours worked and travel time / how many working days 7.5 hours there are in the month - Mon to Fridays only

My problem is my total effectiveness is measured against the full month rather that to date - How do i achieve this

at the moment i need to wait till the month is over to see how we measured - i want to be able to measure as the month goes on.


see attachment

[oeldere]

I don't get the problem.

The formula are in AJ4 and AJ5.

If the data is filled in e.g. both row 4 and row 5, you have the effectiveness for this month.

Even if name 1 has filled up till day 5 and name 2 has filled up till day 10 there will (to my opinion) no problem, unless you work with weight average.

But I don't see weight average in you file.

[dougers1]

ok say i fill in day 1 - it gives me a percentage of hours worked and travel against the full month - what i would like it to do is give me a percenatge of day 1s hours worked and travel/ 14*7.5 and so on as the month goes on.

[oeldere]

Your formula in E7 is

e7=SUM(E5:E6)/7,5

ok say i fill in day 1 - it gives me a percentage of hours worked and travel against the full month - what i would like it to do is give me a percenatge of day 1s hours worked and travel/ 14*7.5 and so on as the month goes on.

So that is apparently not the result. What should be the result (and in which cell)?

[dougers1]

Oeldere

the percentage i want changed is in column AN
this gives me a percentage of hoursand travel time entered against the full month - i want it to give me a percentage of total hours available /hours worked and travel time as the month gets older.
for inyance if it only day 1 then hours available are 14*7.5 / sum of hours and travel and so on as the month gets older

[oeldere]

Then explain why you not getting this result on this moment; since I think you already have this result.

2nd you excel is full of merged cells.

I advise you not to work with merged cells, you get in trouble with it sooner or later.

In your case I guess it will be sooner.

[dougers1]

ok so if i enter hours worked and travel time data for day 1 for all 14 names
hours worked 5
travel time 1
percentage should be measured as the month goes on
day 1 = hours available 14*7.5 = 105 hours/ 14 *6 (5 hours worked & 1 hour travel time)
= 80 %
AN shows as 3.38 % as it measureing it against hours in the month that are not available as yet if it still only day 1 in the month

[oeldere]

I changed the formula in cel AJ7 and aj 12
AJ7= SUM(AJ5:AJ6)/7,5
AP5= COUNTIF(E5:AI5,">0")
AP2 =SUMIF(AK4:AK72,"Hours Worked",$AP$4:$AP$72)*7,5

AQ2 =AL2/AP2

See the attached file

Please reply

[dougers1]

Thank you - this helped me .

[oeldere]

You're welcome. We appreciate the feedback!

Thanks for marking the question solved.

It'd also be appreciated if you were to click the add Reputation button at the foot of any of the posts of those who helped you reach a solution.