Multiple If Statements

[Tigerwould]

I'm trying to return multiple results using IF function for the following.

If A1 is greater that 0.01 and less than 2.50 then enter 3.99, but if A1 is
greater than 2.50 and less than 3.50 then enter 4.99.

I can do it for one:

=IF(AND(J2>2.50,J2<3.50),"4.99")

But not multiples.

Help!

 

[Bernard Liengme]

=IF(AND(A1>0.01,A1<2.50),3.99,IF(AND(A1>2.50,A1<3.50),4.99,"NO REPLY"))
or remove NO REPLY and have an empty ""

If you can live with zero when neither case applies:

=3.99*AND(A1>0.01,A1<2.50) + 4.99*(AND(A1>2.50,A1<3.50))

best wishes

 

[Arvi Laanemets]

Hi

There are several options.

1. There exist a certain pattern in both source and result values. P.e.
limit values are 2.50, 3.50, 4.50, ...etc. and according return values are
3.99, 4.99, 5.50, ... etc., then this formula will do:
=3.99+INT(MAX(0,A1-1.5000000001))


2. There isn't any pattern, or the formula will be too complex, and the
number of values to check is too high, then use lookup table and VLOOKUP
function (only way when you have more than 29 values to check):

On separate sheet, enter the lookup table
LowerLimit Response
-9999999999 0
0.0100000001 3.99
2.50500000001 4.99
....

, and then use the formula:
=VLOOKUP(A1,LookupTable,2,1)


3. When number of lookup values isn't very big, you can use same VLOOKUP
formula with lookup values entered directly as an array (but only, when your
regional settings allow this - p.e. I myself can't use this as my regional
settings have ; as parameter delimiter)
=VLOOKUP(A1,{-9999999999;0,0.0100000001;3.99,2.5000000001;4.99,....},2,1)

4. When the number of lookup values doesn't exceed 29, you can combine
CHOOSE and MATCH functions:
=CHOOSE(MATCH(A1,{-99999;0.01000001;2.5000001;...},1),0,3.99,4.99,...)

5. And at least, when the number of lookup values doesn't exceed 8, you can
use IF function:
=IF(A1<=0.01,0,IF(A1<=2.5,3.99,IF(...,...,...)))

 

[Tigerwould]

What if I wanted to add more results?

I can't seem to get it to work by adding + 5.99*(AND(A1>3.50,A1<4.50)

 

[Tigerwould]

Sorry, these seems to have got too complicated than I needed.

Basically I want to enter a result when a cell is between two values.

ie.

Enter "Value" when A1 is more than 0 but less than 2 or enter "another
Value" when A1 is more than 2 but less than 3. And so on. I can live with
having the result "value" being 0 if not in one of the ranges.

The "value" doesn't have to be a numerical one.

Is this possible?

Thanks

 

[Pete_UK]

You need to add a closed bracket at the end. Also, it might be better
to make the first terms >=, so that you include when A1=3.5, or 2.5
etc:

+ 5.99*(AND(A1>=3.50,A1<4.50))

Hope this helps.

Pete

 

[Bernard Liengme]

Do you have it working now?
If you want many terms then the LOOKUP method is best - it really is not too
complex
Send me (my private email) a table line this

Min Max Value
..01 2.5 3.99
2.5 3.5 4.99
etc

But if the steps are always 1.0 (ie the max is 2.5, 3.5, 4.5, 5.5, 6.5...)
I'm sure we can make a simple math equation

best wishes