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

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

2. ## 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)

3. ## 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!

#### Thread Information

##### Users Browsing this Thread

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