Help, examples, and information on Excel formulas

Help, examples, and information on Excel formulas

Formulas are what helped make spreadsheets so popular. By creating formulas, you can have quick calculations even if the information changes in the cells relating to the formula. For example, you could have a total cell that adds all values in a column.

The basics
All spreadsheet formulas begin with an equal sign (=) symbol.
After the equal symbol, either a cell or formula function is entered. The function tells the spreadsheet the type of formula.
If a math function is being performed, the math formula is surrounded in parentheses.
Using the colon (:) allows you to get a range of cells for a formula. For example, A1:A10 is cells A1 through A10.
Formulas are created using relative cell reference by default and if you add a dollar sign ($) in front of the column or row it will become an absolute cell reference.

Entering a spreadsheet formula

Below is an animated visual example of how an excel formula can be inserted into a spreadsheet. In our first formula entered into the cell "D1," we manually enter a =sum formula to add 1+2 (in cells A1 and B2) to get the total of "3." With the next example, we use the mouse to highlight cells A2 to D2 and then instead of typing the formula use the formula button in Excel to automatically create the formula. Next, we show how you can manually enter a formula and then with the help of a mouse get the cell values (you can also highlight multiple cells to create a range). Finally, we manually enter a times ( * ) formula using sum function to find the value of 5 * 100.

 

Formula examples
Note: The functions listed below may not be the same in all languages of Microsoft Excel. All these examples are done in the English version of Microsoft Excel.

Tip: All of the examples below are listed in alphabetical order, if you want to start with the most common formula, we suggest starting with the =SUM formula.

• =
• AVERAGE
• COUNT
• COUNTA
• COUNTIF
• IF
• INDIRECT
• MEDIAN
• MIN AND MAX
• PRODUCT
• RAND
• RANDBETWEEN
• SUM
• SUMIF
• TODAY
• TREND
• VLOOKUP

=

An = (equals) creates a cell equal to another. For example, if you were to put =A1 in B1 whatever was in A1 would automatically be put in B1. You could also create a formula that would make one cell equal to more than one value. For example, if you have a first name in cell A1 and a last name in cell B1, you could put in cell A2 =A1&" "&B1 which would put cell A1 in with B1 with a space between. You can also use a concatenate formula to combine cell values.

AVERAGE

Display the average amount between cells. For example, if you wanted to get the average for cells A1 to A30, you would type: =AVERAGE(A1:A30).

 
COUNT

Count the number of cells in a range that contain only numbers. For example, you could find how many cells between A1 and A15 contain a numeric value by using the =COUNT(A1:A15). If cell A1 and A5 only contained numbers the value of the cell that contains this function would be equal to "2."

COUNTA

Count the number of cells in a range that contain any text (text and numbers, not just numbers) and are not empty. For example, you could count the number of cells containing text in cells A1 through A20 by using the =COUNTA(A1:A20). If seven cells were empty the number "13" would be returned.

COUNTIF

Count the cells that have a certain value. For example, if you have =COUNTIF(A1:A10,"TEST") in cell A11, then any cell between A1 through A10 that has the word "test" will be counted as one. So, if you have five cells in that range that contain the word test, A11 would say "5."

IF

The syntax of the IF statement are =IF(CELL="VALUE" ,"PRINT OR DO THIS","ELSE PRINT OR DO THIS"). For example, the formula =IF(A1="","BLANK","NOT BLANK") makes any cell besides A1 say "BLANK" if a1 had nothing within it. If A1 is not empty, the other cells will read "NOT BLANK". The if statement has more complex uses, but can generally be reduced to the above structure.

INDIRECT

 

Returns a reference specified by a text string. In the above example, the formula would return the value of the cell contained in A2.

Returns the value of a random cell between A1 and A2 using the indirect and randbetween (explained below) functions.

MEDIAN

 
MIN AND MAX
Find the median of the values of cells A1 through A7. For example, four is the median for 1, 2, 3, 4, 5, 6, 7.

