issue with IF statement

[xtreme07]

Hi,

i need to create a formula for the below

IF(E12>E13 AND E12>=1000, do some math, ELSE "100")

note that
- E12 might be bigger than E13, but not bigger than 1000; in which case i don't know how to deal with this. maybe you know
- the math will only be made if E12 meets the E12>E13 and meets E12>=1000

any ideas how to do this ?

 

[MrKowz]

Something like:

=IF(AND(E12>E13,E12>=1000),*do math*,100)

 

[xtreme07]

Something like:

=IF(AND(E12>E13,E12>=1000),*do math*,100)

it prints 100 no matter the numbers for E12 AND E13.
if you take a look at my note from 1st post you'll see i said
- E12 might be bigger than E13, but not bigger than 1000; in which case i don't know how to deal with this. maybe you know

i said that because the formula has 2 big statements
1st statement: if E12>E13, where it's possible that the proposition might be false, in which case the math won't be done. and more than that the formula get's only one chance to express it self and that is 100
2nd statement: if E12>=1000. same as 1st statement.

so there's 2 statements, from which one can go false. so even if one is true the output will be false.

i hope this sheds some light .. and maybe helps on developing a better formula


it should probably look somthing like

if e12>e13, then look at the e12 to see if it's bigger or equal to 1000. if that's the case, than do the math, else print 100

 

[MrKowz]

I don't quite understand what you are explaining, because from what I understood in both explanations, my formula should fit. Can you please provide some examples of your input and desired output?

 

[xtreme07]

I don't quite understand what you are explaining, because from what I understood in both explanations, my formula should fit. Can you please provide some examples of your input and desired output?

you are right, what you did is good. it's me though that missed some things.
i'm now working on making clear guides with examples on what i need.

it will take me some time ... up to 10 min. i hope you can wait that long :d

 

[danzon]

Sounds like you just need to alter the formula provided... replacing AND with OR

if I am understanding correctly

 

[xtreme07]

i've uploaded the file to mediafire
http://www.mediafire.com/?9yofv2617bmmpxd

the file is in excel 97-2003 format and can be opened with any version of excel

the whole explanation + examples can be seen in that file.

 

[xtreme07]

I pretty much managed to solve the problem.
there's one thing left to it
- replacing the standard FALSE output with 0

formulas

=IF(AND(A1>A2,A1<=1000),(A1*100)/1000,IF(AND(A1=A2,A1>=1000),100,IF(AND(A1=A2,A1<=1000),(A1*100)/1000,IF(AND(A1>A2,A1>1000),100)))
=IF(AND(A2>A1,A2<=1000),(A2*100)/1000,IF(AND(A1=A2,A2>=1000),100,IF(AND(A2>A1,A2>1000),100)))

they are two for each row one.
1st for top row
2nd for bottom row

 

[xtreme07]

problem solved
ty for your help. if meanwhile you started the work on the formula(s) i can only appreciate your help and thank you for involving.

final formulas
=IF(AND(A1>A2,A1<=1000),(A1*100)/1000,IF(AND(A1=A2,A1>=1000),100,IF(AND(A1=A2,A1<=1000),(A1*100)/1000,IF(AND(A1>A2,A1>1000),100,0))))
=IF(AND(A2>A1,A2<=1000),(A2*100)/1000,IF(AND(A1=A2,0),100,IF(AND(A2>A1,A2>1000),100,0)))


for those who lost their eyes in the formulas: there's 4 statements in 1st formula and 3 statements in 2nd formula.