- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎May 16, 2022 11:53 PM
Seeking help with a “If” formula to create buckets in a new column. For example, in column A i have series of different percentages. IN column B i wish to create a formula and returns a “text” if it meets the criteria…for example, if A1 = <100, “<100”, ifA1 = 100to120, “120”, ifA1 = 121to140,“140” and so on.
Hope you can help
Cheers
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎May 17, 2022 03:02 AM
Hey Sam, I’ve created something for you here that should solve your problem. You can duplicate the base by clicking the title of the base at the top of the screen and then click the three horizontal dots on the right, allowing you to modify it as needed.
Here’s the formula used in the table:
IF(
{Column A} < 100, "<100",
IF(
AND({Column A} >= 100, {Column A} < 120), 120,
IF(
AND({Column A} >= 120, {Column A} < 140), 140,
BLANK()
)
)
)
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎May 21, 2022 10:01 PM
Thanks Adam,
This helps…what if the values are percentages, how would I represent that?
Cheers
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎May 21, 2022 11:06 PM
Hm, the percentage values work off the basis of 1
, i.e. 100% is 100, 50% is 0.5, 130% is 1.3, etc
So we’d just modify the checks to accommodate that:
IF(
{Column A} < 1, "<100",
IF(
AND({Column A} >= 1, {Column A} < 1.2), 120,
IF(
AND({Column A} >= 1.2, {Column A} < 1.4), 140,
BLANK()
)
)
)
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎May 21, 2022 11:17 PM
Got it, thats worked…thanks