Min and Max represent the minimum or maximum amount in the cells. For example, if you wanted to get the minimum value between cells A1 and A30 you would put =MIN(A1:A30) or if you wanted to get the Maximum about =MAX(A1:A30).

PRODUCT

Multiples multiple cells together. For example, =Product(A1:A30) would multiple all cells together, so A1 * A2 * A3, etc.

RAND
 

Generates a random number greater than zero but less than one, For example, "0.681359187" could be a randomly generated number placed into the cell of the formula.

RANDBETWEEN

Generate a random number between two values. In the above example, the formula would create a random whole number between 1 and 100.

SUM

 

The most commonly used function to add, subtract, multiple, or divide values in cells. Below are some examples.

Add the cells A1 and A2.

Add cells A1 through A5.

 

Adds cells A1, A2, and A5.
 

Subtract cell A1 from A2.

Multiply cells A1 and A2.

 

Divide cell A1 by A2.
 

SUMIF 

Perform the SUM function only if there is a specified value in the first selected cells. An example of this would be =SUMIF(A1:A6,"TEST",B1:B6) which only adds the values B1:B6 if the word "test" was put somewhere in between A1:A6. So if you put TEST (not case sensitive) in A1, but had numbers in B1 through B6, it would only add the value in B1 because TEST is in A1.

See our SUMIF definition for additional information.
 
TODAY 

 

Would print out the current date in the cell entered. This value will change to reflect the current date each time you open your spreadsheet. If you want to enter a date that doesn't change hold down Ctrl and ; (semicolon) to enter the date.

TREND 

 

To find the common value of cell. For example, if cells A1 through A6 had 2,4,6,8,10,12 and you entered formula =TREND(A1:A6) in a different cell, you would get the value of 2 because each number is going up by 2.

VLOOKUP

 

The lookup, hlookup, or vlookup formula allows you to search and find related values for returned results. See our lookup definition for a complete definition and full details on this formula.

Related Articles:)
Preventing a spreadsheet formula from changing when copying.
See the Excel and formula definition for further information and related links.
Microsoft Excel help and support.

Read More

How to copy a web page link or URL

How to copy a web page link or URL

Duplicating the linking address (URL) of the web page you are viewing can be achieved by highlighting and copying the text in the address bar, and then pasting it into another program (e.g. e-mail). Below are other ways to go about this process.

• Copy an address link (URL) on a desktop or laptop
• Copy an address link on a tablet or smartphone
• Copying a YouTube video link on website and app
• Shortening a long URL
• Copying a link or picture address

Copying an address (URL) using a keyboard and mouse

Highlight the address by moving your mouse cursor over the address bar and clicking the left mouse button once or press the F6 keyboard shortcut to get into the address bar.

After the address has been highlighted, press Ctrl+C or Cmd+C on the keyboard to copy it. You can also right-click any highlighted section and choose Copy from the drop-down menu.

Once the address is copied, paste that address into another program by clicking a blank field and pressing Ctrl+V or Cmd+V. You may also right-click any highlighted section and choose Paste from the drop-down menu.

Copying an address on a mobile device

Open your preferred mobile Internet browser.

Locate the address link you would like to copy.

Note: If the address bar is not visible, try scrolling up using your finger.
Tap the address bar once to highlight all of the text contained within it.
Long press the highlighted text and select Copy.

On smaller devices, the symbol for copying text looks like two identical pieces of paper.
Open the app where you would like to paste the address.

Long press again and select Paste.

Copying a YouTube video link on website and app

Tip: You can also use the how to copy a URL on a computer and how to copy a URL on a mobile device mentioned above to copy a YouTube video link. We're including the steps below as an alternative method of copying a video link.

On the desktop and mobile you can click the share icon that should resemble the share icon shown in the picture below. Once you click the share link, in addition to the social networking services you should see the URL of the video that can be copied and pasted anywhere.

Shortening a long address or URL

