Consultant Break Even Worksheet/Formula Help

[andresndor]

Good afternoon. I was asked to track the activity of our consultants and report back what portion we are making gains/losses on. The way that it works is that they bill us a monthly retainer of $1500. Once they exceed 5 hours we keep the rest. The problem is that I can't seem to come up with a formula that populates the "Break Even" cell, this should be the amount of time 0-5 Hours*Cost Rate and the "Excess" cell for hours greater that 5*Cost Rate).

Hopefull this makes sense and provides enough info. Any help would be greatly appreciated.

Many thanks,

[ChemistB]

By "Excess" cell, you mean the "Overage" cell?
And Cost Rate is in Rates!D2?

In C5 dragged right
=MIN(C$8, 5) * Rates!$D$2
In C6 dragged right
=(MAX(C$8,5) - MIN(C$8,5))*Rates!$D$2
Do those work for you?

[Ace_XL]

In C5
=Rates!$D$2*MIN(Hours!C8,5)
copy across

In C6
=Rates!$D$2*(MAX(0,Hours!C8-5))
Copy across

[andresndor]

Chemist B & Ace_XL,

Thanks for both your responses. I used Ace's formula but it's good to see how you can use both versions. I know how to use the min/max functions but never considered it here. Many thanks and my appologies for the delayed response.