# Calculating an amount in a range

#### aaronhayes1982

##### New Member
Hi All (newbie here!)

I need a formula that can determine the amount of a value that is between a set range, but only if it first exceeds a fixed threshold.

I.e.

If A (a variable amount) exceed B (the fixed threshold amount) then what amount of A is between X and Y

In other words, If 100 (a variable amount) is greater than 50 (the threshold amount) what amount of 100 is between 60 and 70? Answer = 10

If the variable amount was 66 then the answer should show 6.

If the Variable amount was >50 the answer should be 0 as is does not cross the threshold amount?

I have read a number of posts but don't see the answer to this particular problem.

Appreciate you help on this one!

### Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

#### baitmaster

##### Well-known Member
welcome to the board

will the threshold always be less than the lower limit? will it always be less than the upper limit? Could it exceed the upper limit?

#### baitmaster

##### Well-known Member
where threshold is less than lower:
=max(min(variable,upper)-lower,0)

#### baitmaster

##### Well-known Member
where threshold is higher than upper:
=(variable>=threshold)*(upper-lower)

#### baitmaster

##### Well-known Member
so to cover all circumstances you probably want
=(variable>=threshold)*max(min(variable,upper)-lower,0)

This hasn't been tested as my Excel is busy

Replies
5
Views
726
Replies
2
Views
622
Replies
0
Views
358
Replies
4
Views
372
Replies
3
Views
156

1,191,580
Messages
5,987,446
Members
440,097
Latest member
Wint

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back