mercredi 30 septembre 2015

Challenging Formula in excel to calculate successive remainder

I am trying to find a formula which will work out an allowance amount based on the time it has taken for a task to be completed.

Here is an example, in cell:

A1: I have an allowance value of 150 (which does not change)

B1: I have the time it has taken for the task to be performed

C1: I want a formula to return a value based on the criteria below

Criteria 1: 6hrs or less return 20% of the allowance =IF(AND(B1>=0.01,B1<=5.99),A1*0.2,"")

Criteria 2: Between 6hrs and 12hrs, return 50% of the allowance =IF(AND(B1>=6,B1<=11.99),A1*0.5,"")

Criteria 3: Between 12hrs and 24hrs, return 100% of the allowance =IF(AND(B1>=12,B1<=23.99),A1*1,"")

Criteria 4: First 24hrs, 100% of the allowance and for each successive 12hrs add 50% of the allowance (on top of the 100% for the first 24hrs)

If possible I would like one formula to cover all those criterias into C1 cell. As you can see, I have a formula for the first three criterias but not the last one.

It would be great if someone can help. Many thanks in advance :)

Ab

Aucun commentaire:

Enregistrer un commentaire