And() Function



The AND function is a logical function used to require more than one condition at the same time. AND returns either TRUE or FALSE. To test if a number in A1 is greater than zero and less than 10, use =AND(A1>0,A1<10). The AND function can be used as the logical test inside the IF function to avoid extra nested IFs, and can be combined with the OR function.



Syntax
The syntax for the AND function in Microsoft Excel is:

=AND( condition1, [condition2], ... )

Parameters or Arguments
condition1
The first condition to test whether it is TRUE or FALSE.
condition2, ...
Optional. Additional conditions to test whether they are TRUE or FALSE. There can be up to 30 conditions in total.

Excel And Function Examples
The following spreadsheet shows three examples of the Excel And function:

Formulas:
A
B
C
1
5
10
=AND( A1>0, A1<B1 )
2
5
10
=AND( A2>0, A2<B2, B2>12 )
3
5
10
=AND( A3<0, A3>B3, B3>12 )

Results:
A
B
C
1
5
10
TRUE
2
5
10
FALSE
3
5
10
FALSE

Note that, in the above example spreadsheet:
  • The function in cell C1 evaluates to TRUE, as BOTH of the supplied conditions are TRUE;
  • The function in cell C2 evaluates to FALSE, as the third condition, B2>12, is FALSE;
  • The function in cell C3 evaluates to FALSE, as ALL of the supplied conditions are FALSE.


By at No comments:

What-IF Analysis – Chapter III – Data Tables



Click here to view Scenario Manager
Click here to view Goal Seek

Data Tables

Instead of entering formulas and variables individually, to compare results, you can set up a Data Table, with one or two variables. Data Tables are one of Excel's "What If Analysis" features

With a Data Table in Excel, you can easily vary one or two inputs and perform What-if analysis. A Data Table is a range of cells in which you can change values in some of the cells and come up with different answers to a problem.

There are two types of Data Tables 
1.       One-variable Data Tables
2.       Two-variable Data Tables

One-variable Data Tables

A one-variable Data Table can be used if you want to see how different values of one variable in one or more formulas will change the results of those formulas. In other words, with a one-variable Data Table, you can determine how changing one input changes any number of outputs. You will understand this with the help of an example.

Create a Data Table With 1 Variable

In this example, you will build a data table that shows the monthly payments for loan terms ranging from 1 to 6 years. The number of payments will range from 12 to 72.

NOTE: The Input cells have to be on the same sheet as the data table.

The loan information is in cells C2:C4, with the number of payments in cell C3.

To set up the data table:
1.       In cells B8:B13, type the number of payments for loans terms of 1 to 6 years
2.       In cell C7, enter a PMT function, referring to the loan information cells: =PMT(C2/12,C3,C4)
3.       Select cells B7:C13 - the heading cells and the cells for the results
4.       On the Ribbon's Data tab, in the Data Tools group, click What-If Analysis, and then click Data Table.


5.       Click in the Column Input cell box, and then click on cell C3, which contains the variable for the number of payments.


6.       Click OK, to close the dialog box.
7.       Select the cells with the monthly payments, and format as Currency. In the screen shot below, the format is Currency, with negative numbers bracketed and in red.


8.       Click on one of the calculated monthly payment cells, and the formula bar shows that the cell contains a TABLE function, with cell C3 as the second argument. The curly brackets at the start and end of the formula indicate that this is an array formula

Two-variable Data Tables

A two-variable Data Table can be used if you want to see how different values of two variables in a formula will change the results of that formula. In other words, with a twovariable Data Table, you can determine how changing two inputs changes a single output. You will understand this with the help of an example.

Create a Data Table With 2 Variables

In this example, you will build a data table with 2 variables. It will show the monthly payments for loan terms ranging from 1 to 6 years, and interest rates from 2% to 6%.

NOTE: The Input cells have to be on the same sheet as the data table.

The loan information is in cells C2:C4, with the interest rate in C2, and the number of payments in cell C3.

To set up the data table:
1.       In cells B8:B13, type the number of payments for loans terms of 1 to 6 years
2.       In cells C7:G7, enter the interest rates between 2% and 6%
3.       In cell B7, enter a PMT function, referring to the loan information cells: =PMT(C2/12,C3,C4)
4.       Select cells B7:G13 - the heading cells and the cells for the results
5.       On the Ribbon's Data tab, in the Data Tools group, click What-If Analysis, and then click Data Table.
6.       Click in the Row Input cell box, and then click on cell C2, which contains the variable for the interest rate.
7.       Click in the Column Input cell box, and then click on cell C3, which contains the variable for the number of payments.


8.       Click OK, to close the dialog box.
9.       Select the cells with the monthly payments, and format as Currency. In the screen shot below, the format is Currency, with negative numbers bracketed and in red.


10.   Click on one of the calculated monthly payment cells, and the formula bar shows that the cell contains a TABLE function, with cell C2 as the first argument, and C3 as the second argument. The curly brackets at the start and end of the formula indicate that this is an array function.

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

By at No comments:

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.

By at No comments:

What-IF Analysis – Chapter II – Goal Seek






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/
By at No comments:
Subscribe to: Posts (Atom)

OR Function