| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
IF and MOD functions
Hello,
I have a list of numbers spanning down in a column that continuously increases. I would like to have the next column over trigger with a "False" or something of the like whenever the list has hit or first exceeds a certain multiple. For example, if the "Trigger" is every multiple of 200, this is what I envision the list looking like: 150 True 160 True 170 True 195 True 205 False (First to exceed 200, first multiple of 200) 230 True 290 True 340 True 450 False (First to exceed 400, second multiple of 200) 580 True 610 False (First to exceed 600, third multiple of 200) If anyone can help me out with a formula or macro I would really appreciate it, thanks!!! |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
IF and MOD functions
On Wed, 9 Sep 2009 11:59:17 -0700, cincinnatikid123
wrote: Hello, I have a list of numbers spanning down in a column that continuously increases. I would like to have the next column over trigger with a "False" or something of the like whenever the list has hit or first exceeds a certain multiple. For example, if the "Trigger" is every multiple of 200, this is what I envision the list looking like: 150 True 160 True 170 True 195 True 205 False (First to exceed 200, first multiple of 200) 230 True 290 True 340 True 450 False (First to exceed 400, second multiple of 200) 580 True 610 False (First to exceed 600, third multiple of 200) If anyone can help me out with a formula or macro I would really appreciate it, thanks!!! If your list is in the A column starting on row 1, try the following formulas: In cell B1: =A1<200 In cell B2: =FLOOR(A2,200)=FLOOR(A1,200) Copy cell B2 down as far as you have data in column A. Hope this helps / Lars-Åke |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
IF and MOD functions
Woohoo!!! This is great, thank you!
"Lars-Ã…ke Aspelin" wrote: On Wed, 9 Sep 2009 11:59:17 -0700, cincinnatikid123 wrote: Hello, I have a list of numbers spanning down in a column that continuously increases. I would like to have the next column over trigger with a "False" or something of the like whenever the list has hit or first exceeds a certain multiple. For example, if the "Trigger" is every multiple of 200, this is what I envision the list looking like: 150 True 160 True 170 True 195 True 205 False (First to exceed 200, first multiple of 200) 230 True 290 True 340 True 450 False (First to exceed 400, second multiple of 200) 580 True 610 False (First to exceed 600, third multiple of 200) If anyone can help me out with a formula or macro I would really appreciate it, thanks!!! If your list is in the A column starting on row 1, try the following formulas: In cell B1: =A1<200 In cell B2: =FLOOR(A2,200)=FLOOR(A1,200) Copy cell B2 down as far as you have data in column A. Hope this helps / Lars-Ã…ke |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How to convert cell formula functions to code functions | Excel Discussion (Misc queries) | |||
| formula/functions for average and if functions | Excel Worksheet Functions | |||
| efficiency: database functions vs. math functions vs. array formula | Excel Discussion (Misc queries) | |||
| Looking for a site with functions that substitute the ATP functions | Excel Worksheet Functions | |||
| Nesting functions in the functions dialog box | Excel Worksheet Functions | |||
Hybrid Mode
