Check if Numer is less or great then a fixed number and return a v

J

Jean

Hi i am really having trouble to make the following work:
=IF(OR($F$9>28,$F$9<5),4,IF(OR($F$9>4,$F$9<13),12,IF(.....),20,0)))

Basically what i am trying to do is to find if F9 is between 29 and 4 then
return 4, if F9 is between 5 & 12 return 12, if it is between 13 & 20 return
20 and if it is between 21& 28 returns 28, (actually this is a billing cycle)
anyways to do that? or just change F9 to a date ?
 
R

Roger Govier

Hi Jean

The easiest way is to make a lookup table.
Insert the following into cells A1:B5
0 4
5 12
13 20
21 28
29 4


Then the formula is
=VLOOKUP(F9,$A$1:$B$5,2)

The table doesn't have to be in A1:B5, it can be anywhere, even on
another sheet.
Ideally, give the table a name and refer to that.
If you put the table on Sheet2 in cells A1:B5 then
Insert>Name>Define> Name Billdate Refers to =Sheet2!$A$1:$B$5

Then use
=VLOOKUP(F9,Billdate,2)
 
M

mikelee101

When you are trying to see if the number is between two numbers, use And()
instead of Or().

For example, if a number is between 1 and 10 (inclusive), you'd do it:

=And(f9>=1, f9<=10)

Hope that helps.
 
T

Toppers

=LOOKUP($F$9,{0,5,13,21,29},{4,12,20,28,4})


Roger Govier said:
Hi Jean

The easiest way is to make a lookup table.
Insert the following into cells A1:B5
0 4
5 12
13 20
21 28
29 4


Then the formula is
=VLOOKUP(F9,$A$1:$B$5,2)

The table doesn't have to be in A1:B5, it can be anywhere, even on
another sheet.
Ideally, give the table a name and refer to that.
If you put the table on Sheet2 in cells A1:B5 then
Insert>Name>Define> Name Billdate Refers to =Sheet2!$A$1:$B$5

Then use
=VLOOKUP(F9,Billdate,2)
Click to expand...
 
R

Roger Govier

Hi John

Whilst I agree that it may be simpler, and not require setting up a
separate table to use Lookup in this form, I have invariably found it
easier to deal with any future changes by merely altering the table, as
opposed to having to alter anything from 1 to thousands of formulae.

Your Lookup solution produces the correct result, Joel's doesn't.
 
T

Toppers

Roger,
Using (dynamic even) ranges overcomes your "objection"!

I totally agree with the general principle that using tables is better than
"hard coding" the data.

=LOOKUP($F$9,Look,Return)
 
J

Jean

Hi Thanks a lot! that helps, i still have a question:
I am dealing with Dates, so my lookup is
=LOOKUP(DAY(A2),{0,5,13,21,29},{4,12,20,28,4})
where A2 is a date ex: 3/22/2007
What I would like to have is a date as a result, in this case the result
would be 3/28/2007 or if the date is 3/31/2007 the result would be the 4th of
next month which is 4/4/2007. Any idea ? :) Thanks a lot!!!
 
T

Toppers

Try:

=IF(DAY(A2)>28,DATE(YEAR(A2),MONTH(A2)+1,DAY(LOOKUP(DAY(A2),{0,5,13,21,29},{4,12,20,28,4}))),DATE(YEAR(A2),MONTH(A2),DAY(LOOKUP(DAY(A2),{0,5,13,21,29},{4,12,20,28,4}))))
 
Top