This chapter has been about combining columns in different ways, either through mathematical computations or logical decisions, but so far, never both simultaneously. as powerful as the individual features can be. They add a bunch of steps if used individually and can make arriving at a desired outcome cumbersome and difficult if the number of steps and order requirements are high, but those issues are moot with the custom column formula wizard. For this data set, we have a series of transactions, some coupons and a pair of requirements for each coupon, a minimum transaction amount for the coupon to apply and a coupon expiration date. For our coupon to be applicable, the amount must be greater than the minimum and our date must be on or before the expiration date. Even though this is a combination of logical outcomes, we can still use a multi step process to calculate whether the coupon applies by creating two columns and multiplying them together.

The first column would calculate whether the coupon has expired by taking the date and determining whether it comes after the expiration date. In such a scenario, it would return zero, otherwise it would return a value of one. The second column would calculate whether the coupons minimum amount has been met by comparing the transaction amount to that coupon minimum. When the transaction exceeds the minimum, it'll return one, otherwise return zero. Taking the product of these two fields will only result in one when both the coupon is not expired and the minimum has been reached. But this three step process may not be too obvious for all users.

And alternative and in this case, easier approach would be to use a cascading if then else conditional column to apply the rules one after the other and return a zero whenever a rule is broken. This checks to see if the expiration date has passed and returns zero then, if it hasn't passed, then it checks to see if the transaction sufficient and return zero if it isn't. Otherwise, it assumes the coupon is applicable in return. So one. For simple and logical requirements, this is too challenging. However, any significant complexity, or either or requirements will create a long series of statements can quickly become unmanageable.

In this scenario, we're looking at a calculation that can be handled far better and far easier. With a custom column formula, we need to create a custom conditional formula. And to do this, let's use our formula bar as a learning tool. I'm going to click on the added conditional column command and see what it did. Initially, your formula bar may not be visible, so you'll need to turn it on by going to the view ribbon and selecting the check mark next to Formula Bar. Once visible, the formula bar will show the full command performed along the step.

In this case, hit called table dot Add Column and did a bunch of stuff inside that function. The power that we're interested in is the part that comes immediately after the each keyword and before the close parenthesis. This is the function that we created with our conditional formula wizard. The command reads if amount greater than or equal to minimum, then one else zero. Moving to our latest step, we can go on and create a conditional command using the custom function that performs our data exploration calculation like so. We'll give the column a name, and then we'll write if, and just a quick note, keywords must be lowercase capital if would generate an error.

And then to insert the date field, I'll double click on it in my list of available columns. All right, less than or equal to and double click my expires one column. I'll add then one else zero to finish out my statement. And we can see that I have no syntax errors detected which is good. And then I'll press OK to create the column just as we've defined Even adds the table column extra piece to so that it looks identical to our prior command. Since the command does look identical to our prior one, what happens if we try to edit it?

Well, the query editor rightly recognizes it as a simple conditional statement. And so we'll open it back up in the conditional editor wizard. So let's go ahead and delete this command and add a slightly more complex if statement. So this is statement, I'm going to combine our two logical conditions into a single statement using the Add keyword. There's both an and and an or keyword, which can be used in conjunction with parentheses to construct as elaborate a logical statement as needed to determine whether to execute one command or another. In this statement, I want to evaluate whether the date is less than or equal to the expiration date, and whether the amount is greater than or equal to the minimum requirement.

When both of those statements are true, I want to return a one. Otherwise I'll return zero quickly comparing this against our other approach, we can see an identical set of results. But now, if we re edit our custom calculation, we were put back into the add custom calculation wizard, rather than our conditional column wizard, since our logic is more complex, since we're back in this editor, there are other commands that we can apply quickly and easily within these formulas as well like simple arithmetic, rather than calculating whether the coupon applies. But if we calculated the coupons value, instead of than one, all we need to do is replace the one with the calculation that we desire when the statement is true. In this case, that's the amount of the coupon coupon one, multiply it against the original amount field. The other standard operators such as addition, subtraction, and division can all be done this way as well.

So let's rename this to coupon one value. Now as with any other calculations, order of operations can be key. This is very simple calculation. However, I can add parentheses around my coupon one times amount To specify that the calculation belongs as a single unit. Now I'll press OK to create the column. Since I want this column for my secondary coupon to, I can simply re edit my coupon one calculation, copy the formula, create a new custom column, and paste that formula for editing.

In this case, the only things that need modification are the column names, I do not need to delete the column names entirely and re double click on the correct columns. Instead, what I can do is I can simply modify the column names since they are just like references in an excel formula. pressing OK quickly gives me my coupon to value. This covers the initial basics of creating custom formulas using if then else statements with and or logical constructions and basic mathematical calculations. With this, you'll be able to create the custom calculations necessary to solve the following Practice set designed around scoring a blackjack light card game and the follow up lesson that will include this chapter. I'll open Pandora's box by introducing you to the function library, showing you some ways to use the prior wizards to learn about the functions, and finally leaving you with Microsoft's m Reference Library, which lists all of the available formula functions.