PDA

View Full Version : Solved: need a magic formula to ..



neditheg
02-04-2011, 08:00 AM
hello guys :)
I've attached an excel file because I really need your help.

Here is my situation.

I have plenty employees, and I want to calculate their holiday right for a month.

In that excel you will find out this columns :
-> Contractual Hours -- in this column I have the contractual hours for each agent;
-> Holyday right -- here I need the magic formula ;
-> Hours to work -- here I have the hours which an agent must work to be paid with the full salary, and to get the maximum holiday right for a month which in my country is 1.75 days ;
-> Worked Hours -- here I have the amount of hours worked by each agent the passed month, in this case January.
-> in the Q1 cell I have the amount of working days for the month.

Other things you need to know :

I have agents which with this types of contracts 2,3,4,6, and 8 hours/day.
The amount of "Hours to work"="contractual hours" * "Working Days" with two exceptions:
-> For an agent who has a contract of 6 hours/day the amount of "Hours to work"="contractual hours" * "Working Days"+6 ;
-> For an agent who has a contract of 4 hours/day the amount of "Hours to work"="contractual hours" * "Working Days"+8

To calculate the amount of "Hours to work" I'm using this formula

=IF(N4=8;N4*$Q$1;IF(N4=3;N4*$Q$1;IF(N4=6;N4*$Q$1+6;IF(N4=4;N4*$Q$1+8;IF(N4= 2;N4*$Q$1))))).

To calculate the "Holiday Right" for a month for each agent I have to follow this steps
1) - verify what contract has each agent
2) - compare the "Hours to work" values with "worked hours" values

I) For 2,3 and 8 hours contracts I have this situations:

a) "Hours to work" > "Worked hours"

-> In this case the formula is

"Holiday right"=(1.75 / "working days") / "contractual hours" * "worked hours"

b) "Hours to work" <= "Worked hours"

"Holiday right"=(1.75 / "working days") / "contractual hours" * "Hours to work"

The formula which I could use for this cases is


=IF(($P4>$Q4);(1.75/$Q$1)/$N4*$Q4;(1.75/$Q$1)/$N4*$P4)
II) For an agent with an 4 hours/day contract

a) ("Hours to work"- 8 ) > "Worked hours"

"holiday right"=(1.75/ ("working days-8")) / "contractual hours" * "worked hours"

b) ("Hours to work"- 8 ) < = "Worked hours"

"holiday right"=(1.75/ ("working days-8")) / "contractual hours" * "Hours to work"

formula is :


=IF((P7-8>Q7);(1.75/$Q$1)/N7*Q7;(1.75/$Q$1)/N7*(P7-8))

III) For an agent with an 6 hours/day contract

a) ("Hours to work"- 6) > "Worked hours"

"holiday right"=(1.75/ ("working days-6")) / "contractual hours" * "worked hours"

b) ("Hours to work"- 6 ) < = "Worked hours"

"holiday right"=(1.75/ ("working days-6")) / "contractual hours" * "Hours to work"

formula :

=IF((P13-6>Q13);(1.75/$Q$1)/N13*Q13;(1.75/$Q$1)/N13*(P13-6))


Now I want a formula which can group all this cases ... I've tried a nasted if combination ...without succes --the error message was "you used to many arguments for this function"

Now I'm asking you guys for some help.

:whip:whip:whip

neditheg
02-04-2011, 08:02 AM
now i've attached book2.xls ..with the results I want to get using the magic formula :)

shrivallabha
02-04-2011, 09:39 PM
Try the formula below.


=IF(AND(IF(N4<8,TRUE,FALSE),IF(N4>3,TRUE,FALSE)),IF(N4=4,IF(P4-8<Q4,"1.7500",1.75/((P4-8)/Q4)),IF(P4-6<Q4,"1.7500",1.75/((P4-6)/Q4))),IF(Q4>P4,"1.7500",1.75/(P4/Q4)))

See column R in the attachment! Barring the Sr. No. 6, it works fine.

neditheg
02-06-2011, 04:54 AM
thanks a lot! :) now I'm testing your solution.

TY!