Now it's time to highlight some math functions, to help with handling errors and creating some fairly
common computational adjustments.
Let's start with the divide function.
The divide function is fairly simple.
It divides two numbers.
But don't we already do that with the forward slash?
So why use a function?
As you probably know, dividing by zero is a problem that almost always requires some type of error
handling.
You're probably familiar with the formula.
If A equals zero zero comma be divided by a C, yes, you could write it that way index every time,
but then you need to deal with context in what is a and what if A is an aggregate and it's a whole thing.
So instead there's the divide function that ties the error handling into a nice little function for
you.
Let's recreate our average monthly wage column on our labor table.
With it I can add a new column, open divide function and see that there are three inputs the numerator,
the denominator and then the value that I should use.
If there is an error, I use employee wages of the numerator employee count as the denominator and zero
for my alternate result.
When I close the function, I suddenly have a recreation of our average wages column.
I recommend using the divide function every time that you use division over using the standard division
sign simply because more data sets will have a divide by zero issue than not to go along with this divide
function are two functions that I consider very important and useful to no manipulation.
The floor and ceiling functions.
These take a number and take them to the nearest multiple that you've given the function based upon
the direction of the function.
So the ceiling function rounds up and the floor function rounds down.
The most common use of these is to simply get the closest integer in any given direction.
However, there are other uses too.
Let's start with the integer scenario with our average wage and say that I want to define the average
wage rounded down to the nearest dollar.
I can add a new column and offer my floor function.
The first input is the actual number that I want to do the rounding on, and the second input is the
level of significance that I want to round to think about the second parameter as being the closest
multiple value to find.
So floor average monthly wage come one all around the average monthly wage down to the nearest dollar.
So five eighty one point zero four becomes five eighty one and six sixty five point seventy six becomes
six sixty five, rounding down by one is nice, but what else can we do?
Let's say I wanted to know how many quarters we needed for each of these after rounding a quarter is
twenty five cents so I can take the floor of the average monthly wage minus this new column to the significance
of zero point twenty five.
This tells us what the closest number of cents is for each dollar rounded to twenty five cents.
So now I'll wrap this with a revised function and divide by zero point twenty five to get an integer
telling me how many quarters we need for each set.
Now, this isn't perfect because I'm not handling exact values well, as you can see by the negative
ones.
So it seems to be working mostly right now.
Obviously, this example is a bit contrived.
So let's go look at our purchase orders table, which has a new tweak to it.
In addition to the amount that we spent on purchase orders, I now have a quantity field.
Let's imagine that we needed to calculate how many pallets of goods were involved with each purchase
order.
And then we know that every pallet can hold up to one hundred and forty four units for this.
I'm going to use the ceiling function to round up since I have one hundred and forty five goods that'll
need two pallets.
So I'll do ceiling quantity comma one for for this texture quantity up to the nearest multiple of one
hundred and forty four.
We can see cases where we have one hundred and forty six and it rounds up to two hundred eighty eight.
I'll add a divide function to this, and again, I'll divide by one hundred and forty four to get my
number of pallets with a few more steps, you could end up constructing measures to help you figure
out how much quantity you need to sell on average to take full advantage of two pallets.
Or you could even find out how much shipping expenses you're wasting when you order one hundred and
forty six units and have two pallets shipped to you, as you can see, these functions can be very useful
for a wide variety of calculations.
While I've demonstrated them using calculate columns, there are many uses for them and measures as
well.