Some web pages may have a long URL that can present problems when it is pasted into another program, especially an e-mail. If the link you are interested in is long (more than 60 characters), copy and paste the URL into a web page that creates a short link. Our favorite short link service is Tiny URL.

Full listing of services that help create small easy to read URL's

Tip: There are also many browser add-ons that you can utilize to make copying long addresses easier.

Copying a link or image within a page

To copy the address to a link or image within a web page, right-click the link or image and select Copy link address or Copy image from the drop-down menu that appears. Alternatively, users may view the properties of the link and copy the URL from within that menu.

Full steps on how to copy text and images from a web page.


Additional information

See the copy, hyperlink, paste, and URL definitions for further information and help on these terms.

Read More

Help, examples, and information on Excel formulas

Help, examples, and information on Excel formulas

Formulas are what helped make spreadsheets so popular. By creating formulas, you can have quick calculations even if the information changes in the cells relating to the formula. For example, you could have a total cell that adds all values in a column.

The basics
All spreadsheet formulas begin with an equal sign (=) symbol.
After the equal symbol, either a cell or formula function is entered. The function tells the spreadsheet the type of formula.

If a math function is being performed, the math formula is surrounded in parentheses.
Using the colon (:) allows you to get a range of cells for a formula. For example, A1:A10 is cells A1 through A10.
Formulas are created using relative cell reference by default and if you add a dollar sign ($) in front of the column or row it will become an absolute cell reference.
Entering a spreadsheet formula
Below is an animated visual example of how an excel formula can be inserted into a spreadsheet. In our first formula entered into the cell "D1," we manually enter a =sum formula to add 1+2 (in cells A1 and B2) to get the total of "3." With the next example, we use the mouse to highlight cells A2 to D2 and then instead of typing the formula use the formula button in Excel to automatically create the formula. Next, we show how you can manually enter a formula and then with the help of a mouse get the cell values (you can also highlight multiple cells to create a range). Finally, we manually enter a times ( * ) formula using sum function to find the value of 5 * 100.

Formula examples
Note: The functions listed below may not be the same in all languages of Microsoft Excel. All these examples are done in the English version of Microsoft Excel.

Tip: All of the examples below are listed in alphabetical order, if you want to start with the most common formula, we suggest starting with the =SUM formula.

1. =
2. AVERAGE
3. COUNT
4. COUNTA
5. COUNTIF
6. IF
7. INDIRECT
8. MEDIAN
9. MIN AND MAX
10. PRODUCT
11. RAND
12. RANDBETWEEN
13. ROUND
14. SUM
15. SUMIF
16. TODAY
17. TREND
18. VLOOKUP

=
            =
An = (equals) creates a cell equal to another. For example, if you were to put =A1 in B1 whatever was in A1 would automatically be put in B1. You could also create a formula that would make one cell equal to more than one value. For example, if you have a first name in cell A1 and a last name in cell B1, you could put in cell A2 =A1&" "&B1 which would put cell A1 in with B1 with a space between. You can also use a concatenate formula to combine cell values.

AVERAGE

         =AVERAGE(X:X)

Display the average amount between cells. For example, if you wanted to get the average for cells A1 to A30, you would type: =AVERAGE(A1:A30).

COUNT

          =COUNT(X:X)
Count the number of cells in a range that contain only numbers. For example, you could find how many cells between A1 and A15 contain a numeric value by using the =COUNT(A1:A15). If cell A1 and A5 only contained numbers the value of the cell that contains this function would be equal to "2."

COUNTA

            =COUNTA(X:X)
Count the number of cells in a range that contain any text (text and numbers, not just numbers) and are not empty. For example, you could count the number of cells containing text in cells A1 through A20 by using the =COUNTA(A1:A20). If seven cells were empty the number "13" would be returned.

COUNTIF

            =COUNTIF(X:X,"*")

Count the cells that have a certain value. For example, if you have =COUNTIF(A1:A10,"TEST") in cell A11, then any cell between A1 through A10 that has the word "test" will be counted as one. So, if you have five cells in that range that contain the word test, A11 would say "5."

