Count() Function

The Excel COUNT function returns the count of values that are numbers, generally cells that contain numbers. Values can be supplied as constants, cell references, or ranges.

Syntax

The syntax for the COUNT function in Microsoft Excel is:

=COUNT( argument1, [argument2, ... argument_n] )

argument1, argument2, ... argument_n

Either ranges of cells or values. There can be up to 30 arguments.

Count Function Examples

Example 1 - Values Supplied from a Range of Worksheet Cells

Column C of the following spreadsheet shows three examples of the Count function, used to return the number of numeric values in one or more supplied ranges of cells.

Formulas:

	A	B	C
1	5	0	=COUNT( A1:A5 )
2	text		=COUNT( A1:A5, B1 )
3	FALSE		=COUNT( A1:B5 )
4	01/01/2015
5	#N/A	10

Results:

	A	B	C
1	5	0	2
2	text		3
3	FALSE		4
4	01/01/2015
5	#N/A	10

Note that, in the above example:

·         The numbers and the date 01/01/2015 are counted by the function.

·         The text value "text", the logical value FALSE, and the error value #N/A are not counted by the function.

·         The empty cells are not counted by the function.

Example 2 - Values Supplied Directly to the Excel Count Function

In the following spreadsheet, the Excel Count function is used to count the number of numeric values in sets of values supplied directly to the function.

Formulas:

	A
1	=COUNT( 100, DATE(2015,1,1) )
2	=COUNT( "100", "01/01/2015", FALSE )
3	=COUNT( "text", #N/A )

Results:

	A
1	2
2	3
3	0

Note, in the above example:

·         The number 100 and the date 01/01/2015 are counted by the function.

·         The text representations of the number "100" & the date, "01/01/2015", and the logical value FALSE, are counted by the function.

·         The text string "text" and the error #N/A are not counted by the function.

Notes

·         Count can handle up to 255 additional values.

·         Error values or text values that cannot be coerced into numbers are not counted

·         The logical values TRUE and FALSE are not counted.

·         Empty cells and text are not counted.

·         Use COUNTA to include text and logical values. Use COUNTIF to count based on criteria.

What-IF Analysis – Chapter II – Goal Seek

Click here to view Scenario Manager

Click here to view Data Tables

Goal Seek

Goal Seek is a built-in Excel tool that allows you to see how one data item in a formula impacts another. You might look at these as “cause and effect” scenarios.

Whenever you create a formula or function in Excel, you put various parts together to calculate a result. Goal Seek works in the opposite way. It lets you start with the desired result, and it calculates the input value that will give you that result.

How to Use Excel Goal Seek

1.       Create a spreadsheet in Excel that has your data.

2.       Click the cell you want to change. This is called the “Set cell”. In my example, this will be D4.

3.       From the Data tab, select the What if Analysis…button

4.       Select Goal seek. from the drop down menu

5.       In the Goal Seek dialog, enter the new “what if” amount in the To value text box. (Remember to add the percentage sign if you have one.)

In this example, we’re asking Excel to replace the contents of cell D4 which is 63.90% with 66.67%. This is the percentage needed to win the election.

6.       We also need to tell Excel which cell to change. Since we wanted to know the number of YES votes, we’ll click C4.

7.       Click OK. Excel will overwrite the previous cell value with the new one.

8.       If you wish to accept the new value, click OK.

If Goal Seek Cannot Find a Solution

Excel will not be able to find a solution to display in your input cell all time. Sometimes a solution just will not exist. If this happens, you will be notified in the Goal Seek Status dialogue box.

However, just because Goal Seek can't find a solution does not mean you believe that to be true. In fact, you may be certain a solution does exist.   If that is the case, you can try doing the following things:

1.       Change the value of the By Changing Cell field in the Goal Seek dialogue box. Change it to a value that is closer to the solution.

2.       You can also adjust the Maximum iterations under the Formulas tab of the Excel Options dialogue box. You can reach this dialogue box by going to the File tab, then clicking Options. Click Formulas on the left. Iterations are calculations. By increasing this number, Excel can try more possible solutions.

3.       Make sure that the formula cell depends upon the changing cell.

follow us on Arivilm
like us on https://www.facebook.com/Arivilm2501/

What-IF Analysis – Chapter I – Scenario Manager

Excel includes many powerful tools to perform complex mathematical calculations, including what-if analysis. What-if analysis is a useful way of being able to test out various scenarios in Excel.  You can look at these things two different ways.

The first way is to change the input variables and see what impact that has on the output. The scenario manager and data tables work in this way.

The second way is to say what outcome you would like to have and ask Excel to calculate what change in the inputs would be required to achieve this. The goal seek feature works this way.

Click here to view Data Tables

Click here to view Goal Seek

Scenario Manager

The Scenario Manager allows you to create and save different input values that create different results. These are called scenarios.

To set up a scenario, the first thing you have to do is identify the various cells whose values can vary in the scenarios.   Next, you select these cells, then click the Data tab and go to What-If Analysis, then Scenario Manager.

Click Add.

For our scenario, we're going to do Most Likely Case.

Enter Most Likely Case in the Scenario Name box.

Click OK.

Now enter the values for the most likely case.

Click Add to add another scenario.

When you're finished adding scenarios, click OK.

Click on the scenario, then click Show to see the numbers change.

From this window, you can also produce a summary report. This shows the changing and resulting values for your scenarios, in addition to the current values.

  

follow us on http://arivilm.blogspot.in

like us on https://www.facebook.com/Arivilm2501/