+ Reply to Thread
Results 1 to 3 of 3

Need to Limit the Sum of a list of values to a Minimum and Maximum Value

  1. #1
    Registered User
    Join Date
    09-09-2016
    Location
    Hilton Head
    MS-Off Ver
    365
    Posts
    2

    Need to Limit the Sum of a list of values to a Minimum and Maximum Value

    This workbook is ultimately going to be surety bond rating model that will be used to underwrite and price contract surety bonds.

    The Worksheet titled "Rating Rules" contains a series of underwriting rules, the total for which are intended to consecutively modify the Base Rate value in cell D1. However, the values calculated in Cell C15, C26, and C34 need to be restricted within a specified range with the minimum value being -25% and the maximum value being +25%.

    Additionally, the TOTAL Value for RULE 2 in cell C35 needs to be restricted within a specified range with the minimum value being -40% and the maximum value being +40%.

    Can someone please provide some insight regarding the formula that needs to be entered into Cells C15, C26, C34, and C35 in order to yield the desired result?

    Thanks in advance for your assistance.
    Attached Files Attached Files

  2. #2
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Need to Limit the Sum of a list of values to a Minimum and Maximum Value

    Hi,

    To restrict the values you may use MIN and MAX

    =MAX(lower threshold,MIN(higher threshold,your existing formula))

    You may also use MEDIAN

    =MEDIAN(lower threshold,higher threshold,your existing formula)
    Don
    Please remember to mark your thread 'Solved' when appropriate.

  3. #3
    Registered User
    Join Date
    09-09-2016
    Location
    Hilton Head
    MS-Off Ver
    365
    Posts
    2

    Re: Need to Limit the Sum of a list of values to a Minimum and Maximum Value

    Thank you very much for your quick response. It worked perfectly!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. the maximum and minimum limit
    By madhupangal in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 12-07-2015, 05:49 PM
  2. minimum value out of maximum values
    By shamym in forum Excel General
    Replies: 9
    Last Post: 01-21-2015, 05:42 AM
  3. VBA to set Minimum and Maximum Values of X Axis
    By astha.malik1 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 03-18-2014, 04:42 AM
  4. pulling out minimum and maximum values..!
    By via135 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 02-04-2011, 11:05 AM
  5. Maximum & Minimum values in a coulmn
    By balmalik in forum Excel General
    Replies: 7
    Last Post: 07-21-2006, 05:35 AM

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