Conditional Values (Formula)
- 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
Hello All,
I need some help with changing values depending on the given condition below.
I've made a new "Code" column where Name A = Code 1, Name B = Code 2, etc. with the following formula:
IF [Name] = "A" then "1"
elseif [Name] = "B" then "2"
elseif [Name] = "C" then "3"
elseif [Name] = "D" then "4"
elseif [Name] = "E" then "5"
elseif [Name] = "F" then "6"
elseif [Name] = "G" then "7"
else "0" endif
This repeats on a monthly basis:
Date | Name | Amount | Code |
2021-07-31 00:00:00 | A | 100 | 1 |
2021-07-31 00:00:00 | B | 100 | 2 |
2021-07-31 00:00:00 | C | 0 | 3 |
2021-07-31 00:00:00 | D | 100 | 4 |
2021-07-31 00:00:00 | E | 100 | 5 |
2021-07-31 00:00:00 | F | 100 | 6 |
2021-07-31 00:00:00 | G | 100 | 7 |
2021-08-31 00:00:00 | A | 100 | 1 |
2021-08-31 00:00:00 | B | 100 | 2 |
2021-08-31 00:00:00 | C | 100 | 3 |
2021-08-31 00:00:00 | D | 100 | 4 |
2021-08-31 00:00:00 | E | 100 | 5 |
2021-08-31 00:00:00 | F | 100 | 6 |
2021-08-31 00:00:00 | G | 100 | 7 |
However, now I have a new condition where if Name "C" has Amount "0" then the Code for Name "A" needs to be "11". This also needs to take the dates into consideration as some monthly dates may have Name "C" with an Amount greater than 0 and can remain with the same Code "1".
Essentially, I need to get the following output:
Date | Name | Amount | Code |
2021-07-31 00:00:00 | A | 100 | 11 |
2021-07-31 00:00:00 | B | 100 | 2 |
2021-07-31 00:00:00 | C | 0 | 3 |
2021-07-31 00:00:00 | D | 100 | 4 |
2021-07-31 00:00:00 | E | 100 | 5 |
2021-07-31 00:00:00 | F | 100 | 6 |
2021-07-31 00:00:00 | G | 100 | 7 |
2021-08-31 00:00:00 | A | 100 | 1 |
2021-08-31 00:00:00 | B | 100 | 2 |
2021-08-31 00:00:00 | C | 100 | 3 |
2021-08-31 00:00:00 | D | 100 | 4 |
2021-08-31 00:00:00 | E | 100 | 5 |
2021-08-31 00:00:00 | F | 100 | 6 |
2021-08-31 00:00:00 | G | 100 | 7 |
Please let me know how I can solve this!
Solved! Go to Solution.
- Labels:
- Help
- 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
Hi @Yeonsujen , I've used some summarises & a formula to create the New_Code, column you are after, enjoy, Happy solving 😄
Joe
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Much smoother from @Christina_H
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@Christina_H Thank you for your response, it worked perfectly! 🙂
@JoeHerbert Thank you as well for your response! It also worked and agree with the above that Christina's is smoother. Always interesting to see different methods regardless!
- 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
@Yeonsujen I think this is a simple fix - you need to change the join tool to join by both date and employee ID
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@Christina_H Sorry I edited my comment before! Yes this helped to fix it in the end, thank you again! 🙂