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

Hi i am really having trouble to make the following work:
=IF(OR($F$928,$F$9<5),4,IF(OR($F$94,$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 ?

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

Try instead match

=LOOKUP($F$9,{4,12,20,28,31;4,12,20,28,4})

"Jean" wrote:

Hi i am really having trouble to make the following work:
=IF(OR($F$928,$F$9<5),4,IF(OR($F$94,$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 ?

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

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
InsertNameDefine Name Billdate Refers to =Sheet2!$A$1:$B$5

Then use
=VLOOKUP(F9,Billdate,2)

--
Regards

Roger Govier


"Jean" wrote in message
...
Hi i am really having trouble to make the following work:
=IF(OR($F$928,$F$9<5),4,IF(OR($F$94,$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 ?

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

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.
--
Mike Lee
McKinney,TX USA


"Jean" wrote:

Hi i am really having trouble to make the following work:
=IF(OR($F$928,$F$9<5),4,IF(OR($F$94,$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 ?

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

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


"Roger Govier" wrote:

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
InsertNameDefine Name Billdate Refers to =Sheet2!$A$1:$B$5

Then use
=VLOOKUP(F9,Billdate,2)

--
Regards

Roger Govier


"Jean" wrote in message
...
Hi i am really having trouble to make the following work:
=IF(OR($F$928,$F$9<5),4,IF(OR($F$94,$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 ?

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

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.

--
Regards

Roger Govier


"Toppers" wrote in message
...
=LOOKUP($F$9,{0,5,13,21,29},{4,12,20,28,4})


"Roger Govier" wrote:

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
InsertNameDefine Name Billdate Refers to =Sheet2!$A$1:$B$5

Then use
=VLOOKUP(F9,Billdate,2)

--
Regards

Roger Govier


"Jean" wrote in message
...
Hi i am really having trouble to make the following work:
=IF(OR($F$928,$F$9<5),4,IF(OR($F$94,$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 ?

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

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)

"Roger Govier" wrote:

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.

--
Regards

Roger Govier


"Toppers" wrote in message
...
=LOOKUP($F$9,{0,5,13,21,29},{4,12,20,28,4})


"Roger Govier" wrote:

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
InsertNameDefine Name Billdate Refers to =Sheet2!$A$1:$B$5

Then use
=VLOOKUP(F9,Billdate,2)

--
Regards

Roger Govier


"Jean" wrote in message
...
Hi i am really having trouble to make the following work:
=IF(OR($F$928,$F$9<5),4,IF(OR($F$94,$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 ?

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

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!!!
"Toppers" wrote:

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


"Roger Govier" wrote:

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
InsertNameDefine Name Billdate Refers to =Sheet2!$A$1:$B$5

Then use
=VLOOKUP(F9,Billdate,2)

--
Regards

Roger Govier


"Jean" wrote in message
...
Hi i am really having trouble to make the following work:
=IF(OR($F$928,$F$9<5),4,IF(OR($F$94,$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 ?

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

Try:

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

"Jean" wrote:

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!!!
"Toppers" wrote:

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


"Roger Govier" wrote:

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
InsertNameDefine Name Billdate Refers to =Sheet2!$A$1:$B$5

Then use
=VLOOKUP(F9,Billdate,2)

--
Regards

Roger Govier


"Jean" wrote in message
...
Hi i am really having trouble to make the following work:
=IF(OR($F$928,$F$9<5),4,IF(OR($F$94,$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 ?