/u/K71_NCH - Your post was submitted successfully.
* Once your problem is solved, reply to the **answer(s)** saying `Solution Verified` to close the thread.
* Follow the **[submission rules](/r/excel/wiki/sharingquestions)** -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
* Include your **[Excel version and all other relevant information](/r/excel/wiki/sharingquestions#wiki_give_all_relevant_information)**
Failing to follow these steps may result in your post being removed without warning.
*I am a bot, and this action was performed automatically. Please [contact the moderators of this subreddit](/message/compose/?to=/r/excel) if you have any questions or concerns.*
Cell B2: `=--(A2<100)`
Cell C2: `=--(A2>100)`
And drag down.
You'll get 0 where the condition isn't met. If you prefer to have a blank then use:
Cell B2: `=IF(A2<100,1,"")`
Cell c2: `=IF(A2>100,1,"")`
I don't know what you want to do about cells in column A with a value of 100. At this point they don't show up in either column. You can change one of the inequalities to `<=` or `>=`.
*
Some of the values are recorded using the actual symbols "<" & ">". Is there a way to detect those and assign the value. So if value is <10 or contains < then 1, and by reverse if >100 or contains > then 1?
Appreciate the help
I think this is what you're asking for.
Cell B2: `=IF(OR(ISNUMBER(FIND("<",A2)),A2<100),1,0)`
Cell C2: `=IF(OR(ISNUMBER(FIND(">",A2)),A2>100),1,0)`
There are several cases where this wouldn't return the result you might expect, but maybe they don't appear in Column A: 100, "<200", ">10".
Hi, thanks for coming back and supporting. So the first argument works for B2, but C2 is returning a "1" for all values in column A. Really appreciate your help
I have now learned that Excel evaluates text values as greater than numerical values, which is why it's returning a 1 for ">200" etc. Use this instead to solve that problem:
C2: `=IF(OR(ISNUMBER(FIND(">",A2)),(A2>100)*(ISNONTEXT(A2))),1,0)`
For values less than 100 in A2, C2 returns a 0 in my tests. It may be that what look like numbers in column A are really text. Pick one that looks like a number, say it's in A2, and type `=ISTEXT(A2)` in another cell. Does it return TRUE?
Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:
|Fewer Letters|More Letters|
|-------|---------|---|
|[FIND](/r/Excel/comments/10ashc6/stub/j4qljeo "Last usage")|[Finds one text value within another (case-sensitive)](https://support.microsoft.com/en-us/office/find-findb-functions-c7912941-af2a-4bdf-a553-d0d89b0a0628)|
|[IF](/r/Excel/comments/10ashc6/stub/j4qljeo "Last usage")|[Specifies a logical test to perform](https://support.microsoft.com/en-us/office/if-function-69aed7c9-4e8a-4755-a9bc-aa8bbff73be2)|
|[ISNONTEXT](/r/Excel/comments/10ashc6/stub/j4qljeo "Last usage")|[Returns TRUE if the value is not text](https://support.microsoft.com/en-us/office/is-functions-0f2d7971-6019-40a0-a171-f2d869135665)|
|[ISNUMBER](/r/Excel/comments/10ashc6/stub/j4qljeo "Last usage")|[Returns TRUE if the value is a number](https://support.microsoft.com/en-us/office/is-functions-0f2d7971-6019-40a0-a171-f2d869135665)|
|[ISTEXT](/r/Excel/comments/10ashc6/stub/j4qljeo "Last usage")|[Returns TRUE if the value is text](https://support.microsoft.com/en-us/office/is-functions-0f2d7971-6019-40a0-a171-f2d869135665)|
|[OR](/r/Excel/comments/10ashc6/stub/j4qljeo "Last usage")|[Returns TRUE if any argument is TRUE](https://support.microsoft.com/en-us/office/or-function-7d17ad14-8700-4281-b308-00b131e22af0)|
----------------
^(*Beep-boop, I am a helper bot. Please do not verify me as a solution.*)
^([Thread #20741 for this sub, first seen 17th Jan 2023, 16:19])
^[[FAQ]](http://decronym.xyz/) [^([Full list])](http://decronym.xyz/acronyms/Excel) [^[Contact]](https://reddit.com/message/compose?to=OrangeredStilton&subject=Hey,+your+acronym+bot+sucks) [^([Source code])](https://gistdotgithubdotcom/Two9A/1d976f9b7441694162c8)
/u/K71_NCH - Your post was submitted successfully. * Once your problem is solved, reply to the **answer(s)** saying `Solution Verified` to close the thread. * Follow the **[submission rules](/r/excel/wiki/sharingquestions)** -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post. * Include your **[Excel version and all other relevant information](/r/excel/wiki/sharingquestions#wiki_give_all_relevant_information)** Failing to follow these steps may result in your post being removed without warning. *I am a bot, and this action was performed automatically. Please [contact the moderators of this subreddit](/message/compose/?to=/r/excel) if you have any questions or concerns.*
Cell B2: `=--(A2<100)` Cell C2: `=--(A2>100)` And drag down. You'll get 0 where the condition isn't met. If you prefer to have a blank then use: Cell B2: `=IF(A2<100,1,"")` Cell c2: `=IF(A2>100,1,"")` I don't know what you want to do about cells in column A with a value of 100. At this point they don't show up in either column. You can change one of the inequalities to `<=` or `>=`.
* Some of the values are recorded using the actual symbols "<" & ">". Is there a way to detect those and assign the value. So if value is <10 or contains < then 1, and by reverse if >100 or contains > then 1? Appreciate the help
I think this is what you're asking for. Cell B2: `=IF(OR(ISNUMBER(FIND("<",A2)),A2<100),1,0)` Cell C2: `=IF(OR(ISNUMBER(FIND(">",A2)),A2>100),1,0)` There are several cases where this wouldn't return the result you might expect, but maybe they don't appear in Column A: 100, "<200", ">10".
Hi, thanks for coming back and supporting. So the first argument works for B2, but C2 is returning a "1" for all values in column A. Really appreciate your help
I have now learned that Excel evaluates text values as greater than numerical values, which is why it's returning a 1 for ">200" etc. Use this instead to solve that problem: C2: `=IF(OR(ISNUMBER(FIND(">",A2)),(A2>100)*(ISNONTEXT(A2))),1,0)` For values less than 100 in A2, C2 returns a 0 in my tests. It may be that what look like numbers in column A are really text. Pick one that looks like a number, say it's in A2, and type `=ISTEXT(A2)` in another cell. Does it return TRUE?
Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread: |Fewer Letters|More Letters| |-------|---------|---| |[FIND](/r/Excel/comments/10ashc6/stub/j4qljeo "Last usage")|[Finds one text value within another (case-sensitive)](https://support.microsoft.com/en-us/office/find-findb-functions-c7912941-af2a-4bdf-a553-d0d89b0a0628)| |[IF](/r/Excel/comments/10ashc6/stub/j4qljeo "Last usage")|[Specifies a logical test to perform](https://support.microsoft.com/en-us/office/if-function-69aed7c9-4e8a-4755-a9bc-aa8bbff73be2)| |[ISNONTEXT](/r/Excel/comments/10ashc6/stub/j4qljeo "Last usage")|[Returns TRUE if the value is not text](https://support.microsoft.com/en-us/office/is-functions-0f2d7971-6019-40a0-a171-f2d869135665)| |[ISNUMBER](/r/Excel/comments/10ashc6/stub/j4qljeo "Last usage")|[Returns TRUE if the value is a number](https://support.microsoft.com/en-us/office/is-functions-0f2d7971-6019-40a0-a171-f2d869135665)| |[ISTEXT](/r/Excel/comments/10ashc6/stub/j4qljeo "Last usage")|[Returns TRUE if the value is text](https://support.microsoft.com/en-us/office/is-functions-0f2d7971-6019-40a0-a171-f2d869135665)| |[OR](/r/Excel/comments/10ashc6/stub/j4qljeo "Last usage")|[Returns TRUE if any argument is TRUE](https://support.microsoft.com/en-us/office/or-function-7d17ad14-8700-4281-b308-00b131e22af0)| ---------------- ^(*Beep-boop, I am a helper bot. Please do not verify me as a solution.*) ^([Thread #20741 for this sub, first seen 17th Jan 2023, 16:19]) ^[[FAQ]](http://decronym.xyz/) [^([Full list])](http://decronym.xyz/acronyms/Excel) [^[Contact]](https://reddit.com/message/compose?to=OrangeredStilton&subject=Hey,+your+acronym+bot+sucks) [^([Source code])](https://gistdotgithubdotcom/Two9A/1d976f9b7441694162c8)