+ Reply to Thread
Results 1 to 6 of 6

Multiply One Cell by Another to a Maximum Value

  1. #1
    Registered User
    Join Date
    01-18-2019
    Location
    Ireland
    MS-Off Ver
    MS Office 2013
    Posts
    8

    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.
    Last edited by PiaBang; 05-15-2019 at 11:58 AM. Reason: solved

  2. #2
    Forum Contributor
    Join Date
    11-02-2016
    Location
    NY
    MS-Off Ver
    2010
    Posts
    323

    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. #3
    Registered User
    Join Date
    01-18-2019
    Location
    Ireland
    MS-Off Ver
    MS Office 2013
    Posts
    8

    Re: Multiply One Cell by Another to a Maximum Value

    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.
    Last edited by PiaBang; 05-15-2019 at 10:21 AM.

  4. #4
    Valued Forum Contributor
    Join Date
    01-07-2013
    Location
    Michigan
    MS-Off Ver
    O365
    Posts
    601

    Re: Multiply One Cell by Another to a Maximum Value

    Try:
    Please Login or Register  to view this content.
    If someone helped achieve your solution, consider clicking "* Add Reputation" on their post.

    If your question has been answered, mark the thread as [SOLVED] using the Thread Tools menu at the top of the thread.

  5. #5
    Registered User
    Join Date
    01-18-2019
    Location
    Ireland
    MS-Off Ver
    MS Office 2013
    Posts
    8

    Re: Multiply One Cell by Another to a Maximum Value

    Super, thank you, that has worked perfectly.

  6. #6
    Valued Forum Contributor
    Join Date
    01-07-2013
    Location
    Michigan
    MS-Off Ver
    O365
    Posts
    601

    Re: Multiply One Cell by Another to a Maximum Value

    You're welcome, happy to help!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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