Formula: % Triggers

[andyb16]

Hi All,

I am having a tough time building a formula..

Here are the requirements:

Base Exchange Rate = 45

Average Exchange rate = "can be any number"

Now there is to no action to be taken if average exchange rate is within +-5%(floor and ceiling) of the base exchange rate. However, once the base ex rate falls below either side of 5% then the formula should calculate the % difference between the average exchange rate and the floor or ceiling value(42.75 or 47.25) as the case maybe.

Please help!

Thanks.

 

[SydneyGeek]

Assuming the average rate is in B1, with the comparison rates starting in B3:

C3 is

=(ABS(B3-$B$1) > =0.05)*(B3-$B$1)

Without the spaces. They are there to stop the forum software treating > and < as tags.

Denis

 

[andyb16]

Thanks Denis,

Its working...On this code, what does (B3-$B$1) > .05 do? I mean, its not an if function, therefore i am confused how it tests for a range? and why we multiply its result again to the difference?

=(ABS(B3-$B$1) > =0.05)*(B3-$B$1)

 

[SydneyGeek]

Hi Andy,

The first part of the formula tests that the difference is > = 0.05.
That turns into TRUE and FALSE, which Excel interprets as 1 and 0. When that is multiplied by the difference, you only get a non-zero value where the difference is outside the threshhold.

Denis

 

[andyb16]

Thanks Denis