Multiply One Cell by Another to a Maximum Value

Hi All,

This seems like it should be an easy formula but I've googled and searched here and can't seem to find anything.

We have a number of employees who work between 5-6 (occasionally more) shifts per week. There are a couple of shift allowances that we can pay to a maximum of 5 per week.


So for e.g.
Employee A has 6 shifts with a shift allowance of 10.00 per shift to a maximum of 5 shifts = maximum 50
Employee B has 6 shifts with a shift allowance of 20.00 per shift to a maximum of 5 shifts = maximum 100

What would the formula be to calculate number of shifts x the shift allowance to a maximum of 5?

Appreciate any assistance with this.

PiaBang.

Thanks for your reply.

I'd rather not use different formulas for each employee given that we have a range of different shift allowance rates and 150 employees (I just used 2 for illustrative purposes).

Is it possible to have a formula such as:

= sum (Cell b2 x Cell c2) to a maximum of (5 x c2)

There will also be occasions where the employee works only 1-5 shifts and will get the appropriate shift allowance for that, so it's not the max allowance or zero, it's just that it's limited to a maximum of 5.

Try:

Please Login or Register  to view this content.

You're welcome, happy to help!