# Multiply One Cell by Another to a Maximum Value

1. ## 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.

2. ## Re: Multiply One Cell by Another to a Maximum Value

if(shift >=5 then 50 else 0 for Employee A; if(shift >=5 then 100 else 0 for Employee B.....given your limited info, thats what you have

3. ## Re: Multiply One Cell by Another to a Maximum Value

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.

4. ## Re: Multiply One Cell by Another to a Maximum Value

Try:
``Please Login or Register  to view this content.``

5. ## Re: Multiply One Cell by Another to a Maximum Value

Super, thank you, that has worked perfectly.

6. ## Re: Multiply One Cell by Another to a Maximum Value

You're welcome, happy to help!

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1