How to Create Multiple Formulas for the Same Space in Excel

Microsoft Excel is a versatile program that allows for quick data entry into its columnar grid of cells. Once data is input, a variety of tools exist to manipulate and process that information. One of the most powerful of these features is the use of Excel formulas to generate new data based on existing data. The use of these formulas can be basic, such as using simple arithmetic, or much more complex. In some cases it is necessary to combine multiple formulas for processing on a single cell space. Excel provides two primary methods for creating multiple formulas for the same space in Excel.

Things You'll Need

• Microsoft Excel

Instructions

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

1. Press an equal sign in the cell where you would like to create multiple formulas.

2. 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.

3. 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.

4. 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.

5. 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".

References

• University of Arkansas: Advanced Concepts: More on Formulas
• Personal Computer Tutor: Excel IF Statements, Part II: Nesting Functions