Seeking Advice Regarding Formulas for Rebate Template

[Tsg295]

Hello,

After much frustration in my attempt to get this file set up correctly I have come here for help and unable to get what I am looking for. The order and flow of the formulas are just confusing me and getting errors.

This is a rebate proposal template to be filled out for customers. They have 4 proposed sales tiers they can hit to get a certain rebate percent. Columns M through Q are parameter checks to ensure the input passes the test.

Formulas I am going for:

Cell M3= IF B3=“Y”, and M3 >=$500,000, then “Y”. OR IF B3=”Y”, and M3<$499,999, then “N”

Cell N3= IF M3>$2,000,000, and J3<=4%, then “Y”. OR IF M3<$2,000,000, and J3>=4%, then “N”
OR IF M3>$2,000,000, and K3<=4%, then “Y”. OR IF M3<$2,000,000, and K3>=4%, then “N”

Cell O3(Same as N3)= IF M3>$2,000,000, and I3<=4%, then “Y”. OR IF M3<$2,000,000, and I3>=4%, then “N”
OR IF M3>$2,000,000, and J3<=4%, then “Y”. OR IF M3<$2,000,000, and J3>=4%, then “N”
OR IF M3>$2,000,000, and K3<=4%, then “Y”. OR IF M3<$2,000,000, and K3>=4%, then “N”

Cell P3= IF I3>4% then “N”, OR IF I3<3.99% then “Y”
OR IF J3>4% then “N”, OR IF J3<3.99% then “Y”
OR IF K3>4% then “N”, OR IF K3<3.99% then “Y”

Cell Q3= IF W3>H3+.5% then “N”, OR IF W3<H3+.5% then “Y”
OR IF X3>I3+.5% then “N”, OR IF X3<I3+.5% then “Y”
OR IF Y3>J3+.5% then “N”, OR IF Y3<J3+.5% then “Y”
OR IF Z3>K3+.5% then “N”, OR IF Z3<K3+.5% then “Y”

Thank you for any help or insight!! It is greatly appreciated!

Tanner

[Squeaky]

Hi. First issue. You cannot have a formula in a cell that refers to itself. "Cell M3= IF B3=“Y”, and M3 >=$500,000"... I assume you meant L3 in all instances because that is where the $495,000 is. The next issue is in that first set, there is no resolution for if B3="N". FALSE will result. I have set the formula to say N when B3 = N. In cell M3 =IF(B3="Y",IF(L3>=500000,"Y","N"),"N")
Also, in B 1 I changed your Y or N to ' Y or N That's hash-space Y or N I put the hash and space in front of New Rebate as well. I did this because Excel will auto populate B3 when you enter N with "New Rebate", and will auto populate "Y or N" when you enter "Y". The hash-space trick avoids that issue. I cannot do more now. Gotta go. I will check in Monday.

[Squeaky]

For the second formula the problem here is what happens when both the first set and the second set are false? Like when you get $3,000,000 and 5%, or $1,000,000 and 2%?

[Tsg295]

Thank you! This information helps! Almost there.

One Last issue I am having trouble accounting for regarding my parameter check in column Q. Lets say column Z is blank, meaning they had no rate last year. However they want to add a rate this year to say 4%. Well Excel thinks I am going from blank to 4% which is greater than the .5% allowed. Therefore getting a N for that check.

Is there a way Excel could ignore the blank value in column Z and not give me a N for my parameter check? You can see on my file hopefully what I am trying to accomplish.

Thank you!!

[Squeaky]

I need a bit more information maybe. The formula for the Z check looks at K. In your example spreadsheet K says 4%. If you put 4% in Z there is not a greater than .5 difference. I need you to set the scenario. Will K be zero as well as Z and then you add 4% to Z? If K and Z are blank and you are adding to Z only, in the Z line you can add an if statement before the current if statement that if K equals zero, that will resolve to Y, otherwise the rest of your current OR IF Z3 statement will result.

Ok, i noticed your other attachment so I downloaded it and now I understand. Try IF(AND(H3<W3+0.5%,I3<X3+0.5%,J3<Y3+0.5%,OR(K3<Z3+0.5%,Z3="")),"Y","N")
Let me know if that works. I added an OR statement to the last part of the AND equation that deals with K3<z3. if z3 is equal to a blank, it will provide a true statement for that part of the AND equation.