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(J22.50,J2<3.50),"4.99")

But not multiples.

Help!

=IF(AND(A10.01,A1<2.50),3.99,IF(AND(A12.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(A10.01,A1<2.50) + 4.99*(AND(A12.50,A1<3.50))

best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Tigerwould" wrote in message
...
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(J22.50,J2<3.50),"4.99")

But not multiples.

Help!

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(...,...,...)))


--
Arvi Laanemets
( My real mail address: arvi.laanemets<attarkon.ee )


"Tigerwould" wrote in message
...
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(J22.50,J2<3.50),"4.99")

But not multiples.

Help!

What if I wanted to add more results?

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

"Bernard Liengme" wrote:

=IF(AND(A10.01,A1<2.50),3.99,IF(AND(A12.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(A10.01,A1<2.50) + 4.99*(AND(A12.50,A1<3.50))

best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Tigerwould" wrote in message
...
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(J22.50,J2<3.50),"4.99")

But not multiples.

Help!

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

"Arvi Laanemets" wrote:

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(...,...,...)))


--
Arvi Laanemets
( My real mail address: arvi.laanemets<attarkon.ee )


"Tigerwould" wrote in message
...
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(J22.50,J2<3.50),"4.99")

But not multiples.

Help!

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

On May 16, 11:03*am, Tigerwould
wrote:
What if I wanted to add more results?

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



"Bernard Liengme" wrote:
=IF(AND(A10.01,A1<2.50),3.99,IF(AND(A12.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(A10.01,A1<2.50) + 4.99*(AND(A12.50,A1<3.50))

best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Tigerwould" wrote in message
...
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(J22.50,J2<3.50),"4.99")

But not multiples.

Help!- Hide quoted text -

- Show quoted text -

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
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Tigerwould" wrote in message
...
What if I wanted to add more results?

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

"Bernard Liengme" wrote:

=IF(AND(A10.01,A1<2.50),3.99,IF(AND(A12.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(A10.01,A1<2.50) + 4.99*(AND(A12.50,A1<3.50))

best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Tigerwould" wrote in message
...
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(J22.50,J2<3.50),"4.99")

But not multiples.

Help!