Excel Function Question?

Question

I am wanting to do a formula for the following:
If M10>="100000" and O10>=".1" OR IF M>="100000" and O10<="-.1" OR IF M<="-100000" and O10>=".1" OR IF M<="-100000" AND O10>=".1" then "Yes" otherwise "No".

I have tried to write it but keep getting #Value. I am testing variances and this will tell me if I have a variance or not - any help would be appreciated! :)

Answer 1

Here's the proper syntax. I tested it and it does work.

=IF(OR(AND(M10>=100000, O10>=0.1), AND(M10>=100000, O10<=-0.1), AND(M10<=-100000, O10>=0.1), AND(M10<=-100000, O10>=0.1)), "YES", "NO")

Please note above formula has embedded spaces so that it will display properly in Yahoo. Excel should ignore them but if not, just paste into Notepad and replace spaces with nulls.

Anyway, I corrected the synax by using notepad. I find using notepad to be very helpful when working out logic of very long formulas.

Hope this helps.
Kind Regards,
QwertyKPH @ Yahoo !

Answer 2

Try using user defined functions. It will be clearer to understand.

function findvariance(cellbig, cellsmall)
dim bigno
dim smallno
bigno = 10000
smallno = .1
findvariance = false
if cellbig >= bigno and cellsmall >=smallno then
findvariance = true
endif
if cellbig >= bigno and cellsmall <=smallno then
findvariance = true
endif
if cellbig <= -bigno and cellsmall <=smallno then
findvariance = true
endif
if cellbig <= -bigno and cellsmall >=smallno then
findvariance = true
endif
end function

Answer 3

change all your "or" in your functions to Else and that should get you on your way, this is QUITE a long nested if function

Answer 4

remove the quotes on your numeric compares?

Answer 5

maybe add some brackets to clarify?