In this lesson, we'll be diving into the conditional column command, and using it to create new columns that are based upon conditions in one or more other columns. So let's take a look at this data set and construct a basic conditional test. Want to see if the sale price was greater than the wholesale price? We can use a series of steps to define this first by subtracting wholesale price from sales price, identifying the sign of the results column, and then grouping by that side column to see how many gains losses and breakeven transactions were made. This gives us 3020 gains, 2844 losses, and 118 breakevens across three calculation steps with little customization allowed. Now let's look at how we can construct this with our conditional column command and possibly create some more flexibility So let's go ahead and outline our logic.
If our sale price is greater than our wholesale price, then we've gained otherwise we've lost. With our initial logic outlined, let's create our conditional column. Step one is to simply give our new column a name, like was gained. Now we need to translate our conditional statement into the various inputs. The first part says if sales price is greater than wholesale price, so the sales price and wholesale prices are both columns. So I can select sales prices, the first entry, the second entry, while I said greater than I actually want greater than or equal to capture breakevens is a game so I'll select is greater than or equal to this leaves wholesale price is the third entry so I can select column from this drop down and then select wholesale prices.
My comparison column when this combination of statements is true, that is when sales price is greater than or equal to wholesale price that I want my column to equal plus one so that goes in the output field. In a Any other scenario, I want my column to equal minus one. So that goes in the otherwise field. This conditional column recreates most of what we had previously done with the subtraction and sign commands, I can recreate the group by to see our gains of 3138, and losses of 2844. Our breakevens have been tied into our gains. This was a simple statement.
So let's go back and revise it a bit. Let's add a new rule which gives us a whole additional line for a new condition when sales price equals wholesale price, then let's equals zero pressing OK to update the results in going to the group by I can see that nothing has changed. Why is that? Well, the conditional column was it will interpret the conditions in order from top to bottom since our greater than or equal to encompasses the equals condition, it will never trigger. So we need to change it to a simple greater than this somewhat demonstrates the importance of order, but let's expand our conditional calculation one More time and add a rule specifically for losses. So if sales price is less than or equal to wholesale price, then I'll return negative one.
Otherwise, I'll return 999. This way, I would be able to quickly see if any comparisons failed as an error. Our results are exactly as we'd expect. But suppose they weren't, we can edit our conditional column and quickly move our command up or down with the ellipsis commands to the right, move to our newest addition up one will cause the less than or equals to condition to prevent our equals condition from triggering. I can press OK, and go to the group rows to quickly confirm those results. Fixing the command is as easy as re editing and moving the new command back down.
Or if I so desired, I could delete an entire condition with the Delete command within the ellipsis. As you can see, the conditional column wizard is really quite easy and intuitive to use. It makes it very easy to create Simple if then else statements for creating conditional outcomes. While we did not do so in this lesson, you could include other columns as the outputs or the otherwise output instead of a basic value. In our next lesson, we'll dive into the more generalized add custom column command and start learning how to write our own calculation equations.