IF

=IF(*)

The syntax of the IF statement are =IF(CELL="VALUE" ,"PRINT OR DO THIS","ELSE PRINT OR DO THIS"). For example, the formula 

=IF(A1="","BLANK","NOT BLANK") makes any cell besides A1 say "BLANK" if A1 had nothing within it. If A1 is not empty, the other cells will read "NOT BLANK". 

The IF statement has more complex uses, but can generally be reduced to the above structure.

Using IF can also be useful for times you may want to calculate values in a cell only if those cells contains values. 

For example, you may be dividing the values between two cells. However, if there is nothing in the cells you would get the #DIV/0! error. Using the IF statement you can only calculate a cell if it contains a value. 

For example, if you only wanted to perform a divide function if A1 contains a value you could type: =IF(A1="","",SUM(B1/A1)) which only divides cell B1 into A1 if A1 contains text. Otherwise, the cell is left blank.

INDIRECT

=INDIRECT("A"&"2")

Returns a reference specified by a text string. In the above example, the formula would return the value of the cell contained in A2.

=INDIRECT("A"&RANDBETWEEN(1,10))

Returns the value of a random cell between A1 and A2 using the indirect and randbetween (explained below) functions.

MEDIAN

=MEDIAN(A1:A7)

MIN AND MAX
 
Find the median of the values of cells A1 through A7. For example, four is the median for 1, 2, 3, 4, 5, 6, 7.

=MIN/MAX(X:X)

Min and Max represent the minimum or maximum amount in the cells. For example, if you wanted to get the minimum value between cells A1 and A30 you would put =MIN(A1:A30) or if you wanted to get the Maximum about =MAX(A1:A30).

PRODUCT

=PRODUCT(X:X)

Multiples multiple cells together. For example, =Product(A1:A30) would multiple all cells together, so A1 * A2 * A3, etc.

RAND

           =RAND()
Generates a random number greater than zero but less than one, For example, "0.681359187" could be a randomly generated number placed into the cell of the formula.

RANDBETWEEN

           =RANDBETWEEN(1,100)
Generate a random number between two values. In the above example, the formula would create a random whole number between 1 and 100.

ROUND
           =ROUND(X,Y)

Round a number to a specific number of decimal places. X is the Excel cell containing the number to be rounded. Y is the number of decimal places to round. Below are some examples.

             =ROUND(A2,2)

Rounds the number in cell A2 to one decimal place. If the number is 4.7369, the above example would round that number to 4.74. If the number is 4.7614, it would round to 4.76.

             =ROUND(A2,0)

Rounds the number in cell A2 to zero decimal places, or the nearest whole number. If the number is 4.736, the above example would round that number to 5. If the number is 4.367, it would round to 4.

SUM
          =SUM(X:X)

The most commonly used function to add, subtract, multiple, or divide values in cells. Below are some examples.

           =SUM(A1+A2)

Add the cells A1 and A2.

         =SUM(A1:A5)

Add cells A1 through A5.

            =SUM(A1,A2,A5)

Adds cells A1, A2, and A5.

            =SUM(A2-A1)

Subtract cell A1 from A2.

           =SUM(A1*A2)

Multiply cells A1 and A2.

            =SUM(A1/A2)

Divide cell A1 by A2.

SUMIF

         =SUMIF(X:X,"*"X:X)

Perform the SUM function only if there is a specified value in the first selected cells. An example of this would be =SUMIF(A1:A6,"TEST",B1:B6) which only adds the values B1:B6 if the word "test" was put somewhere in between A1:A6. So if you put TEST (not case sensitive) in A1, but had numbers in B1 through B6, it would only add the value in B1 because TEST is in A1.

See our SUMIF definition for additional information.

TODAY

                      =   TODAY()

Would print out the current date in the cell entered. This value will change to reflect the current date each time you open your spreadsheet. If you want to enter a date that doesn't change hold down Ctrl and ; (semicolon) to enter the date.

