Incorrect formula

[Mel]

Hi,

I have the following but have made mistakes in the formula.

=IF(OR($P$53>499,L34="D",L34="P"),P34*10%,IF(OR($P$53>499,L34="D",L34="Pre",N34="Y"),P34*5%,)*NOT($R$35="NT")*NOT($R$36="NP"))

What I want to achieve is this.

In Cell where formula is. will be nothing, or 10% or 5%.

If a "N" is in Cell N34 and P53>499, or L53 contains a "D" or "P" then the
result is 10% but if a "Y" in Cell N34 then 5%, if neither a N or Y then
result is nothing. Again if R35 is "NT" or R36 is "NP" it overrides the
formula to have nothing.

Thanks for any help
Mel

 

[Shane Devenshire]

Hi,

I think this captures what you want:

=IF(OR(R35="NT",R36="NP"),"",IF(OR(AND(N34="N",P53>499),L53="D",L53="P"),10%,IF(N34="Y",5%,"")))

 

[Mel]

Hi Shane,

No this does not give me the P34 by the 5 or 10 percent.

Thanks
Mel

 

[Gary Keramidas]

I'm not sure I understand, but look at this. then select the cell with this
formula, and choose tools/formula auditing/evaluate formula. then click
evaluate and watch each expression.

=IF(OR(R35="NT",R36="NP"),"",IF(AND(OR($P$53>499,L34="D",L34="Pre"),N34="Y"),P34*5%,IF(OR($P$53>499,L34="D",L34="P"),P34*10%,"other")))

 

[Shane Devenshire]

Hi,

The problem is that the formula you present and the description of what you
want it to do, do not match? So I wrote the formula based on your verbal
description.

You tell us that the first formula doesn't, and then you describe what you
want it to do. Do you want us to follow what you say doesn't work or what
you say you want?

 

[Shane Devenshire]

Hi,

If all that is wrong is the formula is not multiplying the 5% or 10% by P34,
just change the formula to read:

=IF(OR(R35="NT",R36="NP"),"",IF(OR(AND(N34="N",P53>499),L53="D",L53="P"),P34*10%,IF(N34="Y",P34*5%,"")))

Regarding your original formula:

=IF(OR($P$53>499,L34="D",L34="P"),P34*10%,IF(OR($P$53>499,L34="D",L34="Pre",N34="Y"),P34*5%,)*NOT($R$35="NT")*NOT($R$36="NP"))

NOT(R35="NT")*NOT(R36="NP") is an AND condition not an OR condition.
Also you have a L34="Pre" test in your formula which is not described in
your description of what you want the formula to do?

 

[Mel]

Oh, I apologise, I changed the one "pre" to just a "P" in the first part and
not the other when I submitted my formula. I had been working for 4 hours
without a break, and a migraine, my apologies Shane. I will try the formulas
when I wake tomorrow and thank all for their help.

Warm regards
Mel