Excel Basic – Looks can be deceiving
Once upon a time, I was questioned about the accuracy of calculation in Excel just because one saw a formula “3% x 100” (A1*B1) yielding a result of 2.5 (C1). WHY????
As a basic but important rule, DO NOT trust what you see at the first sight in real world as well as in Excel.
What you see in a cell does not have to be what it is actually. What you see in a cell could be a makeup someone puts on it. A makeup makes the cell look nicer; or even turns the cell into something else totally.
Take a look at below and you will have a better understand.
Take a look at below and you will have a better understand.
The above should be self-explanatory. Nonetheless a brief explanation may help you better…
In A2, although you see “3%” in the cell, it is actually 2.5% being round up with no decimal place. That’s why we’ve got the result 2.5 instead of 3.
A3 – Value of 0.03 formatted as percentage with no decimal. It yield a result of 3 when multiplying by 100 of course.
A4 – a Text “123k” is input. If you apply a calculation to it, you will get #value.
A5 – a Value 123456 formatted as #,##0,k. Thus it is displayed as 123k. As a result, when it is multiplied by 100, you get 12345600
A6 – it looks really like a number but when we click into it, you will see the apostrophe comma ‘ in front of 100. If we start inputting anything with ‘, it tells Excel that we are going to input Text. Did you see the little green triangle on the upper left of the cell? If you click on the cell, you’ll see the smart tag next to the cell. Click the smart tag, it will tell you the value is stored as text.
A7 – A value of 100 being input normally. It is a value of course.
A8 – A date of 25/12/2013. When you input date correctly (e.g. 32/12/2013 is not a date as we don’t have 32 days in any month), Excel interprets it as Date automatically. In Excel, date is actually stored as value starting from 1 for 1/1/1900. 25/12/2013 means the 41633th day from 1/1/1900. If you change the format of the cell from Date to General or Number, you get 41633.
A9 – “Time values are a part of a date value and are represented by a decimal number (for example, 12:00 PM is represented by 0.5 because it is exactly halfway through a day)” – Extract from Excel Help
So far, did you notice that Text is being aligned to the left while Number is being aligned to the right. This is the default setting of Excel and gives you a quick indication of whether the celling content is a Text or a Number.
- Aligned to Left = Text
- Aligned to Right = Number
(Note: Such indication will be lost if you apply Centre Alignment to the cell)
A10 and A11 looks exactly the same to human eyes, but not to computer.
How does it matter?
Don’t overlook the importance of it. When you perform LOOKUP related functions, it could give you incorrect result without any notice.
Don’t overlook the importance of it. When you perform LOOKUP related functions, it could give you incorrect result without any notice.
As the same token, the difference between ‘100 (text in A6) and 100 (number in A7) would give you unexpected LOOKUP result. Excel considers that you are looking for a Text while in your lookup table, only Number is found (or vice versus). Therefore you will get #N/A even though you see the lookup value by your eyes!!!!!
If you are in doubt, always look into the formula bar or check the cell format.
Most people including myself did not learn Excel from scratch, that’s why we miss some important basics to master it.
Finally,
Before using a calculator to challenge a computer, check the cell format or formula first!
📤You download App EVBA.info installed directly on the latest phone here : https://www.evba.info/p/app-evbainfo-setting-for-your-phone.html?m=1
Hi, I'm Mr KING Admin of KINGEXCEL.INFO .Thank you so much and I hope you will have an interesting time with KINGEXCEL.INFO. Mail: quantriexcel@gmail.com - PHONE : +84779313987.