Let's take a look at some simple but useful formula examples.
This is also going to help us practice entering formulas in Excel. In the first example we want to get
the final value after the percentage increase or decrease.
Here we have a list of items, we have their price and we have the discount
that we want to apply to that price.
We want to get the final value.
First off let's just start to calculate what is the dollar amount of that discount.
Just type in the equals sign.
Go to cell B5 multiplied with the discount and press enter.
Six dollars is the amount of the discount in this case.
Now we want to deduct the six dollars from the original price.
So let's go and modify the formula.
Go to B5 minus our discount value.
The best practice is to input your brackets here so that it's clear which calculation occurs before
the other.
But in this case it doesn't matter whether you have brackets or not because remember multiplication
occurs before subtraction.
When I press enter, I get 54.
We can also calculate this in a different way so we can just factor out B5 and rewrite this formula.
This would be =B5*(1-C5).
Can I safely remove the brackets?
I can't.
I will get the wrong answer because in this case I need the subtraction to happen before the multiplication.
I need to include that in its own brackets.
If I take away the brackets let's just see what number we get. We get the wrong number.
Excel also thinks we want to get a percentage returned so let's just change that to General.
We get 59.
Right?
So putting the brackets in the correct place is really important.
You don't always need it but just put the brackets because it's clear for everyone else what's being
calculated first.
So now let's highlight this, double click on the side, and send this down.
Now another thing to keep an eye on is if your formulas are different within a range.
Let's say by mistake someone comes in here and adds a plus two and we press enter.
We get this green icon on the side of the cell
and if you click on this exclamation mark, you see it says inconsistent formula.
Now we can decide if we want to copy the formula from above
and if you don't want to see this green icon you just want to hide it.
You can go to error checking options.
This takes you to the Excel options. And here under error checking, in the formula section, take away
the tick mark for enable background error checking.
This doesn't put that green icon on the side of the cell in case you have inconsistent formulas. But at
the beginning when you're using Excel it's good practice to have it on.
And once you're more confident about writing formulas then you can take away this checkmark.
In this case let's just go and copy our formula all the way down. In the next example,
we have our profit information on a year to date basis.
This means March data for example includes January monthly, February monthly, and March monthly.
Now we've been given the task to get the profit information per month instead of having it year to date.
So what type of formula can we write to get the job done? Where for January
Year to date and monthly is going to be the same value so we can just reference the first cell here,
press enter and send the formula down.
But what about February, March, April, and so on? February monthly would be the February year to date minus
the January year to date which would be the January month. Press enter and send this down.
Now what about March?
Is it the same formula?
It's similar to the formula I just wrote, right?
Because March monthly would be the March year to date minus February year to date.
This means that I can actually drag my formula all the way across and all the way down.
The only formula that's completely different would be the January month.
So if I drag this across and drag it down I get my formulas calculating correctly because they're all
relatively referencing the cell.
So when I click here I see it's July minus June.
Let's go to the bottom corner here and see what we get.
We get December minus November for the last item.
So everything has relatively shifted.
.
The only thing is that January is different.
And usually when you're writing formulas best practice is try to get the same formula for the entire range.
For us to do that here,
One option would be to use the if function which is one of Excels most useful functions.
And that's something we're going to cover in the next section when we take a look at Excel functions.