TREND

                   =       TREND(X:X)

To find the common value of cell. For example, if cells A1 through A6 had 2,4,6,8,10,12 and you entered formula =TREND(A1:A6) in a different cell, you would get the value of 2 because each number is going up by 2.

VLOOKUP
 
                   =            VLOOKUP(X,X:X,X,X)

The lookup, hlookup, or vlookup formula allows you to search and find related values for returned results. See our lookup definition for a complete definition and full details on this formula.

Related Articles
Preventing a spreadsheet formula from changing when copying.

Read More

Where to post my pictures, movies, or other files online for free

Users who have media, such as screenshots, family photos, or movies, may want to post them on the Internet so that they can be viewed by others. Below is a listing of different locations on the Internet that allow you to post pictures, movies, or other files for free on their servers.

Internet service provider (ISP)

Your Internet service provider or company you use to connect to the Internet gives you a small amount of storage space on their servers for free. Before trying any of the below recommendations, you may want to check with your ISP and see what space they give you and how to access it.


If available, you can almost always connect to the server over FTP and upload the pictures to their server. Some companies also have easy to use Internet web interfaces that allow you to send files to the server from a web page.
Once the files have been uploaded, they are accessible by opening your personal homepage or directory. 

This directory commonly consists of the domain of your ISP followed by your home directory. For example, if your ISP's web page is "example-isp.com and your username is myname, your home page could be http://www.example-isp.com/users/~myname or http://www.example-isp.com/users/myname.

Of course, all the above information is just an example and can change depending on who you use to connect to the Internet. Ask your ISP what your home page would be.

E-mail

Free e-mail services are giving more and more storage space to their users. If you only want to share files with friends and family, try using these free mail services. 

For example, Google's Gmail gives its users 25 GB of storage space.
If you plan on using this method to share your photos, videos, and other files with friends and family, make sure they can receive e-mail attachments as large as your files. 

If the files are large (greater than 2 MB) and your recipient's e-mail service does not support large e-mail attachments, you may not be able to send them the photos or files this way.

Social networks

Social Networks sites like Facebook and Twitter are a great place to upload photos. Every day, over 200 million new photos are uploaded to Facebook.

Other third-party websites

There are dozens of companies and websites that allow you to store your pictures, images, movies, and other files on their site for free. Below is a listing of sites we've found useful and family safe, as well as the files they allow you to post.

Flickr - https://www.flickr.com

Supported files: Photos

Description: An excellent site and community that enables its users to post their personal photos on their site. It allows you to upload photos and share them with others online. It a huge user base and millions of photos to view.

YouTube - https://www.youtube.com/

Supported files: Video

Description: An excellent site with a great community that enables users to post their own movie files on their server for everyone to view.

Google Drive - https://drive.google.com/

Supported files: Photos, Documents (e.g. PDF, DOC, and XLS)

Description: Another great free online service provided by Google that gives you 5 GB of free storage and allows your files to be shared with anyone.
Dropbox - https://www.dropbox.com

Supported files: Any file

Description: A fantastic service that allows anyone to save their files online. Dropbox is available for Windows, macOS X, Linux, Android, and iOS. The Dropbox software allows you to not only store your files online easily, but also share them between all devices.

ImageShack - http://imageshack.us
Supported files: Photos, Other Image files
Description: A good site for posting small image files that requires no registration.

Photobucket - http://www.photobucket.com
Supported files: Photos, Other Image files
Description: An excellent site that supports a wide variety of image formats. Operates an extensive network of servers to host the files. Download time is always extremely fast.

SpeedSmart.net - http://speedsmart.net/
Supported files: Photos, Other Image files, Video, Audio, Other
Description: Another great place to post files up to 100 MB that you may need to transfer, move, or share.

Read More

Computer tips and tricks

Computer tips and tricks

Below is a listing of categories containing all computer tips and tricks available on learne-world.blogspot.in. With these tips, users can increase their productivity on the computer and make their overall computer experience a lot more enjoyable.

