PortalKota – How to create a complex Excel Formula. Today I am going to demonstrate slightly more complex formula that you can create in excel sheets.
I hope you have seen the last video where i demonstrated how to do Addition Multiplication Division and Subtraction . These complex formulas uses all those simple operations. Assuming that you have seen that let’s go into creating these formula into the excel sheet.
How to create a complex Excel Formula
So the formulae I have chosen are for Simple Interest and Compound Interest you use this in calculating your finances especially if you have a loan or if you have put a deposit into the bank.
For ease I’m going to copy-paste this as an image into our excel sheet so for that I press Win-Shift-S button. And that allows me to catch a snap from the screen the chosen area. I go into our excel sheet and paste it resizing it to a fairly reasonable size.
This will be here on the right side for our reference and in the left side we will create the formula. You can see that both these formula have three variables principal number of years and rate of interest here.
Also you can see principal number of years and rate of interest. I’m going to type out those labels here principle in US Dollars number of years and rate of interest in percentage.
I go here on top of the column B and “Double Click” this edge here and it resized it for me to the perfect length. I select these cells, give it a nice border so that I can enter the values. Now let me enter some values say I’m putting in 100 000 US Dollars as a principal amount.
The number of years and say 3 years. The rate of interest as say 7.5. Now to calculate the simple interest, first I put equal to and then I open the bracket. With the bracket open, I add the three variables one-by-one.
For that first I click on principle value which is here. Press the multiplication symbol, then the number of years, multiplication and then finally rate of interest.
There I close the bracket and I divide by 100. That gives me 22,500 as the simple interest amount I will get after three years at a rate of interest of 7.5 percentage for a total amount of 100,000 dollars that I deposit.
Now let’s create the formula for compound interest. This formula is slightly more complex because it has more operations than what we are used to already.
You see that there are two levels of brackets and there is a power factor you are raising this whole thing (1 plus rate of interest by 100) is being raised to the power of (number of years). We have not encountered that before and we are going to see how to do it. Now one thing to note in excel sheet is that you do not use different types of bracket.
You use only one type of bracket which is the round bracket. For any levels of brackets you just keep using the round brackets.
Let’s see how to do it. Again for like any formula you start by pressing equal to and choose the principal which is the first value. Then multiply it by opening the first bracket, Open the second bracket so i’m just following what I am seeing in the formula on the side.
Then I do 1 plus, then I click on “rate of interest” here on the cell, divided by 100. There i close the bracket.
Now after closing the bracket, I need to raise this whole thing inside that red bracket to the power of “number of years”. What I use for that is the “caret” symbol. It’s appearing in my keyboard over the number 6.
If i do a Shift-6, I get a Caret. It looks like an upward pointing arrow. Raised to the power of “number of years” which is here 3. Now this whole thing, from that we need to subtract one. There’s a minus one happening.
And finally we close the bracket so we have put the entire formula. We have just seen from left to right what is happening and then put each operation one-by-one.
Finally we confirm the formula by pressing enter. This is the amount that you get in compound interest. Just going to format these two cells so I can select these two cells together by pressing Ctrl.
If i select these and then keep the Ctrl pressed while I select these, both got selected.
And whatever I do to these, whatever formatting i put on this will appear on all the selected cells. I am giving a border and I am giving a background color for the results so there is our formula.
Hope you enjoyed this video so this is a tutorial for creating slightly complex formula. we will advance in the next video to create much more complex operations using excel formula.
Thank you for watching and please remember to like, share, and comment any questions you may have about this video or any opinion you may want to leave. Thank you all.
Read More: How to Create Instagram Reels for 2021