Calculating an amount in a range

[aaronhayes1982]

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!

 

[baitmaster]

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]

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

 

[baitmaster]

where threshold is higher than upper:
=(variable>=threshold)*(upper-lower)

 

[baitmaster]

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