Monday, May 23, 2011

How to Create Multiple Formulas for the Same Space in Excel


Parenthetical Nesting
1. Locate the cell in which you would like to create multiple formulas.
2. Press the equal button on the keyboard to start a formula in that cell.
3. Combine multiple formulas into one nested formula using parenthesis organization. It is often necessary in Excel to arrive at a formula result after the processing of several intermediate formulas. While these could be split into individual cells, Excel allows many formulas to be combined into one. For example, if one formula requires that a cell range be added together, and another formula must subsequently multiply the sum by a constant, write the combined formula as '=(sum(a1:a15))*15' where the 'a1:a15' represents the cell range and '15' the constant multiplier.
4. Combine arithmetic formulas together into one formula using the standard order of mathematical operation. For example, if you need two separate formulas to calculate the products of two sets of numbers, and a third formula to find the difference in these two outcomes, combine them all in a simple formula as '=a1*3-a2*6'. This could be split into three formulas, but by using standard order of operation, the multiplication is handled first for each product before the subtraction is calculated.
'IF()' Function
5. Press an equal sign in the cell where you would like to create multiple formulas.
6. Type 'if(' to begin an 'IF()' function. These functions allows multiple separate formulas to be run depending on conditions. You can have one cell house at least two formulas that do not interact with each other or depend on each other's results.
7. Determine the condition for the 'IF()' statement. For example, perhaps you wish to calculate a number by another if that number is positive, but calculate it by a different number if the number is negative. Type the condition next, such that the formula currently reads '=if(a1>0', where 'a1' is the cell that determines which formula to use. Type a comma in the formula before proceeding.
8. Type the formula that will occur if the condition is true. The 'IF()' statement will thus currently read '=if(a1>0,a1*10' where 'a1*10' represents one of the two formulas you will include in this one single overall formula.
9. Type a comma followed by the second formula that is run if the condition is false. Close the formula with an ending parenthesis. The formula may read '=if(a1>0,a1*10,a1*12)'. The two formulas that are included in this statement are 'a1*10' and 'a1*12' based on the outcome of the test, 'a1>0'.

Blogger news