Available hours calculation in Utilization!!!

Hi Brains,

I need to work out utilization of particular department in our company.
Normally we get a report with the following informations from the system :

Actual hours spent by an resource :XXX
Hours need to be billed :xxx

But i need another column ->Available hours ( calculation based )

Utilization formula would be billed hours / Available hours * 100

Available hours

Availalble hours workings should be as follows :

Resource who works on onsite - 8 hours per Day
Resource who works on offshore - 8.75 hours per Day

A resource may work on onsite for few days or he/she may be full month onsite ( or ) fully offshore.

Working should be hours based & project based

Attached worksheet for further understanding.

Eagerly waiting for reply

:bump::bump:

Hi

How did you get the 35 hours in Example!E4? Just seems to be entered. If so then are you only trying to find the result that should go into E6?

Perhaps update your example and explain how each of the numbers in column E is determined.


rylo

Oops wrongs illustrated.
Please find the revised example (E17 column will be 96.25 hours ) and sorry for the confusion.

Let me know whether you clarified?

Hi

Using sheet example
E16: =(20-SUMPRODUCT(--(C3:C6="Onsite"),(D3:D6))/8)*8.75
E4: =ROUND(D4/(D4+D6)*E16,0)
E6: =E16-E4
E6: change the format to general.

Or
E6: =ROUND(D4/(D4+D6)*E16,0)
This will leave an outstanding available hours of 0.25...

If this is OK, then try to expand for sheet1. Come back with any questions.


rylo

Thanks Rylo for the reply.

But can you help me in a formula for Sheet1 where more number of employees & projects involved.

Can anybody help me

Hi

Using sheet1:
H1: =SUM(1/COUNTIF(A3:A10,A3:A10))*20
This formula is array entered. Hold down the shift and ctrl keys, then press enter.
E16: =(H1-SUMPRODUCT(--(C3:C10="Onsite"),(D3:D10))/8)*8.75
F3: =ROUND(IF(C3="Onsite",D3,D3/SUMPRODUCT(--($C$3:$C$10="offshore"),($D$3:$D$10))*$E$16),0)
Copy from F3 down to F10.

See how that goes.

rylo

I think it works & let me check.
Thank you very much rylo.

Originally Posted by rylo

Hi

Using sheet1:
H1: =SUM(1/COUNTIF(A3:A10,A3:A10))*20
This formula is array entered. Hold down the shift and ctrl keys, then press enter.
E16: =(H1-SUMPRODUCT(--(C3:C10="Onsite"),(D3:D10))/8)*8.75
F3: =ROUND(IF(C3="Onsite",D3,D3/SUMPRODUCT(--($C$3:$C$10="offshore"),($D$3:$D$10))*$E$16),0)
Copy from F3 down to F10.

See how that goes.

rylo