Multiple criteria formula help
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi,
I'm trying to use the formula tool to add another column with output dependent on certain rules below and concatenate the result in the output column shown
Rule 1: None of 4 columns can be blank
Rule 2: None of 4 columns can have the word "grape"
Rule 3: Col 2 must be a whole number
Col 1 | Col 2 | Col 3 | Col 4 | Output |
pear | 4 | banana | Col 3 cannot be blank | |
apple | 3.567 | apple | grape | Col 2 must be whole number, Col 4 cannot be grape |
strawberry | kiwi | grape | Col 2 must be whole number, Col 3 cannot be grape, Col 4 cannot be blank |
Solved! Go to Solution.
- Labels:
- Help
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi @richleeb2
Here's one way:
- Transpose the data
- IF statement for each piece of logic
- Concatenate results and join back
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Thank you! How do I add a space after each comma?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
I had a very similar approach to @Luke_C . I always recommend pivoting your data instead of writing formulas across multiple columns.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Thank you Luke, this worked perfect but I want to add one more criteria to Col 2. In addition to it must be a whole number, it cannot be blank. How do I update the formula?
IF IsEmpty([Value])
Then [Name] + ' cannot be blank'
Elseif Contains([Value],'N/A')
Then [Name] + ' cannot be N/A'
Elseif !IsInteger([Value]) and [Name] = 'Rehosting Question 2'
Then [Name] + ' must be a whole number'
else null()
endif
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi @richleeb2
If column two is left blank it should already be handled by the logic because it is checking for all fields being blank. Please provide more information if that's not working correctly with your data. Otherwise be sure to accept the solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Sorry I meant 0 but I figured it out!
