Using an If formula to print different answer when a criteria is met.

[jupops]

Good Morning all

Can anybody help? I am trying to create a formula that will print a different answer when a certain criteria is met..

In this case, if the number in a cell falls between two numbers it will show an answer. I used the formula:

=IF(OR((A3>="E02-A01")*(A3<="E02-A99")),Data!A2,0)

But I need to add more criteria’s which will show different answers.. I tried this formula

=IF(OR((A4>="E02-A01")*(A4<="E02-A99"),Data!A2,(A4>="E05-A01")*(A4<="E06-A99")),Data!A3)

But no matter what was in A4, it would show what was in A3 on my data sheet.. I am nut sure what I am doing wrong, can you help?

Thank You

Regards

Jupops

 

[macropod]

Hi Jupops,

First off, the OR test in your original formula does nothing. What you have there is effectively an AND test nested inside a non-functional OR test. All you need is:
=IF(AND(A3>="E02-A01",A3<="E02-A99"),Data!A2,0)
or
=IF((A3>="E02-A01")*(A3<="E02-A99"),Data!A2,0)

With your second formula, you only ever get the Data!A3 result because you've enclosed the Data!A2 expression and the second set of condition inside the origibal OR test.

Presumably, you want something along the lines of:
IF Cond1=True THEN Data!A3 ELSE IF Cond2=True THEN Data!A2
where Cond1 and Cond2 are two AND tests. In that case, you need something like:
=IF(AND(A3>="E02-A01",A3<="E02-A99"),Data!A2,IF(AND(A4>="E05-A01",A4<="E06-A99"),Data!A3,0))
or
=IF((A3>="E02-A01")*(A3<="E02-A99"),Data!A2,IF((A4>="E05-A01")*(A4<="E06-A99"),Data!A3,0))

Cheers

 

[SkipVought]

Hi,

What values do you have in A3 & A4?

Do you really want to compare the value in A3 to the literal E02-A01

or

do you want the value in E2 subtracted from the value in A1 evaluated mathematically to return a value?

Skip,

_{[red]Be Advised![/red]
The band of elderly oriental musicians, known as Ground Cover, is, in reality...
Asian Jasmine!}