Hello Excel-lent,
Time for a quick weekend poll. What is your favorite tool for data analysis?
1. Formulas
2. Pivot Tables
3. Or both
Post your choice in the comments. Also mention the number of years Excel experience you have.
For ex, my answer is: Both (10 years)
Friday, 4 September 2015
Monday, 31 August 2015
Your September Secret gift....
Hello Excel-lent,
I am glad to welcome you to September, how time flies!
I read Pastor Jimi Tewe's BB broadcast today on "DO IT BIG!" and he mentioned that "by this time tomorrow, we will have only 4 months left in 2015 and although you have done many things and accomplished a number of your goals, there is one thing you are yet to do..."
"When 2015 started, didn't you say to yourself that your accomplishment this year will be mind-blowing? So, apart from you, whose mind has been "blown?"
"Friend, it's time to do it BIG! If you are in Abuja next week Saturday (Sept 12) by 10am, join him as he holds his BIGGEST event in Abuja at (NAF Conference Centre) and it is absolutely free. To register, click this link;
What really touched me about his broadcast was when he asked "apart from you, whose mind has been blown?" I asked myself this same question over and over again and really wanted to know if I had in any way "blown" your mind through "Excelgist"?
I have no intent of "blowing" your mind, but at Excelgist blog, I have one goal, "to make you awesome in Excel and charting" but if you have your mind blown up in the process, then you're are simply excel-lent!
My secret gift for you this September is to learn how to un-protect a locked excel worksheet or workbook. I call it a secret gift because it opens up what's been hidden for you to see!
Do you have a protected Excel worksheet that you want to have full access to? Simply upload this worksheet to Google Spreadsheets using the url;
After uploading, open the worksheet and download it using the File menu and check through the locked cells and columns....I hope this revelation blows your mind!
Welcome to September!
Excel-lently yours,
Oladapo Sorinola
07014282477, 07062932708
BB Pin 52E9802D
Thursday, 27 August 2015
1.5 -Working with the order of operations- "BODMAS"
Hello Excel-lent,
This topic brings back good old memories of our primary/elementary days in school when we were taught "Arithmetic" and a resounding "State and capital".... "Wow, things have changed!
Reciting through the "Times Table" was something I never liked then because of the long "Metric Arithmetical Tables" that was more of a song....100 metre makes 1 kilometer, 365 days make 1 year, 366 days make 1 leap year and off course the long 2 times 1 two, 2 time 2 four, 2 time 3 six. I liked the State and capital more because of its rhythmic sequence, Sokoto- Sokoto, Ogun-Abeokuta, Niger-Minna, Plateau-Jos!....but today i am glad i did sang those songs using the book below in illustration 1.
I almost forgot this- 30 days has September, April, June and November...
Illustration 1.
Picture source: http://www.nairaland.com/389532/u-did-not-use-exercise
Moving to higher classes, we were then exposed to one of the mathematical rules that turned on my hatred for "Quantitative Aptitude", why because I failed like no man's business and not until i learnt the simple rules behind BODMAS that i began to pass with good grades.
BODMAS is the simple rule guiding the order of mathematical operations and the simple truth why you get some mathematical calculations wrong is because you flaw this rule. Now, let's go back to class as I still remember how the blackboard of St. Bernadette's Primary School, Abeokuta looks like
As simple as this may sound, using it wrongly has caused many companies to suffer financial loss arising from using bad Financial Models that does not obey the BODMAS rule. As a matter of fact, some employees salary has been wrongly calculated (shortchanged) from wrong use of BODMAS whilst calculating tax, prorated leave allowances and other deductions!
Some experience can be the worst teacher...
Excel-lently yours,
Oladapo Sorinola
07014282477, 07062932708
BB Pin 52E9802D
This topic brings back good old memories of our primary/elementary days in school when we were taught "Arithmetic" and a resounding "State and capital".... "Wow, things have changed!
Reciting through the "Times Table" was something I never liked then because of the long "Metric Arithmetical Tables" that was more of a song....100 metre makes 1 kilometer, 365 days make 1 year, 366 days make 1 leap year and off course the long 2 times 1 two, 2 time 2 four, 2 time 3 six. I liked the State and capital more because of its rhythmic sequence, Sokoto- Sokoto, Ogun-Abeokuta, Niger-Minna, Plateau-Jos!....but today i am glad i did sang those songs using the book below in illustration 1.
I almost forgot this- 30 days has September, April, June and November...
Illustration 1.
Picture source: http://www.nairaland.com/389532/u-did-not-use-exercise
Moving to higher classes, we were then exposed to one of the mathematical rules that turned on my hatred for "Quantitative Aptitude", why because I failed like no man's business and not until i learnt the simple rules behind BODMAS that i began to pass with good grades.
BODMAS is the simple rule guiding the order of mathematical operations and the simple truth why you get some mathematical calculations wrong is because you flaw this rule. Now, let's go back to class as I still remember how the blackboard of St. Bernadette's Primary School, Abeokuta looks like
"Operations" mean things like add, subtract, multiply, divide, squaring, etc. If it isn't a number it is probably an operation.
But, when you see something like...
7 + (6 × 52 + 3)
... what part should you calculate first?
Start at the left and go to the right?
Or go from right to left?
Start at the left and go to the right?
Or go from right to left?
Calculate them in the wrong order, and you will get a wrong answer !
So, long ago people agreed to follow rules when doing calculations, and they are:
Order of Operations
Do things in Brackets First. Example:
 | 6 × (5 + 3) | = | 6 × 8 | = |
