Please help, im trying to get excel to return a number if the amount entered falls between 2 set amounts. Example below.

?1-?1000 3 Days
?1001-?2500 5 Days
?2501-?5000 10 Days
?5001-?9999 20 Days
?10,000 + 30 Days

So if an amount is entered, say ?4500 it would return 10 Days.

I managed to get a formula kind of working but it doesnt seem to work all the time.

=IF(A210000,"30 Days",IF(A25000,"20 Days",IF(A22500,"10 Days",IF(A21000,"5 Days"))))

Can someone please help as this is quite urgent.

thanks

Lee


EggHeadCafe - Software Developer Portal of Choice
EBCDIC Encoding with .NET
http://www.eggheadcafe.com/tutorials...-with-net.aspx

Try this Vlookup formula.

=VLOOKUP(E1,B1:C5,2,2)

Whe
E1 contains the value to lookup, (1 to over 10,000)
B1:B5 contains 1, 1000, 2500, 5000, 9999.
C1:C5 contains 3, 5, 10, 20, 30, (number of days)

HTH
Regards,
Howard

<lee bexton wrote in message ...
Please help, im trying to get excel to return a number if the amount
entered falls between 2 set amounts. Example below.

?1-?1000 3 Days
?1001-?2500 5 Days
?2501-?5000 10 Days
?5001-?9999 20 Days
?10,000 + 30 Days

So if an amount is entered, say ?4500 it would return 10 Days.

I managed to get a formula kind of working but it doesnt seem to work all
the time.

=IF(A210000,"30 Days",IF(A25000,"20 Days",IF(A22500,"10
Days",IF(A21000,"5 Days"))))

Can someone please help as this is quite urgent.

thanks

Lee


EggHeadCafe - Software Developer Portal of Choice
EBCDIC Encoding with .NET
http://www.eggheadcafe.com/tutorials...-with-net.aspx

Try something like:

=IF(A20,LOOKUP(A2,{1,1001,2501,5001,10000},{3,5,1 0,20,30}),"")

lee bexton wrote:
Please help, im trying to get excel to return a number if the amount entered falls between 2 set amounts. Example below.

?1-?1000 3 Days
?1001-?2500 5 Days
?2501-?5000 10 Days
?5001-?9999 20 Days
?10,000 + 30 Days

So if an amount is entered, say ?4500 it would return 10 Days.

I managed to get a formula kind of working but it doesnt seem to work all the time.

=IF(A210000,"30 Days",IF(A25000,"20 Days",IF(A22500,"10 Days",IF(A21000,"5 Days"))))

Can someone please help as this is quite urgent.

thanks

Lee


EggHeadCafe - Software Developer Portal of Choice
EBCDIC Encoding with .NET
http://www.eggheadcafe.com/tutorials...-with-net.aspx

Well, "it doesnt seem to work all the time" isn't a very specific problem
description. Perhaps you could tell us what value in A2 gives you what
result, and what result you expected?
You haven't included the 3 day result, so perhaps you intended:
=IF(A210000,"30 Days",IF(A25000,"20 Days",IF(A22500,"10
Days",IF(A21000,"5 Days","3 Days"))))
One cause of problems would be if the value in A2 were text instead of a
number. If you are having problems, see what you get from =ISTEXT(A2) and
=ISNUMBER(A2).
--
David Biddulph

<lee bexton wrote in message ...
Please help, im trying to get excel to return a number if the amount
entered falls between 2 set amounts. Example below.

?1-?1000 3 Days
?1001-?2500 5 Days
?2501-?5000 10 Days
?5001-?9999 20 Days
?10,000 + 30 Days

So if an amount is entered, say ?4500 it would return 10 Days.

I managed to get a formula kind of working but it doesnt seem to work all
the time.

=IF(A210000,"30 Days",IF(A25000,"20 Days",IF(A22500,"10
Days",IF(A21000,"5 Days"))))

Can someone please help as this is quite urgent.

thanks

Lee


EggHeadCafe - Software Developer Portal of Choice
EBCDIC Encoding with .NET
http://www.eggheadcafe.com/tutorials...-with-net.aspx