| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 ? |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 ? |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 ? |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 ? |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 ? |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 ? |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 ? |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 ? |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 ? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Excel check one column for highlighting and return number | Excel Discussion (Misc queries) | |||
| Addding a Random number to a fixed number..... | Excel Discussion (Misc queries) | |||
| Find first numer in a Column | Excel Worksheet Functions | |||
| check if reference exists, then return its value or return 0 | Excel Worksheet Functions | |||
| Rounding a number to a multiple quantity that adds to a fixed total number | Excel Worksheet Functions | |||
Hybrid Mode
