Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF function help
Hello,
I have data arrange as this, b2 c2 D2 1 2 3 ........17 B3 C3 D3 0 2 5 ..........15 Using the above criteria i would like to match C2 in col E then corresponding C3 with col B and if number in Col B is below that of C3 then return ''out' if over ''on target'' Hope this clear, I have tried this with an IF function but can't get it to work? Any help would be appreciated. Thanks |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF function help
Unclear. You are matching C2 in col E? and C3 in col B? What are you matching
exactly? You have no reference to what is in column E, and column B has 2 values that you show. More detail is needed. And perhaps what a desired output would be for a given sample table. -- John C "Haz" wrote: Hello, I have data arrange as this, b2 c2 D2 1 2 3 ........17 B3 C3 D3 0 2 5 ..........15 Using the above criteria i would like to match C2 in col E then corresponding C3 with col B and if number in Col B is below that of C3 then return ''out' if over ''on target'' Hope this clear, I have tried this with an IF function but can't get it to work? Any help would be appreciated. Thanks |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF function help
Hi thanks for responding,
Sorry, i''m trying to do two matches Yes, i would like to match any number in order from 0 -17 that appear in a row from b2 -S2 with the same numbers that appear randomly in Col E and when they match then do a second match based on the set of numbers (0 -15) that sit below in b3-S2 against numbers in Col B which are anything between 0 -15. The result in col F would be a exact match which fine but if it is below say 'out' if it is higher say 'on target' hope this is clearer but i can email you a table if you provide an email address. Thanks again Haz b2 c2 D2 1 2 3 ........17 B3 C3 D3 0 2 5 ..........15 Col B "John C" wrote: Unclear. You are matching C2 in col E? and C3 in col B? What are you matching exactly? You have no reference to what is in column E, and column B has 2 values that you show. More detail is needed. And perhaps what a desired output would be for a given sample table. -- John C "Haz" wrote: Hello, I have data arrange as this, b2 c2 D2 1 2 3 ........17 B3 C3 D3 0 2 5 ..........15 Using the above criteria i would like to match C2 in col E then corresponding C3 with col B and if number in Col B is below that of C3 then return ''out' if over ''on target'' Hope this clear, I have tried this with an IF function but can't get it to work? Any help would be appreciated. Thanks |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF function help
I am still not able to wrap my mind around your question. I think because you
keep referring to columns when maybe you mean rows. Here is How I envision your table In cells range of B2:S2, and I assume these are static (always the same) values: 0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 | 13 | 14 | 15 | 16 | 17 In cells range of B3:S3 is a formula of some sort that will cause all these cells to have the values between 0 and 15 (inclusive?) And if I read your question right, you are basically want to know when, given a certain number of 0-17, if it is greater than, less than, or equal to than the value it sits directly above in row 3. (Column F has 2 values already in it, 4 in F2, and randomnumber in F3, so don't know what you mean by result in column F, unless it is like in cell F5. Word of advice, if you are giving a result that is in a specific cell, give the specific cell, if a range of cells, at least give the range start, such as F5:F20 or something). Based on what I 'think' you are looking for, you could try this: Assuming E5 is where your number of 0-17 is located, then: F5: =IF(E5HLOOKUP(E5,$B$2:$S$3,2,FALSE),"on target",IF(E5<HLOOKUP(E5,$B$2:$S$3,2,FALSE),"out", "equal")) If they are equal, not sure how you want that handled. But could modify the formula if it is = to be on target like so: =IF(E5=HLOOKUP(E5,$B$2:$S$3,2,FALSE),"on target","out") or if it is only if it is to be on target, and if it is <= then out, like this: =IF(E5HLOOKUP(E5,$B$2:$S$3,2,FALSE),"on target","out") -- John C "Haz" wrote: Hi thanks for responding, Sorry, i''m trying to do two matches Yes, i would like to match any number in order from 0 -17 that appear in a row from b2 -S2 with the same numbers that appear randomly in Col E and when they match then do a second match based on the set of numbers (0 -15) that sit below in b3-S2 against numbers in Col B which are anything between 0 -15. The result in col F would be a exact match which fine but if it is below say 'out' if it is higher say 'on target' hope this is clearer but i can email you a table if you provide an email address. Thanks again Haz b2 c2 D2 1 2 3 ........17 B3 C3 D3 0 2 5 ..........15 Col B "John C" wrote: Unclear. You are matching C2 in col E? and C3 in col B? What are you matching exactly? You have no reference to what is in column E, and column B has 2 values that you show. More detail is needed. And perhaps what a desired output would be for a given sample table. -- John C "Haz" wrote: Hello, I have data arrange as this, b2 c2 D2 1 2 3 ........17 B3 C3 D3 0 2 5 ..........15 Using the above criteria i would like to match C2 in col E then corresponding C3 with col B and if number in Col B is below that of C3 then return ''out' if over ''on target'' Hope this clear, I have tried this with an IF function but can't get it to work? Any help would be appreciated. Thanks |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF function help
This works fine, sorry wasn't clear first time round.
"John C" wrote: I am still not able to wrap my mind around your question. I think because you keep referring to columns when maybe you mean rows. Here is How I envision your table In cells range of B2:S2, and I assume these are static (always the same) values: 0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 | 13 | 14 | 15 | 16 | 17 In cells range of B3:S3 is a formula of some sort that will cause all these cells to have the values between 0 and 15 (inclusive?) And if I read your question right, you are basically want to know when, given a certain number of 0-17, if it is greater than, less than, or equal to than the value it sits directly above in row 3. (Column F has 2 values already in it, 4 in F2, and randomnumber in F3, so don't know what you mean by result in column F, unless it is like in cell F5. Word of advice, if you are giving a result that is in a specific cell, give the specific cell, if a range of cells, at least give the range start, such as F5:F20 or something). Based on what I 'think' you are looking for, you could try this: Assuming E5 is where your number of 0-17 is located, then: F5: =IF(E5HLOOKUP(E5,$B$2:$S$3,2,FALSE),"on target",IF(E5<HLOOKUP(E5,$B$2:$S$3,2,FALSE),"out", "equal")) If they are equal, not sure how you want that handled. But could modify the formula if it is = to be on target like so: =IF(E5=HLOOKUP(E5,$B$2:$S$3,2,FALSE),"on target","out") or if it is only if it is to be on target, and if it is <= then out, like this: =IF(E5HLOOKUP(E5,$B$2:$S$3,2,FALSE),"on target","out") -- John C "Haz" wrote: Hi thanks for responding, Sorry, i''m trying to do two matches Yes, i would like to match any number in order from 0 -17 that appear in a row from b2 -S2 with the same numbers that appear randomly in Col E and when they match then do a second match based on the set of numbers (0 -15) that sit below in b3-S2 against numbers in Col B which are anything between 0 -15. The result in col F would be a exact match which fine but if it is below say 'out' if it is higher say 'on target' hope this is clearer but i can email you a table if you provide an email address. Thanks again Haz b2 c2 D2 1 2 3 ........17 B3 C3 D3 0 2 5 ..........15 Col B "John C" wrote: Unclear. You are matching C2 in col E? and C3 in col B? What are you matching exactly? You have no reference to what is in column E, and column B has 2 values that you show. More detail is needed. And perhaps what a desired output would be for a given sample table. -- John C "Haz" wrote: Hello, I have data arrange as this, b2 c2 D2 1 2 3 ........17 B3 C3 D3 0 2 5 ..........15 Using the above criteria i would like to match C2 in col E then corresponding C3 with col B and if number in Col B is below that of C3 then return ''out' if over ''on target'' Hope this clear, I have tried this with an IF function but can't get it to work? Any help would be appreciated. Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
copy of excel file not showing formulal/function in the function b | Excel Discussion (Misc queries) | |||
LINKEDRANGE function - a complement to the PULL function (for getting values from a closed workbook) | Excel Worksheet Functions | |||
Offset function with nested match function not finding host ss. | Excel Worksheet Functions | |||
Emulate Index/Match combo function w/ VBA custom function | Excel Worksheet Functions | |||
Nested IF Function, Date Comparing, and NetworkDays Function | Excel Worksheet Functions |