Formulas in MS Excel
Formulas
are the Bread and butter of worksheet. Without formula, worksheet
will be just simple tabular representation of data. A formula
consists of special code, which is entered into a cell. It performs
some calculations and returns a result, which is displayed in the
cell.
Formulas
use a variety of operators and worksheet functions to work with
values and text. The values and text used in formulas can be located
in other cells, which makes changing data easy and gives worksheets
their dynamic nature. For example, you can quickly change the data in
a worksheet and formulas works.
Elements
of Formulas
A
formula can consist of any of these elements −
Mathematical
operators, such as +(for addition) and *(for multiplication)
Example
−
=A1+A2
Adds the values in cells A1 and A2.
Values
or text
Example
−
=200*0.5
Multiplies 200 times 0.15. This formula uses only values, and it
always returns the same result as 100.
Cell
references (including named cells and ranges)
Example
−
=A1=C12
Compares cell A1 with cell C12. If the cells are identical, the
formula returns TRUE; otherwise, it returns FALSE.
Worksheet
functions (such as SUM or AVERAGE)
Example
−
=SUM(A1:A12)
Adds the values in the range A1:A12.
Creating
Formula
For
creating a formula you need to type in the Formula Bar. Formula
begins with '=' sign. When building formulas manually, you can either
type in the cell addresses or you can point to them in the worksheet.
Using the Pointing method to supply the cell addresses for formulas
is often easier and more powerful method of formula building. When
you are using built-in functions, you click the cell or drag through
the cell range that you want to use when defining the function’s
arguments in the Function Arguments dialog box. See the below screen
shot.
As
soon as you complete a formula entry, Excel calculates the result,
which is then displayed inside the cell within the worksheet (the
contents of the formula, however, continue to be visible on the
Formula bar anytime the cell is active). If you make an error in the
formula that prevents Excel from being able to calculate the formula
at all, Excel displays an Alert dialog box suggesting how to fix the
problem.
You
can also Watch this Video :)
what is the formula in subtracting a cell value from another cell value? for example i want to subtract 999 to 89 what kind of formula am I going to use?
TumugonBurahinYou can use this formula, =C1-C2 or =999-89 Subtract the values in cells C1 and C2.
BurahinAnother way to do this task, use the SUM function. Adding a negative number is the same as subtracting.
NOTE: There is no SUBTRACT function in Excel. Use the SUM function and convert any numbers that you want to subtract to their negative values.
For example, SUM(999,-89) returns 910 also.
Naalis ng may-ari ang komentong ito.
TumugonBurahinSo to sum it up excel can function as a calculator too and it makes computing easier.
BurahinYes, but in excel worksheets it is more advance, it can create graphs and to use various functions and theorems. We use formula in excel to compute the data in each cell and you can also insert an alphabetical value in each cell to show the representation of your calculated data in presentable way. That is one of the limitation of calculator, to read the letters.
BurahinAre there any limitations in the given formulas in Microsoft Excel?
TumugonBurahin11-B
Yes, they have also limitations.
BurahinBut there are lots of formula that you can make in MS EXCEL so don't afraid to explore.
Goodevening. Is this part of our upcoming topics?
TumugonBurahin11-B
Of course, because your subject is Empowerment Technology and it includes the Social networks, MS WORD, MS EXCEL, POWERPOINT, and Publisher. So in MS Excel you have to learn on how to use it, including its functions.
BurahinDo you think we can use it in business? Can I calculate the profit that I earned from last week and from the present week?
TumugonBurahinYes, Excel spreadsheets are commonly used across business to display financial information and other data relevant to the running of the business. This could be information relevant to the customer relationship management department, sales, marketing or HR.
BurahinWith the use of formulas in MS EXCEL you can calculate the profit that you earned last week and up to the present week.
for what purpose we can use this microsoft excel??
TumugonBurahinMicrosoft Excel can be very useful for student interactive activities, interactive lectures, and instructor use for developing materials for class. Using Excel as a calculator to explore what mathematical equations can tell us about how the real world works for specific input conditions or for a range of possible values.
BurahinSpreadsheets, files and memos are necessary documents in most businesses. Until computers became part of the typical business office, these types of records were created on paper and stored in metal filing cabinets. Today, these records are created using computer software programs such as Microsoft Word or Excel and filed electronically. MS Word and Excel can be used for many purposes in a company.
BurahinThat's also right there are lots of purposes. It can help in business by creating charts and graphs over time to compare business income and expenditures, for teachers by easily compiling the grades of the students etc. But as of now/as a student it can more help us by reducing the difficulty of plotting data and allows us a means for interpreting the data and it can enhance understanding of content within a graphic presentation of the information; it provides a visual representation of data that makes it easier to analyze.
BurahinGood evening Ma'am, what is the benefit of using foemula in MS Excel?
TumugonBurahinThe primary benefit of using Microsoft Excel is calculating numbers. typing text, coloring cells, saving files.
BurahinGood evening Ma'am, what is the benefit of using foemula in MS Excel?
TumugonBurahinAdditional, Microsoft Excel is perhaps the most important computer software program used in the workplace today. That's why so many workers and prospective employees are required to learn Excel to enter or remain in the workplace.
BurahinIn Business it can help us to build great charts, Use conditional formatting, Help identify trends, Bring data together, and Online access. While in school it can help to the teachers to compute the grades of the student easily.
Excel's main function is to interlink cells, through the use of formulas, so that the formula cell changes when data is inputted into linked cells. The best way to store information in spreadsheets is through the use of formulas.
BurahinYes inserting formulas and calculate numerical value is the main function of MS EXCEL, but besides in inputting and computing numerical information using formulas, it can also store letters like personal profile (names, address etc).
BurahinMa'am Joyce can you give us the advantages and disadvantages of using MS Excel.
TumugonBurahinMicrosoft Excel's advantages and disadvantages depend on the spreadsheet user's goals. Excel contains an extensive list of spreadsheet functions that can be useful to advanced users and financial analysts.
BurahinTo answer your question, I have here some Advantages and disadvantages of MS EXCEL
Advantages:
1. Sent through Emails
Excel can be sent through email and viewed by most
smartphones which makes more convenient .
2. Part of Microsoft Office
Excel is a part of the Microsoft office which comes with most
PC so there is no need to purchase or install it.
3. An All in One Program
Excel is an all in one programme and does not need the addition
of financial subsets.
4. Availability of Training Programs and Training Courses
There is training programs and even training courses to make
users more familiar with Excel.
5. Secure
Excel files can be password protected for extra security. A
user can create a password through Visual Basic programming or
directly within the Excel file.[1]
6. Easy connection to OLAP
Excel is capable of connecting directly to OLAP databases and
can be integrated in Pivot Tables.[2]
Disadvantages:
1. Viruses
Viruses can be attached to an Excel file through macros. Macros
are mini programs that are written into an Excel spread sheet.
2. Slow Execution
Using only one file can make the file size very big and as a
result the program might run slowly.
3. Loss of Data
So you might have to break it into smaller files, by doing so
there is an increased risk in Excel data being lost.
4. Hard to Use
Although there are training programs, it is still hard to use
and some users might not get the hang of it.
5. Space Limit
It limits the number of rows and columns you can use.
SO MAAM JOYCE THIS DISADVANTAGES MAY EFFECT OUR WORK IN MICROSOFT EXCELS ? HOW ABAOUT THIS BUG ? THIS LAGS ? IT ALSO PART OF THE ADVANTAGES IF OUR COMPUTER HAVE BUGS ?
BurahinOf course it can affect, for example, what if your file is attacked by the Macros virus? your file will be destroy by Macros maybe you cannot get/read the information you inputted to this file.
BurahinWhat do you mean, the bugs in MS EXCEL?
The example of bug is =850*77.1 — which gives a result of 100,000 instead of the correct 65,535. It seems that any formula that should evaluate to 65,535 will act strangely.The chance that you would see this in real life calculations is microscopic and the chance of this bug actually mattering to you as an individual is breathtakingly small. So, it is not part of the disadvantage of excel.
Excel helps us to easily input data . For example,as future educator or a future teacher, we need to record the grades of our students. So for me , it will become easier to compute their grades when we use Excel .
TumugonBurahinYes that's right, it can help us in our future profession:)
BurahinMa'am how do we display or hide formulas?
TumugonBurahinYou can control the display of formulas in the following ways:
Burahin1. Switch between displaying formulas and formula results on a worksheet This method is a toggle between two different display modes. It does not change the state of the cells.
2. Prevent formulas from displaying in the formula bar by protecting the cells that contain them
This method changes the state of the cells – protected cells cannot be edited.
3. Show hidden formulas by removing the protection from cells
This method changes the state of the cells – unprotected cells can be edited. Make sure this is an acceptable risk, and make a backup copy of the workbook before you proceed.
Mam, Is there any limitation in using formula in Mswordexcel?
TumugonBurahinYes, they have also limitations.
BurahinBut you can try to create formulas and study some logical functions, you will see that you can use in more formulas in excel.
incredibly helpful thanks
TumugonBurahinYour welcome Ms. Precia :)
BurahinMa'am is there any portable Microsoft Excel that we can use in any places without hassle.. and if there's po mam can you give where can we download it?
TumugonBurahinThere is a portable MS EXCEL and you can download at this website: http://www.portalprogramas.com/en/microsoft-excel-portable-2015-download
BurahinMastering Ms-Excel is a great advantage specially on doing reports, documents and projects, and knowing the basics is the key on mastering Ms-Excel.
TumugonBurahinThanks for the information
good blog
Your welcome Ms.Zabala :)
BurahinHi Maam Joyce! Is there a maximum limit of length of formula contents?
TumugonBurahinYes, The maximum length of formula is 1,024 characters.
BurahinSome Excel are increased from 1,000 to 8,000 it depends on the version.
why i can't enter numbers starting with "0" in excel?
TumugonBurahinExcel converts these number codes to a general or a number format.
BurahinThe reason why we cant enter numbers starting with '0' because the default behavior of those formats is to remove any leading zeros and, depending on the length of the number, use scientific notation.
Ma'am, I learned in your blog, and I believe that I can apply it when Ma'am Dina, introduce to us the Excel. Thank you for giving me/us a new leanings :)
TumugonBurahinYour welcome Namie :)
BurahinKeep on studying Microsoft Excel and enjoy :)
If there have limitations, What is the maximum length of the formula ?
TumugonBurahinThe maximum length of formula is 1,024 characters.
BurahinSome Excel are increased from 1,000 to 8,000 it depends on the version.
If there have limitations, What is the maximum length of the formula ?
TumugonBurahinThe maximum length of formula is 1,024 characters.
BurahinSome Excel are increased from 1,000 to 8,000 it depends on the version.
Goodevening Ma'am.. what are the different types of formula that is used in Excel?
TumugonBurahinThere are two commonly used formula in Microsoft excel, First is Auto Sum is a function in Microsoft Excel and other spreadsheet programs that automatically enters the appropriate formula or function into your spreadsheet and Logical Functions can be used to introduce decision making into your spreadsheet.
BurahinMa'am, Halimbawa po sa MS Excel, may lists of students na may info po about age..mga nasa 17-20..
TumugonBurahinKaya po bang gawing graph yung total percentage ng mga age po ng students?
yes, kayang kaya po yun, walang imposible sa mundo ng microsoft! :)
BurahinYes, all you have to do is to highlight the data that you want to make a graph.
BurahinI like this 👍 I love Math that's why I like this. Hope this is a part our topics. 😊
TumugonBurahinThanks Leona:) Of course this is part of our lesson in Empowerment Technology.
BurahinIs it possible to make a game in Excel?
TumugonBurahinIt is depends on the game you want to make. Its yes, If your game is about calculating/solving. For example, Logic game or arithmetic game. You can use formulas and function in doing this.
BurahinGoodevening ma'am. :) Ma'am why do MS Excel has a formulas? Is it applicable to apply all of these to our MS Excel? :)
TumugonBurahinBecause this is the powerful feature of MS Excel. And yes you may apply this all. But there are some formula that not discussed in this blog. Those formulas are applicable depends on the version of MS Excel.
BurahinMam what other things can we do in ms excel?
TumugonBurahinGood Afternoon Ma'am, what are the other features of ms excel? and why do we need it?
TumugonBurahinMakakaapekto po ba sa buhay natin ang pageexcel
TumugonBurahinMakakaapekto po ba sa buhay natin ang pageexcel
TumugonBurahinHi maam joyce :)
TumugonBurahinMay advantage po ba yung paggamit ng Formulas sa pag eexcel ? :) -AMIHAN
Salamat po pala sa mga Formulas :) -Section B<3
TumugonBurahin