General computer tips

• Computer buying tips
• Computer cleaning tips
• Top 10 lists
• Miscellaneous tips
• Computer keyboard shortcuts
• All computer tips
• Computer questions and answers

Internet related tips

• Chrome tips
• Internet tips
• Facebook tips
• Firefox tips
• Internet Explorer tips
• Web design tips
• E-mail tips

Computer software tips

• Linux and Unix tips
• Microsoft Excel tips
• Microsoft Outlook tips
• Microsoft Windows tips
• Microsoft Word tips
• MS-DOS and command line tips
• Other Software tips

Computer hardware tips

• General hardware tips

Visitors favorite tips

• Top 10 keyboard shortcuts everyone should know
• How to repeat YouTube videos
• Determine last modified date of a web page
• Top 10 Internet tips and tricks
• View two windows side-by-side using Smart Window
• How to make Windows programs open as full screen
• Auto sum shortcut key in Microsoft Excel
• Top 10 Free PC programs everyone should have
• Top 10 computer mouse tips everyone should know
• View Windows command line history
• Top tips of 2010 to 2016

Most recently added tips

Shortcut to only select one sentence in Microsoft Word

Read More

Why should I learn about computers?

Today, almost every person in every part of the world uses some form of a computer every day and having a general understanding of computers can not only help you function in today's world it can prevent you from getting left behind. Below we've created a list of reasons why you should learn more about computers with additional resources on how you can learn more about computers.

Tip: Many people believe smartphones, tablets, and other computing devices are not computers. However, this confusion is another reason why you should learn more about computers so you can realize all of these devices are a form of computer.

Computers help you work faster

As computers, software, and hardware improve, so do their capabilities. For example, someone with even a very basic understanding of a word processor is going to be able to work faster and enjoy writing more than someone who is typing on a typewriter. With a word processor, you can easily edit anywhere in a document, erase text, move text, copy text, change fonts, and much more. All these things were impossible or tough to do with a typewriter.
Also, after learning the basics of a task or program, you can continue to learn and evolve until you become a master and become even faster at working. For example, someone who is familiar with a program and knows its keyboard shortcuts can be two to three times faster than someone with a basic understanding of a program.

Computers can answer almost any question

Today, a person with a computer connected to the Internet can find answers to virtually any question. Understanding the computer, connecting to the Internet, opening a browser, and using a search engine gives that person so much more power than someone who doesn't know how to do these things.

A computer can teach you almost anything

Also, being able to find an answer to virtually any question, a computer can also teach you how to do almost anything. There are dozens of stories of people visiting YouTube and other sites on the Internet to learn more about a subject, learn a new skill for a job, or how to do something.

Understanding computer terminology helps with other technology

Having a good understanding of the terminology and lingo used with computers can help you be more efficient with other technology. For example, who knows how to find something on the Internet is going to know the basics of how to connect to the Internet and what program to use on all of their devices.

Resolve problems and questions faster

A person with even a basic understanding of computers and software programs is going to have a much easier time solving problems they may have encountered while working on a computer. For example, someone with experience who gets an error while logging into an account may realize its because the caps lock key is enabled, and passwords are case sensitive.

Computers and robots are replacing jobs

More and more computers and robots are assisting or replacing jobs that have been done in the past by humans. If you have a better understanding of how to use computers, you can help program these machines, know how to fix them, and work more efficiently with them. If you lack the technological skills, it's more likely you would be one of the first people in your company to be replaced.

Learning about computers can help you find a job

Almost every job today and jobs of the future require some technical knowledge. Learning more about computers and computer software required for the job can a competitive edge against others who may be applying for the same job.

Computers can save and make you money

A computer can also save you money, and there are dozens of places online that allow you to compare prices and shop. Someone with the right skills can quickly find the best price and even get the item shipped to them without even having to leave their house.
A computer can also make you money by giving you the freedom of working for yourself by doing different things online.

Read More