48
| ||
 | 6 × (5 + 3) | = | 30 + 3 | = |
33
| (wrong) |
Exponents (Powers, Roots) before Multiply, Divide, Add or Subtract. Example:
| 5 × 22 | = | 5 × 4 | = |
20
| ||
| 5 × 22 | = | 102 | = |
100
| (wrong) |
Multiply or Divide before you Add or Subtract. Example:
| 2 + 5 × 3 | = | 2 + 15 | = |
17
| ||
| 2 + 5 × 3 | = | 7 × 3 | = |
21
| (wrong) |
Otherwise just go left to right. Example:
| 30 ÷ 5 × 3 | = | 6 × 3 | = |
18
| ||
| 30 ÷ 5 × 3 | = | 30 ÷ 15 | = |
2
| (wrong) |
How Do I Remember It All ... ? BODMAS !
B
| Brackets first |
O
| Orders (ie Powers and Square Roots, etc.) |
DM
| Division and Multiplication (left-to-right) |
AS
| Addition and Subtraction (left-to-right) |
The Order of Operations in Excel Formulas
Spreadsheet programs such as Excel and Google Spreadsheets have a number of arithmetic operators that are used in formulas to carry out basic mathematical operations such as addition and subtraction.
If more than one operator is used in a formula, there is a specific order of operations that Excel and Google Spreadsheets follow in calculating the formula's result
The Order of Operations is:
Brackets "( ) "
Orders (Powers and Square Roots, offs)
Division
Multiplication
Addition
Subtraction
An easy way to remember this is to use the acronym formed from the first letter of each word in the order of operations:
B O D M A S
How the Order of Operations Works
1. Any operation(s) contained in round brackets will be carried out first.
2. Second, any calculations involving exponents will occur.
3. After that, Excel considers division or multiplication operations to be of equal importance and carries out these operations in the order they occur left to right in the formula.
4. The same goes for the next two operations – addition and subtraction. They are considered equal in the order of operations. Whichever one appears first in an equation, either addition or subtraction is the operation carried out first.
Changing the Order of Operations in Excel Formulas
Since round brackets are first in the list, it is quite easy to change the order in which mathematical operations are carried out simply by adding brackets around those operations we want to occur first.
As simple as this may sound, using it wrongly has caused many companies to suffer financial loss arising from using bad Financial Models that does not obey the BODMAS rule. As a matter of fact, some employees salary has been wrongly calculated (shortchanged) from wrong use of BODMAS whilst calculating tax, prorated leave allowances and other deductions!
Some experience can be the worst teacher...
Excel-lently yours,
Oladapo Sorinola
07014282477, 07062932708
BB Pin 52E9802D
Monday, 24 August 2015
1.4-Creating a basic formula -Part 2
Hello Excel-lent,
Welcome to the last week of August! A week full of expectations for both employer and employee, debtor and creditor.
Last week, we started out with creating a basic formula in excel and I recall mentioning that formulas are the engine room of Excel. We will continue this week, taking some deep look into how we can create basic formulas.
Creating a particular formula in excel, is determined by the issue at hand, that is, what mathematical, statistical or logical problem you want excel to solve for you? After identifying this problem and stating it then can you also identify the best formula to apply. There exists quite over 500 excel formula functions BUT you don't need to know them all, only learn and know the few ones that applies to your need and be able to reference them when you hear the call.
In Ms Excel, we have formulas ranging from Statistical to logical as shown in illustration 1 below and each of these formulas or a combo of them are helpful in solving operational issues.
Illustration 1.
Read http://excelgist.blogspot.com/2015/07/discover-these-15-extremely-powerful.html and http://excelgist.blogspot.com/2015/07/discover-these-15-extremely-powerful_6.html to see some TEXT & DATE functions and how they work.
For a creditor who had given out loans and wants to keep track of who is owing and repayments dates, a simple basic formula can be used on excel to track this just as we have in illustration 2 below.
Illustration 2.
As at today 24th of August, 2015, we are 65% and 235 days away into the year 2015, leaving out 129 days to go which represents 35%. How do we get this without marking or counting each day of the calendar? A simple "YEARFRAC" and "DAYS" formulas are used as shown below in illustration 3. If you decide to keep this simple calendar without updating the End Period date every day, you can simply type in "=TODAY()" formula where you have 8/24/2015 so that excel updates the date each time you open the worksheet.
Illustration 3.
Click " Join Excelgist " to receive Excelgist discussions directly to your mail box
Have a blessed week ahead.
Oladapo Sorinola
07014282477, 07062932708
Welcome to the last week of August! A week full of expectations for both employer and employee, debtor and creditor.
Last week, we started out with creating a basic formula in excel and I recall mentioning that formulas are the engine room of Excel. We will continue this week, taking some deep look into how we can create basic formulas.
Creating a particular formula in excel, is determined by the issue at hand, that is, what mathematical, statistical or logical problem you want excel to solve for you? After identifying this problem and stating it then can you also identify the best formula to apply. There exists quite over 500 excel formula functions BUT you don't need to know them all, only learn and know the few ones that applies to your need and be able to reference them when you hear the call.
In Ms Excel, we have formulas ranging from Statistical to logical as shown in illustration 1 below and each of these formulas or a combo of them are helpful in solving operational issues.
Illustration 1.
Read http://excelgist.blogspot.com/2015/07/discover-these-15-extremely-powerful.html and http://excelgist.blogspot.com/2015/07/discover-these-15-extremely-powerful_6.html to see some TEXT & DATE functions and how they work.
For a creditor who had given out loans and wants to keep track of who is owing and repayments dates, a simple basic formula can be used on excel to track this just as we have in illustration 2 below.
Illustration 2.
As at today 24th of August, 2015, we are 65% and 235 days away into the year 2015, leaving out 129 days to go which represents 35%. How do we get this without marking or counting each day of the calendar? A simple "YEARFRAC" and "DAYS" formulas are used as shown below in illustration 3. If you decide to keep this simple calendar without updating the End Period date every day, you can simply type in "=TODAY()" formula where you have 8/24/2015 so that excel updates the date each time you open the worksheet.
Illustration 3.
Click " Join Excelgist " to receive Excelgist discussions directly to your mail box
Have a blessed week ahead.
Oladapo Sorinola
07014282477, 07062932708
Friday, 21 August 2015
1.4-Creating a basic formula
Hello Excel-lent,
TGIF!
Hope you're doing great and how has your week been? The week has been tough emotionally but I thank God for His goodness and mercy!
I was going to ask if you had ever used any excel formula before and for what purpose? That sounds like "are you serious at all?", but the factual truth is many have not seen the spreadsheet grid lines before or for those who had seen it, seeing was only what they could do!
We agreed last week that formulas are one of the most commonly used features of Excel and they can be used to carry out simple addition and subtraction or far more complex mathematical calculations. All formulas in Excel, no matter how complex, always begin with the same two steps:
TGIF!
Hope you're doing great and how has your week been? The week has been tough emotionally but I thank God for His goodness and mercy!
I was going to ask if you had ever used any excel formula before and for what purpose? That sounds like "are you serious at all?", but the factual truth is many have not seen the spreadsheet grid lines before or for those who had seen it, seeing was only what they could do!
We agreed last week that formulas are one of the most commonly used features of Excel and they can be used to carry out simple addition and subtraction or far more complex mathematical calculations. All formulas in Excel, no matter how complex, always begin with the same two steps:
- Click on the cell where you want the formula's result to be displayed.
- Type an equal sign ( = ) to let Excel know you are creating a formula
In the example below, I want my formula's result to be displayed in cell "B3", so i put my cursor in cell "B3" as shown below..
Illustration 1.
With that done, i can then think of what i want excel to calculate for me. I can do a basic calculation of how much i need to pay if i buy 25 litres of petrol at N87/ litre. In the example below, I have "Litres" entered in cell B1 and "Rate" in cell C1 as titles while i have 25 and 87 captured in cells B2 and C2 respectively. Because i want my formula's result to show in cell B3, i then type in the "equals to = sign" and using my cursor, reference cells B2 and C2 with a multiplication sign embedded like this =B2*C2. Once this is done, hit the enter button and gbam.....excel gives you your result.
Illustration 2.
Illustration 3.
From this simple formula, you can then graduate to building complex formulas like what you see in Illustration 4 below.
Illustration 4.
With excel + a sound knowledge of formulas, you are bound to excel at your workplace and save a lot of time spent stumbling in a data jungle.
Please, if your answer is not 2,175 in the example above, kindly let me know but if you buy 25 litres Petrol at any filling station and they sell it for more than N2,175.00 (Two Thousand, One Hundred And Seventy Five Naira only) at N87 per litre, please report to DPR on 01-261 8228 or visit 5, Kofo Abayomi Street, Victorial Island, Lagos.
Have a Cntrl + A weekend, where "A" ="Awesome"
Oladapo Sorinola
07014282477, 07062932708
BB Pin 52E9802D
Thursday, 13 August 2015
1.3- Entering and editing formulas
Hello Excel-lent,
I can't help you reduce Lagos traffic but i can help you work faster and be efficient at your workplace using Ms Excel. There is no reason why you shouldn't be excel-lent at work and that's the more reason why you should be part this and subsequent sessions of our Excel online training as we would be talking and discussing "FORMULAS".... the engine room of Excel J
In today's session, I would be introducing you to "how to enter and edit formulas" in Excel. There exists about over 500 formula in excel ranging from Statistical, Mathematics and Trigonometry,Financial, Engineering, Text, Date & Time, Lookup and Reference, Information, Database, Cube and Logical functions. You don't need to know them all but you need to be able to use the ones that speaks to your daily operational workplace need.
Excel Formula Overview
Formulas are one of the most commonly used features of Excel. They can be used to carry out simple addition and subtraction or far more complex mathematical calculations.
All formulas in Excel, no matter how complex, always begin with the same two steps:
Many formulas in Excel perform basic mathematical calculations such as subtraction and multiplication.
For these formulas, after the two steps listed above, we only need to add, in the correct order, the data to be used in the calculations and the mathematical operators that tell Excel which mathematical operation to perform.
Using Cell References in Formulas
Rather than enter the data directly into a formula, it is better to enter the cell references where the data is located into the formula.
The advantages of this are that:
The easiest and best way to add cell references to a formula is to use pointing, which means to click with the mouse pointer on the cell containing the data you want added to the formula.
Click " Join Excelgist " to receive Excelgist discussions directly to your mail box.
Excel-lently yours,
Oladapo Sorinola
07014282477, 07062932708
BB Pin 52E9802D
I can't help you reduce Lagos traffic but i can help you work faster and be efficient at your workplace using Ms Excel. There is no reason why you shouldn't be excel-lent at work and that's the more reason why you should be part this and subsequent sessions of our Excel online training as we would be talking and discussing "FORMULAS".... the engine room of Excel J
In today's session, I would be introducing you to "how to enter and edit formulas" in Excel. There exists about over 500 formula in excel ranging from Statistical, Mathematics and Trigonometry,Financial, Engineering, Text, Date & Time, Lookup and Reference, Information, Database, Cube and Logical functions. You don't need to know them all but you need to be able to use the ones that speaks to your daily operational workplace need.
Excel Formula Overview
Formulas are one of the most commonly used features of Excel. They can be used to carry out simple addition and subtraction or far more complex mathematical calculations.
All formulas in Excel, no matter how complex, always begin with the same two steps:
- Click on the cell where you want the formula's result to be displayed.
- Type an equal sign ( = ) to let Excel know you are creating a formula.
Many formulas in Excel perform basic mathematical calculations such as subtraction and multiplication.
For these formulas, after the two steps listed above, we only need to add, in the correct order, the data to be used in the calculations and the mathematical operators that tell Excel which mathematical operation to perform.
Using Cell References in Formulas
Rather than enter the data directly into a formula, it is better to enter the cell references where the data is located into the formula.
The advantages of this are that:
- If you later change your data the formula automatically updates to show the new result
- In certain instances, using cell references makes it possible to copy formulas from one location to another in a worksheet
The easiest and best way to add cell references to a formula is to use pointing, which means to click with the mouse pointer on the cell containing the data you want added to the formula.
Click " Join Excelgist " to receive Excelgist discussions directly to your mail box.
Excel-lently yours,
Oladapo Sorinola
07014282477, 07062932708
BB Pin 52E9802D
Monday, 10 August 2015
N500M legal suit for data error!
Hello Excellent,
On Tuesday, July 21st, 2015 when I shared "Eight of the worst spreadsheet blunders" http://excelgist.blogspot.com/2015/07/eight-of-worst-spreadsheet-blunders.html, it was very unfortunate that i couldn't lay my hands on any "local' sample as all the examples shared were foreign but what happened last week is an indication that "data error" has no boundary!
When an error occurs, it may take a whole lot of ones resources to clean out the mess, depending on the magnitude though but you will agree that a cleanser worth N500M ($2.38M equivalent ) in law suit must have been a very BIG and MASSIVE error!
Image Source: Punch and ThisDay Newspapers, Friday 07, August 2015
Offcourse, the list must have been prepared by someone, and probably reviewed by another senior personnel but one thing people miss out on data is that Data is information and can either make or mar depending on how it's been processed. It could have been a case of errant cut-and-paste, it could have been this, it could have been that but my worry is that how did a name that is not meant to be on a Director's list appear on a Director's list? A Director of a company is not just an employee but someone who can be held liable for the operations or shortcomings of the company as in the case of "Thriller Endeavours" and "Diamond Bank". This is a big data error and I wonder how many of this we have on banks databases. More shocking revelations may be revealed if we conduct a litmus test across board.
When University of Toledo lost $2.4M in Projected Revenue caused by "an internal budgeting error", President Daniel Johnson said "No job action would be taken against the employee who made the mistake, who has good performance record", I only wish same would be said and no job action would be taken against the employee or employees involved in this error.
Good a thing they have apologized, else N500M would have eaten down their profits for the year. You can read more here for full details
http://dailypost.ng/2015/08/07/diamond-bank-apologizes-to-abike-dabiri-for-erroneously-identifying-her-as-debtor/
http://leadership.ng/news/452279/debtors-list-abike-dabiri-slams-n500m-suit-on-diamond-bank
How excel-lent are you with Data? Judge yourself today before it is too late
Oladapo Sorinola
BB pin 52E9802D
07014282477, 07062932708
On Tuesday, July 21st, 2015 when I shared "Eight of the worst spreadsheet blunders" http://excelgist.blogspot.com/2015/07/eight-of-worst-spreadsheet-blunders.html, it was very unfortunate that i couldn't lay my hands on any "local' sample as all the examples shared were foreign but what happened last week is an indication that "data error" has no boundary!
When an error occurs, it may take a whole lot of ones resources to clean out the mess, depending on the magnitude though but you will agree that a cleanser worth N500M ($2.38M equivalent ) in law suit must have been a very BIG and MASSIVE error!
Offcourse, the list must have been prepared by someone, and probably reviewed by another senior personnel but one thing people miss out on data is that Data is information and can either make or mar depending on how it's been processed. It could have been a case of errant cut-and-paste, it could have been this, it could have been that but my worry is that how did a name that is not meant to be on a Director's list appear on a Director's list? A Director of a company is not just an employee but someone who can be held liable for the operations or shortcomings of the company as in the case of "Thriller Endeavours" and "Diamond Bank". This is a big data error and I wonder how many of this we have on banks databases. More shocking revelations may be revealed if we conduct a litmus test across board.
When University of Toledo lost $2.4M in Projected Revenue caused by "an internal budgeting error", President Daniel Johnson said "No job action would be taken against the employee who made the mistake, who has good performance record", I only wish same would be said and no job action would be taken against the employee or employees involved in this error.
Good a thing they have apologized, else N500M would have eaten down their profits for the year. You can read more here for full details
http://dailypost.ng/2015/08/07/diamond-bank-apologizes-to-abike-dabiri-for-erroneously-identifying-her-as-debtor/
http://leadership.ng/news/452279/debtors-list-abike-dabiri-slams-n500m-suit-on-diamond-bank
How excel-lent are you with Data? Judge yourself today before it is too late
Oladapo Sorinola
BB pin 52E9802D
07014282477, 07062932708
Subscribe to:
Posts (Atom)