Learn to use the Min and Max functions in Excel to find the highest and lowest values and to use the Small and Large functions to return the nth ranked value in a range of cells.

ExcelMOOC > Excel Functions

Posted on by Juan David

Learn to use the Min and Max functions in Excel to find the highest and lowest values and to use the Small and Large functions to return the nth ranked value in a range of cells.

Posted on by Juan David

The excel menu system is a basic knowledge that you have to learn initially. In the ribbon, you can find almost every command to use in Microsoft Excel; you can find all that commands organised in tabs, according to each command use.

You can find button about formatting, inserting objects, about formulas, about data, view and review, and the file management options.

Put the mouse cursor in each button without clicking and you can find an explanation of the button, try to explore all this commands and you can learn what are all the powerful tools that Microsoft Excel has. Learn more about the Microsoft Excel Ribbon in the following video:

Posted on by Juan David

The COUNT function counts the number of cells that contain numbers, and counts numbers within the list of arguments. Use the COUNT function to get the number of entries in a number field that is in a range or array of numbers.

The COUNTA function counts the number of cells that are not empty in a range.

The COUNTBLANK function counts empty cells in a specified range of cells.

Posted on by Juan David

Do you ever have had to show a data of time in seconds? In Excel Doing it is really simple and will take only a few seconds.

We are used to express the values of time in hours, minutes and seconds. But probably sometimes you need these values only time expressed in seconds. Suppose the following example.

In this example we have a data equivalent to 1 hour, 35 minutes and 15 seconds. To find quickly the number of seconds is sufficient to modify the format of the cell. Right click on the cell and select Format Cells option and should go to the Custom section.

In the Type text box you should specify the custom format [ss] and click OK

Excel will show the total of equivalent seconds of the time data in the cell.

Posted on by Juan David

There are some functions in Excel where any arguments allow to specify a condition so that if it is met will perform the action. Such is the case of COUNTIF function or SUMIF function.

Such functions are useful to apply an action a cause of certain condition. For example, if I want to count all the people in the range B1: B20 that are older than 30 years can apply the following formula: `=COUNTIF(B1:B20, ">30")`

But now, what if I want to count only those who are aged above average? One option would be to get the average in a separate cell and then make the comparison with the obtained value to determine whether or not you can count every item. Rather than make a separate calculation, we can make use a special quality of the COUNTIF function argument condition.

This quality is due to the argument condition is a text string and therefore can make a chain with the result of another function. Observe carefully how to perform this concatenation in the second argument of the function: `=COUNTIF(B2:B21, ">"&AVERAGE(B2:B21))`

Concatenation in Excel is done with “&” and for this example I’m using the AVERAGE function so that Excel gets the average of the range and then the concatenation to get ready the argument condition.

After making the evaluation of this expression Excel shows the correct result.

For this example I used the AVERAGE function, but you can use any function that helps form the argument condition you need. (Source: Excel Total)

Posted on by Juan David

Excel includes several tools for data analysis and Goal Seek is a technique used to easily find the number that fulfills the necessary conditions to achieve a goal.

This tool will help you do many tests of values in a formula to find the exact value that meets the conditions. That is, if we know the result of a formula, but we are not sure of the value of one of its arguments, then Goal Seek will help us to test various scenarios to find the exact value needed. For example, suppose we have the following problem.

I have a number multiplied by another number X and this should give me the result shown. Although this is an easy problem to solve mathematically will be useful to show the functionality of Goal Seek. I begin by defining an important formula. In cell C3 will place the formula to be met to obtain the desired result, ie, = B1 * B2 it should result in 689

For now the result in C3 is zero, because there is no value in cell B2, however, when you go looking for the appropriate value for B2, the value of cell C3 will become the same as B3 when the correct value is found.

Goal Seek Command

Now you need to click the Data tab and select If Analysis where an options menu will be displayed and you must choose Goal Seek.

Immediately the dialog box Goal Seek is displayed, where you must fill in the following text boxes.

– Set cell: It is the cell that contains the formula – To Value: This is the number we want as a result of the formula. – By changing cell. The cell to be modified to have the desired result. Once the parameters have been set, you must click on the OK button and Excel will begin to enhance the necessary calculations. When have found a suitable result the Goal Seek Status dialog box is displayed.

This dialog box just notified that it has reached the target by changing the specified cell. When you close the dialog box you can see the result. For our example Excel was doing several tests until the result of 146,60 is the number multiplied by 4,7 will give the expected result.

(Source: Excel Total)

Posted on by Juan David

Using dates in Excel is very common and probably more than once you need to extract information from a specific date as the day, the month or the year. TEXT function offers an alternative to obtain such information.

Show the month of a date

Suppose the next column of date, the first task will be to obtain the month of each and present it as a text string. The function used in column B is: `=TEXT(A2, "m")`

Indeed I get the month number each date. Now see what happens if I use the same function but in the second argument put two letters “m” as follows: `=TEXT(A2, "mm")`

The difference from the previous result is that now each month are displayed with two digits putting a leading zero if required. In the next picture you can see what happens if in the second parameter of the function TEXT put three, four and five letters “m”.

Show the day of a date

Similarly as in the previous example we can obtain information of the day. Notice how using the TEXT function and specifying the second parameter as the letter “d” we may obtain information about the day.

TEXT function helps us to specifically identify the name of the day and thus can make an analysis of information according to specific days of the week.

Show the year of a date

Finally, for the year there are only two possible alternatives that are getting the year with two digits or four-digit format.

Working with dates is very important and aware of the possibility offered by the TEXT function to work with this type of data will be very useful. (Source: Excel Total)

Posted on by Juan David

You can make your worksheet look cleaner and make the worksheet text easier to read by turning off the sheet gridlines. When you do this, Excel displays the worksheet with a plain white background, which often makes the worksheet easier to read.

If you find you have trouble selecting ranges with the gridlines turned off, you can easily turn them back on again.

Posted on by Juan David

You can restructure or reorganize a worksheet by moving an existing formula to a different part of the worksheet. When you move a formula, Excel preserves the formula’s range references.

Excel also enables you to make a copy of a formula, which is a useful technique if you require a duplicate of the formula elsewhere or if you require a formula that is similar to an existing formula. When you copy a formula, Excel adjusts the range references to the new location.

Posted on by Juan David

To get the benefit of an Excel function, you need to use it within a formula. You can use a function as the only operand in the formula, or you can include the function as part of a larger formula.

In “Understanding Excel Functions,” you learned that Excel has many functions and that most functions take one or more arguments, but it is often difficult to remember a function’s arguments and the order in which they appear. To make it easy to choose the function you need and to add the appropriate arguments, Excel offers the Insert Function feature.