simple calculation multiple steps and is difficult to explain

[Jellyroll1970]

Here is what i want a formula to do:


If (A1>=B1 = A1x5% and If A1>=B3 = A1x5.25% and If A1>=B2 = A1x4.75% and If A1<B2 = A1x0 Yet if A1 is equal to the distance between B1 through B2 = A1x5%). All in one formula.


Make sense?

 

[oldchippy]

Hi Jellyroll1970,


Yet if A1 is equal to the distance between B1 through B2


What does that mean exactly?

B1-B2

B2-B1

B1-B2+1

B2-B1+1

 

[Jellyroll1970]

I will put this into actual figures:

A1 = 101

B1 = 91

B2 = 101

B3 = 111


The distance between B1 - B2 is 92, 93, 94, 95, 96, 97, 98, 99 and 100.

If 101 was equal to or above 111 then 101 x 5.25

If 101 was below B1 then 101 x 0.00

If 101 is between B1 and B2 then 101 x 5.0

If 101 was equal to or between B2 and B3 then 101 x 5.25

 

[oldchippy]

Hi,


Sorry for the delay in getting back to you, something cropped up.


In your first question you also had this one


If 101 is greater than or equal to B2 then 101 x 4.75%


Now if thats the case then it would clash with


If 101 was equal to or between B2 and B3 then 101 x 5.25%


So which is correct?

 

[Jellyroll1970]

let me rephrase again...difficult for ME to explain...I speak for a living and am very thankful you are here because numbers are a big challenge for me.


If 101 is greater than or equal to B2 yet less than B3 then 101 x 5.0%

If 101 is greater than B3 then 101 x 5.25%

If 101 is greater than or equal to B1 yet less than B2 then 101 x 4.75%

If 101 is less than B1 then "0.00"


Basically, if "actual" is 90% of goal = actual x 4.75%; 100% of goal = actual x 5.0% and 110% of goal = actual x 5.25%. Between 90% and 100% = actual x 4.75; between 100% and 110% = actual x 5.0% yet over 110% is still actual x 5.25%.

 

[VaraK]

Hi,


Try this:


=IF(A1<B1,0,IF(AND(A1>=B1,A1<B2),A1*5,A1*5.25))


Hope this helps.

 

[oldchippy]

Hi,


Or try this


=IF(AND(A1>=B2,A1<B3),A1*5,IF(A1>=B3,A1*5.25,IF(AND(A1>=B1,A1<B2),A1*4.75,IF(A1<B1,0))))


I'm assuming "actual" is A1 and "goal" is B2

 

[Jellyroll1970]

Thank you very much! It works perfectly.

I hope you have a great weekend.


Thanks again