General Help

[ehlfb]

Dear All,

I have in Cell A2 132:23 hours, I need to calculate in Cell B2 the following:

First 60 Hours at the rate of 3
Second 60 hours at the rate of 5
anything > 120 at the rate of 9

the total should be 591.45

Can anyone help me please?.

 

[ehlfb]

Thanks Peter,

It works perfectly. Really appreciate it.

Guys thanks for all of you.

 

[ehlfb]

Thanks guys,

Peter, thank you very much it works perfectly. Is it possiple to explain it to me as I cannot see the rates 3,5 and 9?

 

[Peter_SSs]

Is it possiple to explain it to me as I cannot see the rates 3,5 and 9?

I'll try. Instead of breaking into the 3 ranges and multiplying by 3, 5 and 9 I used this logic.

Everything is multiplied by at least 3 so I multiply everything by 3.

Now everything above 60 has to be multiplied by at least 5, but it has already been multiplied by 3, so I just multiply it by another 2.

Now everything above 120 has to be multiplied by 9, but it has already been multiplied by 5 (3 and 2), so I just multiply it by another 4.

The MAX in the last two sections ensures that nothing gets subtracted if there is nothing in that range.

Does that make sense?

 

[ehlfb]

Peter, I have to say that you are genius!

 

[Peter_SSs]

Peter, I have to say that you are genius!

That's very kind. I must remenber to tell my boss tomorrow.